roland_lei
asked on
SQL Server 2005 - "Select" cannot use optimum index
I am using Microsoft SQL Server 2005.
There is a datetime column, sales_date, in the table sales.
I have created an index in the table: -
create index idx_sales on sales(sales_date)
When I issue the follow SQL: -
select * from sales where sales_date = '20081030'
The result prompt out immediately. I figure out from the Execution Plan that it has used the correct index, idx_sales.
When I issue the follow SQL: -
select * from sales where sales_date between '20080801' and '20081030'
The result prompt out immediately, too. The correct index, idx_sales is used.
However, when I issue the follow SQL: -
select * from sales where sales_date between '20080501' and '20081030'
(Any date range more than two months)
The result come out very very slowly. From the Execution Plan, the index of the primary key has been used. (It means that "table scan" has been performed).
The table has been set as "IsAutoCreateStatistics" and "IsAutoUpdateStatistics".
So, why the correct index cannot be used?
Thx!
There is a datetime column, sales_date, in the table sales.
I have created an index in the table: -
create index idx_sales on sales(sales_date)
When I issue the follow SQL: -
select * from sales where sales_date = '20081030'
The result prompt out immediately. I figure out from the Execution Plan that it has used the correct index, idx_sales.
When I issue the follow SQL: -
select * from sales where sales_date between '20080801' and '20081030'
The result prompt out immediately, too. The correct index, idx_sales is used.
However, when I issue the follow SQL: -
select * from sales where sales_date between '20080501' and '20081030'
(Any date range more than two months)
The result come out very very slowly. From the Execution Plan, the index of the primary key has been used. (It means that "table scan" has been performed).
The table has been set as "IsAutoCreateStatistics" and "IsAutoUpdateStatistics".
So, why the correct index cannot be used?
Thx!
ASKER
The order of the index is decending.
All other columns are necessary. Do other columns affect the correct selecting of index?
All other columns are necessary. Do other columns affect the correct selecting of index?
You have two options:
1) Force the index (using index hints)
2) Try alternate forms of the query, such as:
select * from sales where sales_date >= '20080501' and sales_date <= '20081030'
There can be quirks in the query optimizer that incorrectly affects the query plan. Try 2 first, but if it does not work, try 1.
1) Force the index (using index hints)
2) Try alternate forms of the query, such as:
select * from sales where sales_date >= '20080501' and sales_date <= '20081030'
There can be quirks in the query optimizer that incorrectly affects the query plan. Try 2 first, but if it does not work, try 1.
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
please try to rebuild the index, or defrag the index, or update the stats on the index...
ASKER
Rewriting as
select * from sales where sales_date >= '20080501' and sales_date <= '20081030
will have no change.
And I would not like to force the index because couple of programs should be amended.
I have dropped the index and recreate it and then update the statistics. There is no change, too.
select * from sales where sales_date >= '20080501' and sales_date <= '20081030
will have no change.
And I would not like to force the index because couple of programs should be amended.
I have dropped the index and recreate it and then update the statistics. There is no change, too.
Sometimes the query analyzer simply fails to optimize correctly. Do not assume that rewriting the query to use a different format will do nothing, as I have shown in my own queries that it can (but will not always, or even frequently) correct issues with bad query plans.
What is happening in this case is that the query analyzer is deciding that, with the number of records you are selecting, the end speed *should* be faster using a pk / clustered index (is the primary key clustered?) scan than it would be by using the index.
If you test by forcing the index, does the resulting query return more quickly than without in the poorly=performing case? Or does it simply return the first rows faster, thus seeming more responsive, but take longer or more resources to complete the full query?
What is happening in this case is that the query analyzer is deciding that, with the number of records you are selecting, the end speed *should* be faster using a pk / clustered index (is the primary key clustered?) scan than it would be by using the index.
If you test by forcing the index, does the resulting query return more quickly than without in the poorly=performing case? Or does it simply return the first rows faster, thus seeming more responsive, but take longer or more resources to complete the full query?
ASKER
I have done two tests with different date ranges.
1st (shorter date range)
The time when running the full query with forcing index is 19 seconds.
The time when running the full query without forcing index is 39 seconds.
2nd (longer date range)
The time when running the full query with forcing index is 25 seconds.
The time when running the full query without forcing index is 44 seconds.
It means that the index is useful. However, the query optimizer cannot use the appropriate index.
1st (shorter date range)
The time when running the full query with forcing index is 19 seconds.
The time when running the full query without forcing index is 39 seconds.
2nd (longer date range)
The time when running the full query with forcing index is 25 seconds.
The time when running the full query without forcing index is 44 seconds.
It means that the index is useful. However, the query optimizer cannot use the appropriate index.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Are all the columns really necessary?