Hitting An Index within Query
-
Query A runs much slower than Query B. Will Query A not hit an index because it is based on a variable? Query B runs in 20 seconds, Query A runs in 6 minutes. Is there a more efficient way to query in this manner? Thanks!
QUERY A
Declare @days int, @startDate smalldatetime
Set @days = 3
Set @startDate = getDate() - @days
Select FieldA
From TableA
Where dateField >= @startDate
QUERY B
Select FieldA
From TableA
Where dateField >= '09/29/2006'
-
What is the datatype of dateField?
_____________
Code for TallyGenerator -
It is a datetime field.
-
Then you need to read this:
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=310214
Just not to repeat all those words.
_____________
Code for TallyGenerator -
Thank you, Sergiy. I understand I must compare with same datatypes, but I am failing at doing this with my parameter. What change must I make? I've change the parameter to be declared as datetime but no effect.
Thanks again!
-
Are you sure your dateField is datetime???
Implicit conversion of datetime to smalldatetime used to be my "favourite exercise"
some time ago. And converting variables to the same type as columns always helped.
Look at the execution plans. Find what's different in execution of both queries.
It may be not the only reason.
_____________
Code for TallyGenerator -
Thanks for following along with me on this. I really appreciate it!
I can verify that it is datetime. Unfortunately, the execution plan does not give much to go on. I'll keep playing with it and my go another route.
Thanks again!
-
Have you tried using table hints to utilise the required index?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply