Performance tuning of sql query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Performance tuning of sql query

Author

Topic

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-04-28 : 04:39:22

Dear Experts,

I have the below query i request you to help me in fine tuning this query,

CREATE procedure [dbo].[Cmd_Personfeed_ClearResurvey]

as

SET NOCOUNT ON;

Declare @StartTime dateTime
set @startTime = getutcdate()
Declare @StartTimeAll datetime
set @StartTimeAll = getutcdate()

--days of resurvey--
--insert the records into the log that have been surveyed within the days of resurvey defined by the corvey
insert into Log (Type,Source,Message,Logdate,FirstName,LastName,Email,Requested_FirstName,Requested_LastName,Requested_Email,Corvey,CaseNo)
select 1,'Mailbatch (SP)','Person is deleted from PERSON_FEED because this person has received a survey within the resurvey period',
getutcdate(),PF.FirstName,PF.LastName,PF.Email,PF.Requested_FirstName,PF.Requested_LastName,PF.Requested_Email,PF.Corv_Name,PF.CaseNo
from dbo.person_feed PF
INNER JOIN dbo.corvey C on c.Name = PF.Corv_Name
INNER JOIN dbo.Person P on P.FirstName = PF.FirstName and P.LastName = PF.LastName and P.Email = PF.Email and C.Id = P.Corv_ID
where P.DateLastRequested > dateadd(day,-C.NOofDaysForResurvey,getdate())

--remove the records from person feed
delete person_feed
from person_feed pf
INNER JOIN dbo.corvey C on c.Name = PF.Corv_Name
INNER JOIN dbo.Person P on P.FirstName = PF.FirstName and P.LastName = PF.LastName and P.Email = PF.Email and C.Id = P.Corv_ID
where P.DateLastRequested > dateadd(day,-C.NOofDaysForResurvey,getdate())

insert into dbo.SPRuntimes
select 'Cmd_Personfeed_ClearResurvey','days of resurvey',datediff(s,@startTime,getutcdate()),getutcdate()
set @startTime = getutcdate()

--Remove double records in the feed--

--Put the tickets in tmp table--
Select Max(ID)as ID,FirstName, LastName, Corv_Name,Email
Into #Tmp
from dbo.Person_Feed
group by FirstName, LastName, Corv_Name,Email

--Log the tickets that will be removed--
insert into Log (Type,Source,Message,Logdate,FirstName,LastName,Email,Requested_FirstName,Requested_LastName,Requested_Email,Corvey,CaseNo)
select 1,'Mailbatch (SP)','Person is deleted from PERSON_FEED because this person is multiple times in the batch',
getutcdate(),PF.FirstName,PF.LastName,PF.Email,PF.Requested_FirstName,PF.Requested_LastName,PF.Requested_Email,PF.Corv_Name,PF.CaseNo
from dbo.person_feed PF
INNER JOIN dbo.corvey C on C.Name = PF.Corv_Name
INNER JOIN #Tmp T on T.FirstName = PF.FirstName and T.LAstName = PF.LastName and T.Corv_Name = PF.Corv_Name and T.Email = PF.Email
where C.NoOfDaysForResurvey != 0 and PF.ID != T.ID

--Delete the tickets--
Delete from Person_Feed
from dbo.person_Feed PF
INNER JOIN dbo.corvey C on C.Name = PF.Corv_Name
INNER JOIN #Tmp T on T.FirstName = PF.FirstName and T.LAstName = PF.LastName and T.Corv_Name = PF.Corv_Name and T.Email = PF.Email
where C.NoOfDaysForResurvey != 0 and PF.ID != T.ID

Drop Table #Tmp

insert into dbo.SPRuntimes
select 'Cmd_Personfeed_ClearResurvey','double records in the feed',datediff(s,@startTime,getutcdate()),getutcdate()

insert into dbo.SPRuntimes
select 'Cmd_Personfeed_ClearResurvey','All',datediff(s,@startTimeAll,getutcdate()),getutcdate()

SET NOCOUNT OFF

Let me know if any details required further.

Thanks,
Gangadhara MS
SQL Developer and DBA

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-28 : 04:43:23

First of all put in some logging to find out how long each step takes then you will know what to concentrate on.
How long does it take at the moment?

I'm guessing tha
where P.DateLastRequested > dateadd(day,-C.NOofDaysForResurvey,getdate())
doesn't help and you are doing it twice. Get the PKs (or other uniqu eindex) into a temp table or table variable then use that for the two queries.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-04-28 : 05:00:23

The sp is taking 30 sec to run

? DB: SQL Server 2005 (Size: 29GB).
? Data in the tables size used in the Stored Procedure: 3 million to 8.5 million.
? Issue: While executing the Stored Procedure, it is getting timed out (30 Secs).
? This is a console application which performs a set of validation and sends mail. This is being scheduled and run from the Server.
? The stored procedure performs following operations
1. INSERT INTO….SELECT from one table to another based on JOIN.
2. DELETE from a table based on JOIN.
3. SELECT into #Tmp Table.
? The First INSERT INTO….SELECT takes about 28 secs to execute, with 30 000 records in the base table in Production environment.
? The First INSERT INTO….SELECT takes about 9 secs to execute, with
1 500 000 records in the base table in Production environment.
? The expected time of execution of the whole Stored Procedure: 15-20 sec.
? We have already provided/suggested the following,
1. Best practices for Performance Improvements.
2. Temp table with index
3. Temp Variable instead of Temp table
4. Table instead of Temp table
5. Recompiling Stored Procedure using sp_recompile.
6. Processing in batch.
7. DB Admin Jobs
a) Shrink DB
b) Rebuild Indexes
c) Defragment the DB.
Point (b) & (c) is already being performed in the Production as Jobs.

Point# 1 to 5 does not boost the performance as expected by the client (5%-8%), as the expectation in Performance improvement is >40%.

? The Improvement should be consistent as we scale up or growth of data.
? Customer is not keen on increasing the command Timeout /Connection Timeout.

Thanks,
Gangadhara MS
SQL Developer and DBA

yadhu_cse
Constraint Violating Yak Guru

252 Posts

Posted - 2011-04-28 : 08:32:11

u do one thing. copy the query that ur running into profiler then start running that around 3 times and store result into to table then open tunning advicer and run it then it will siggest u anying is required for ur query.. copy that query and run it on ur database then check

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-28 : 08:51:20

The first insert will be getting the rows into memory which is probably why the second similar statement is a lot quicker.
Almost all of your time is taken with this statement so it's the one you should concentrate on
Using the temp table as I suggested should help - it'll certainly show whether it's the insert of the select that's causing the issue. It should get the time of the second statement down to near 0.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

gangadhara.ms
Aged Yak Warrior

549 Posts

Posted - 2011-04-29 : 05:12:14

any other suggestions pls

Thanks,
Gangadhara MS
SQL Developer and DBA

Jahanzaib
Posting Yak Master

115 Posts

Posted - 2011-04-29 : 13:54:36

go to SSMS and paste as

exec Cmd_Personfeed_ClearResurvey

then click on Execution Plan button then execute this and then send that execution plan

what is the server specification ?

Regards,

Syed Jahanzaib Bin Hassan
MCTS,MCITP,OCA,OCP,OCE,SCJP,IBMCDBA

My Blog
www.aureus-salah.com

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-04-29 : 14:22:28

Get someone involved who is willing to do something about it?
How many suggestions are you looking for?

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources