December 20, 2010 at 2:47 am
I need help to execute below query.
In this query
If I use @Result and @CurDate as parameter than it takes lot of time in execution in scenario 1.
If I use value of the parameter directly than it executes within 2 seconds scenario 2.
Kindly suggest me how I can use parameters in this query so that it can execute faster.
Scenario 1
declare @CurDate date, @Result date
select @CurDate='30 Sep 2009'
select @Result='01 July 2009'
select trn.InvNbr,trn.TrnTrNbr,trn.TrnSrlNbr,trn.EffectDt,trn.EntryDt,trn.TrnTy,trn.TrnStatus,
trn.RevSrlNbr,trn.OrigSrlNbr,tru.TrNmMixdCase
from RSAInvTrn trn
join rsatrust tru on trn.trntrnbr=tru.trnbr
where trn.entrydt between @Result and @CurDate
Scenario 2
select trn.InvNbr,trn.TrnTrNbr,trn.TrnSrlNbr,trn.EffectDt,trn.EntryDt,trn.TrnTy,trn.TrnStatus,
trn.RevSrlNbr,trn.OrigSrlNbr,tru.TrNmMixdCase
from RSAInvTrn trn
join rsatrust tru on trn.trntrnbr=tru.trnbr
where trn.entrydt between ’01 July 2009’ and ’30 Sep 2009’
December 20, 2010 at 3:29 am
How is this query getting called from your front end application?
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
December 20, 2010 at 4:13 am
through stored procedures...
December 20, 2010 at 4:23 am
Most likely a parameter sniffing problem.
Search for "Parameter Sniffing" and you should find some good resources.
December 20, 2010 at 6:23 am
Check the execution plans on both queries. I'm pretty sure you'll see that they're different. You're hitting a bad parameter sniffing problem. I'd also suggest trying the query within a stored procedure and using parameters instead of variables, just to see what that looks like.
"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
December 20, 2010 at 1:34 pm
vino5786 (12/20/2010)
through stored procedures...
Then there's no problem here. When you put this into a stored proc, make those parameters of the proc and you'll get the same behaviour as for constants.
Technically this is not parameter sniffing. It's the absence of parameter sniffing.
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply