Index not getting used when where clause uses variable, index is getting used when value is hard coded.

Question

Post reply

Index not getting used when where clause uses variable, index is getting used when value is hard coded.

coombst

SSC Enthusiast

Points: 123
More actions
October 14, 2010 at 12:34 pm

#225088

I have a select statement that uses an index when the date range value in the WHERE clause is hardcoded like this (query takes 3 seconds),
WHERE ((workh_date_out <= '2010-10-14 11:01:33.513' AND workh_date_out > '2010-10-14 11:01:33.513') OR workh_id = @WorkOrderID)
AND workh_oemrt_id = @OemrtID
whereas the index does not get used when the where clause uses variables for the datetime value like this (query takes 67 seconds),
WHERE ((workh_date_out <= @TodaysDate AND workh_date_out > DATEADD(month, @Month * -1, @TodaysDate)) OR workh_id = @WorkOrderID) AND workh_oemrt_id = @OemrtID
There is no significant difference in the datetime values being used. I could also be using BETWEEN for the date range but it has no impact.
Any ideas why this happening?
Thanks,
Troy

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323547 More actions · Answer 1

this is inside a procedure, right?

is @TodaysDate a parameter with a default of NULL?

if that's true, I'm thinking parameter sniffing is the problem, and a bad execution plan is being used.

the link i provided can lead to a lot of posts on the subject;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

coombst SSC Enthusiast Points: 123 More actions · Answer 2

It's actually in a function... the @TodaysDate parameter is always set to a date, it will never be null.

Based on that do you think paramter sniffing is still the issue?

Troy

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Parameter sniffing (or more accurately, lack thereof)

http://sqlinthewild.co.za/index.php/2008/02/25/parameter-sniffing-pt-2/

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

coombst SSC Enthusiast Points: 123 More actions · Answer 4

That was it. I made the variable into a parameter and the execution plan and query time was drastically improved. Thanks for the help.

Troy

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Using a function on the lower bound of the date range is also likely to cause problems. Try this instead:

DECLARE @workh_date_out DATE

SET @workh_date_out = DATEADD(month, @Month * -1, @TodaysDate)

WHERE (

(workh_date_out <= @TodaysDate AND workh_date_out > @workh_date_out)

OR workh_id = @WorkOrderID

) AND workh_oemrt_id = @OemrtID

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden