SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 SQL Server Administration (2005)
 Performance tuning of sql query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 04/28/2011 :  04:39:22  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 04/28/2011 :  04:43:23  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 04/28/2011 :  05:00:23  Show Profile  Reply with Quote
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
Go to Top of Page

yadhu_cse
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/28/2011 :  08:32:11  Show Profile  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 04/28/2011 :  08:51:20  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page

gangadhara.ms
Aged Yak Warrior

India
547 Posts

Posted - 04/29/2011 :  05:12:14  Show Profile  Reply with Quote
any other suggestions pls

Thanks,
Gangadhara MS
SQL Developer and DBA
Go to Top of Page

Jahanzaib
Posting Yak Master

Pakistan
115 Posts

Posted - 04/29/2011 :  13:54:36  Show Profile  Visit Jahanzaib's Homepage  Reply with Quote
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
Go to Top of Page

nigelrivett
Flowing Fount of Yak Knowledge

United Kingdom
3383 Posts

Posted - 04/29/2011 :  14:22:28  Show Profile  Visit nigelrivett's Homepage  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000