asked
between operator not using index
Hi, There is a datetime column say Dates in Table A. A non-clustered index exists on this column. But, the below query when used always shows up a table scan instead of a index seek operation. select col1 from tableA where Dates between @startdate and @enddate. What should be my approach inorder to make this table scan into a index seek operation?
The optimizer may be choosing a table scan as the values of `@startdate` and `@enddate` are beyond the bounds of what is in the table, in other words if you specify dates that means the whole table will be required, then table scan will almost always be chosen. If not, then make sure your statistics are up-to-date. You can force the optimizer to choose an index, but that could hurt performance if the data changes significantly.
A Scan can also be chosen if the range between the dates aren't selective enough. Its probably faster to perform a Scan then trying to seek after more than 50% of the data. **EDIT** Another thing that came into mind is that if the index can't give you all the data you need for the query, SQL server has two options: - Do an index seek + a key lookup - A clustered index seek or a table scan If too much data will be returned, a clustered index scan is prefferred over index seek + key lookup
It could be statistics though, or even the need for a full scan on the stats instead of the default sampled scan. Still, I'm inclined to believe it's just moving more data than it's worth to do a seek.
Here is a great article if you want to learn more
http://www.sqlskills.com/BLOGS/KIMBERLY/category/The-Tipping-Point.aspx If you have 2008 and really want to, you could use FORCESEEK hint
+1 ah yes that is what I was trying to elude to, but you put it better!
@Håkan Winther is right. The optimizer is evaluating that is cheaper to just perform a table scan rather than index scan, and then joining with the table to retrieve the rows. It happens a lot in small tables. or if the range scanned is pretty large. My recommendation, enhance the index with an INCLUDE of values often retrieve so an INDEX scan is guarantied.
I would like to know execution plan of the query / SP. IS index is de-fragment(however this does`t change query plan) Are statistics up to date? can you include column col1 in you non clustered index? select col1 if you are using query then try to run with the query like from tableA where Dates between '1/1/2013' and '2/2/2013' if it generate different execution plan(index Seek) then you are not able to use your statistics efficiently due to uneven data (rows) distributions on the table for the key column, because when you use variable in your query then it estimate numbers or rows average of rows for key column avoid doing it. IF you are using SP then i think its due to parameter sniffing. when sp run first time it saves the execution plan what data you provided first time. here you can use local parameter or optimise for or recompile . go google all.
1 Person is following this question.
