April 28, 2011 at 2:46 am
Dear experts,
please help with the below sp fine tuning.
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.
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
Attached the execution plan.let me know if any info required further.
April 28, 2011 at 5:05 am
I can't see that exec plan. When I opened it I got chinese characters.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 28, 2011 at 5:21 am
Gangadhara MS (4/28/2011)
3. Temp Variable instead of Temp table
Likely to degrade performance, not improve it
a) Shrink DB
Almost certain to degrade performance.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 28, 2011 at 8:01 pm
Reattached the exec plan.
April 29, 2011 at 5:30 am
Try adding p.Date_Last_Requested as an INCLUDE column on the Person.CorvId_FirstName_LastName_Email index. That would eliminate the key lookup operation you're getting to retrieve that one column. That will help, a lot.
The other issue is the function on the column. That's going to prevent you from using an index. This has to go: DATEADD(day, -C.NOofDaysForResurvey,GETDATE())
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply