CODEWITHSUNDEEP
sql-serverindexingsql-server-2012query-performancesql-server-performance
anar khalilov
anar khalilov

Reputation: 17498

Query Performance Paradox: Unique Clustered vs Non-Unique Non-Clustered Index

I have a table MY_TABLE with approximately 9 million rows.

There are in total of 38 columns in this table. The columns that are relevant to my question are:

Now I run the following two queries and naturally expect the first one to execute faster because the data is being sorted by a column that has a unique clustered index but somehow it executes 271 times(!) slower.

SELECT TOP 1 
    RECORD_ID 
FROM 
    MY_TABLE 
WHERE 
    RECORD_CREATED >= '20140801' 
ORDER BY 
    RECORD_ID

SELECT TOP 1 
    RECORD_ID 
FROM 
    MY_TABLE 
WHERE 
    RECORD_CREATED >= '20140801' 
ORDER BY 
    RECORD_CREATED

The execution times are 1630 ms and 6 ms, respectively.

Please advise.

P.S.: Due to security policies of the environment I cannot see the execution plan or use SQL Profiler.

Upvotes: 0

Views: 590

Answers (1)

StriplingWarrior
StriplingWarrior

Reputation: 156524

SQL Server has a few choices to make about how to perform this query. It could begin by sorting all the items, leveraging the indexes you mentioned, and then follow that up by filtering out any items that don't match the WHERE clause. However, it's typically faster to cut down on the size of the data set that you're working with first, so you don't have to sort as many items.

So SQL Server is most-likely choosing to perform the WHERE filter first. When it does this, it most likely starts by using the non-unique, non-clustered index on RECORD_CREATED to skip over all the items where RECORD_CREATED is less than '20140801', and then take all the items after that.

At that point, all the items are pre-sorted in the order in which they were found in the RECORD_CREATED index, so the second query requires no additional effort, but the first query then has to perform a sort on the records that have been chosen.

Upvotes: 2

Related Questions