March 17, 2010 at 3:42 am
Hi all,
I have 2 query's that are nearly identical, they both select data and aggregate it up where a value is between a date, 1 uses hard coded dates and 1 uses variables.
i.e
where d_booked between '14 March 2010 00:00:00' AND '20 March 2010 23:59:59'
vs
where d_booked between @fromDate AND @ToDate --the values of these variables are as the values above.
However, the first method has a much better execution plan (albeit not great), as it uses an index seek and then a key lookup. The second method uses a clustered indexe scan, so performs a lot worse.
As I said the indexes on this table are not ideal, but my question is this, why does using a variable cause it to be so radically different, the stats are all up to date, any idea's?
Thanks,
Nic
March 17, 2010 at 4:09 am
Hi,
My guess is that when the query uses variables for the dates, the Optimizer must assume that they can have any value. But when you hard code the dates, the Optimizer can optimize the query for the specific dates.
In worst case you pass two dates that result in all rows in the table (which will require an index scan. But with the two dates you used in your test, the result might actually be only one or two rows, in which case an index seek is the best.
Try changing the dates to a wider interval in the hard coded query; when the interval is large enough the query plans will probably be identical...
/Markus
March 17, 2010 at 4:12 am
Hi Markus,
Thanks for the reply, I think your right and it could just be the optimizer getting in a state with the values I provided, I will try some other scenario's and get back to you.
Many thanks.
Nic
March 17, 2010 at 4:17 am
The phenomenon is known as parameter sniffing.
See http://msdn.microsoft.com/en-us/library/cc966425.aspx#XSLTsection133121120120 for a full explanation, and workarounds.
March 17, 2010 at 4:20 am
Paul White (3/17/2010)
The phenomenon is known as parameter sniffing.See http://msdn.microsoft.com/en-us/library/cc966425.aspx#XSLTsection133121120120 for a full explanation, and workarounds.
Yeah I experienced something similar last week, I should of really identified it as that before! oh well, I'll get it next time.
Thanks for all the help, it all makes perfect sense now.
Nic
March 17, 2010 at 4:23 am
Cool. If the values in the variables are atypical, consider adding OPTION (RECOMPILE) to the statement. This will generate a new plan using the specific values in the variables.
March 17, 2010 at 5:50 am
another post with related problem http://www.sqlservercentral.com/Forums/Topic881805-360-1.aspx
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
March 17, 2010 at 5:52 am
Bhuvnesh (3/17/2010)
another post with related problem http://www.sqlservercentral.com/Forums/Topic881805-360-1.aspx
It is a very common problem. That other thread has more to do with using the UNIQUEIDENTIFIER data type correctly, though.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply