Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

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...

> 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...

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc