Sql Server Programming :: SQL 2005 not using index on large tables?


	SQL 2005 not using index on large tables? I have an odd issue -- I have a table ("CUSTOMERS") that has about 500,000 rows on it. I need to do a "SELECT" to retrieve rows from a particular company, ordered by company and zip code. I have several indexes, including an index for the primary key ("COMPANY" and "CUSTOMER_NUMBER"), and an index by company and zip code. The select statement I am using is quite simple (values are just examples) -- SELECT (all fields) from CUSTOMERS where COMPANY=200 and ZIPCODE > 02138 order by COMPANY , ZIPCODE This takes a long time to run, and when I analyze the execution plan, I see that it spends most of its time doing a SORT and then a scan over the primary key index. Even if I use the "WITH" to try and force it to use the index, no change. However, when I limit the rows being returned, like this -- SELECT TOP 1500 (all fields) from CUSTOMERS where COMPANY=200 and ZIPCODE > 02138 order by COMPANY , ZIPCODE It does use the index. This makes no sense to me -- I am assuming that it has something to do with the number of rows being returned, but in both cases, it is going to need to do the sort to deliver the rows in sequence. Anybody have any idea why SQL 2005 would not be using the index in the first case, and what is it about the "TOP" keyword that is making it use the index? Orwell Well, I have some questions? How many rows do you return (without TOP clause)? Pehaps it is much more easy for SQL Server to scan an entire table rather than using indexes Is it high selective COMPANY column?. Create in index on ANY combination of selective criteria. You don't need everything from the WHERE clause but try and supply the combination of columns that will yield a selective set! Are COMPANY an ZIPCODE high selective? Do you really need to resturn all columns? "Orwell" <orwellnel@usa.com> wrote in message news:1173020782.370500.5920@64g2000cwx.googlegroups.com... - Hide quoted text - >I have an odd issue -- I have a table ("CUSTOMERS") that has about > 500,000 rows on it. I need to do a "SELECT" to retrieve rows from a > particular company, ordered by company and zip code. I have several > indexes, including an index for the primary key ("COMPANY" and > "CUSTOMER_NUMBER"), and an index by company and zip code. The select > statement I am using is quite simple (values are just examples) -- > SELECT (all fields) from CUSTOMERS where COMPANY=200 and ZIPCODE > > 02138 order by COMPANY , ZIPCODE > This takes a long time to run, and when I analyze the execution plan, > I see that it spends most of its time doing a SORT and then a scan > over the primary key index. Even if I use the "WITH" to try and force > it to use the index, no change. > However, when I limit the rows being returned, like this -- > SELECT TOP 1500 (all fields) from CUSTOMERS where COMPANY=200 and > ZIPCODE > 02138 order by COMPANY , ZIPCODE > It does use the index. This makes no sense to me -- I am assuming > that it has something to do with the number of rows being returned, > but in both cases, it is going to need to do the sort to deliver the > rows in sequence. Anybody have any idea why SQL 2005 would not be > using the index in the first case, and what is it about the "TOP" > keyword that is making it use the index? > It does use the index. This makes no sense to me -- I am assuming > that it has something to do with the number of rows being returned, > but in both cases, it is going to need to do the sort to deliver the > rows in sequence. Anybody have any idea why SQL 2005 would not be > using the index in the first case, and what is it about the "TOP" > keyword that is making it use the index? Without the DDL, we can only guess. Because you are requesting all columns, a key/rid lookup is needed if to retrieve data using a non-clustered index. There is a threshold at which a scan is more efficient that a lookup. It is likely that the 1500 rows you specify in the TOP clause is below that threshold so the index is used. -- Hope this helps. Dan Guzman SQL Server MVP "Orwell" <orwellnel@usa.com> wrote in message news:1173020782.370500.5920@64g2000cwx.googlegroups.com... - Hide quoted text - >I have an odd issue -- I have a table ("CUSTOMERS") that has about > 500,000 rows on it. I need to do a "SELECT" to retrieve rows from a > particular company, ordered by company and zip code. I have several > indexes, including an index for the primary key ("COMPANY" and > "CUSTOMER_NUMBER"), and an index by company and zip code. The select > statement I am using is quite simple (values are just examples) -- > SELECT (all fields) from CUSTOMERS where COMPANY=200 and ZIPCODE > > 02138 order by COMPANY , ZIPCODE > This takes a long time to run, and when I analyze the execution plan, > I see that it spends most of its time doing a SORT and then a scan > over the primary key index. Even if I use the "WITH" to try and force > it to use the index, no change. > However, when I limit the rows being returned, like this -- > SELECT TOP 1500 (all fields) from CUSTOMERS where COMPANY=200 and > ZIPCODE > 02138 order by COMPANY , ZIPCODE > It does use the index. This makes no sense to me -- I am assuming > that it has something to do with the number of rows being returned, > but in both cases, it is going to need to do the sort to deliver the > rows in sequence. Anybody have any idea why SQL 2005 would not be > using the index in the first case, and what is it about the "TOP" > keyword that is making it use the index?