Why So Slow? SQL TOP Statement

[darbnacnud]

Here's the problem. I have a table with about 200,000 records. If I use this statement "Select PartNo from tblInventory where PartNo= [string]" the result is instant. Problem is I need to get part numbers beginning at a range so I use "Select Top 10 PartNo from tblInventory where PartNo >= [String] Order by PartNo" and it is very slow. I have an Index on PartNo. Any help would be appreciated.

 

[scriverb]

The method that I try to use in analyzing weird results like this if there is not a glaring answer is to break down the query and run it in parts. Create a query with the following SQL:
"Select PartNo from tblInventory where PartNo >= [String]"

Now how fast does this run? Is the resulting recordset in PartNo order? If both are true then the Order by clause is the culpret and is not necessary to get you the top ten. You see I believe that by selecting on your indexed field PartNo the recordset is being returned in PartNo order. If I am wrong please let me know. With that in mind you can just drop the Order by in the original SQL and it should be very fast once again.

Post back with your results.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[darbnacnud]

Bob, Thanks for your help, but that nasty problem is that results do not come back in Part No. Order.... Any other ideas? Is there anyway to force a SQL statement to use a particular index?