November 8, 2013 at 8:17 am
I'm just curious as to why this would increase performance so drastically. I have a stored procedure that accepts two date parameters, a start date and end date. Initially they were passed to @StartDt and @EndDt in the where clause of my query. The query was taking up to 10 minutes to run. Now it only takes 8 to 10 seconds, the only change I made was declaring a @StartDate and @EndDate variable inside the procedure and assigning the @StartDt and @EndDt parameters to the variables then using the variables instead of the parameters in the where clause of my query. Just curious as to why this would increase performance so drastically. Thanks!
Slow way
ALTER PROCEDURE [dbo].[MyProcedure]
@StartDt datetime,
@EndDt datetime
AS
BEGIN
SET NOCOUNT ON;
--query is obviously more complex
Select *
from Table
Where Docdate >= @StartDt and Docdate <= @EndDt
Fast Way
ALTER PROCEDURE [dbo].[MyProcedure]
@StartDt datetime,
@EndDt datetime
AS
BEGIN
SET NOCOUNT ON;
Declare @StartDate datetime
Declare @EndDate datetime
Set @StartDate = @StartDt
Set @EndDate = @EndDt
--query is obviously more complex
Select *
from Table
Where Docdate >= @StartDate and Docdate <= @EndDate
November 8, 2013 at 8:46 am
This perhaps?
Or other parameter sniffing problem. Basically, SQL can sniff parameter values and use those to estimate row counts for query optimisation. It can't with variables. If it gets that estimation wrong from the parameters, you can get horrid performance.
Could you post the execution plans for both the slow and fast procedures? With those should be able to tell for sure.
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
November 8, 2013 at 9:11 am
GilaMonster,
Thanks for your reply. I am very new at trying to decipher execution plans, so if you had any recommendations on a good website or book it would be very much appreciated 😀
Anyway,
I have attached both the fast and slow execution plans. They are the actual and not estimated.
November 8, 2013 at 9:31 am
kreierson (11/8/2013)
GilaMonster,Thanks for your reply. I am very new at trying to decipher execution plans, so if you had any recommendations on a good website or book it would be very much appreciated 😀
Anyway,
I have attached both the fast and slow execution plans. They are the actual and not estimated.
You can start with Grant Fritchey's book. There's an eBook version available here:
November 8, 2013 at 9:37 am
Thanks! This should help a lot
November 11, 2013 at 1:19 am
I also second "Parameter sniffing " here.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
November 11, 2013 at 1:27 am
kreierson (11/8/2013)
GilaMonster,Thanks for your reply. I am very new at trying to decipher execution plans, so if you had any recommendations on a good website or book it would be very much appreciated 😀
Anyway,
I have attached both the fast and slow execution plans. They are the actual and not estimated.
Meant to look at this over the weekend, didn't have time, still intend to look at it as soon as I can.
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
November 11, 2013 at 7:23 am
Thank you very much!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply