SQL Server 2005 Clustered Index Query Speed

Question

Our sites are getting pounded pretty hard so we're taking a look into optimizing some of our existing queries.

While looking into this we ran across several queries whose execution plan was about 4-5 times faster when a simple reference of the clustered index is in the query... for example

If this was the old query:

SELECT ...
FROM myTable
WHERE categoryID =  @category

the following query would be 4 times faster according to the execution plan in SSMS:

SELECT ...
FROM myTable
WHERE categoryID =  @category 
AND lotID = lotID

We can't seem to make sense of how this would make the query faster. The clustered index is on lotID but since its doing a comparison against itself how is this helping?

Answer 1

seems pretty obvious to me

your first query is not covered by the clustered index while the second is since lotID is not in the WHERE clause of the first query

You might want to read SQL Server covering indexes to see how that all works

you also need to understand that a clustered index IS the data, all the data for a table is in the clustered index. when you create a non clustered index on table that has a clustered index then the non clustered index will have a pointer to the clustered index (since that is where the rest of the data is) unless you can satisfy your query completely by the non clustered index and in that case only the non clustered index will be used...I will stop rambling now

EDIT

I read AND lotID = @lotID NOT AND lotID = lotID

sometimes you can fake out a clustered index by doing where lotID >0 (picking the lowest number you have) and you will get a seek

So if your smallest lotID = 1 and you add AND lotID > 0

you could also see a seek instead of a scan, I demonstrate WHERE IndexValue > '' in this post Is an index seek always better or faster than an index scan?

Answer 2

Are you sure that you are not seeing the effects of caching?

Answer 3

If you post execution plans of the two queries, we can give you more details about what's going on.