CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Bob Horn
Bob Horn

Reputation: 34325

SQL Query Slow When Ordering by a PK Column

Why does this query take a long time (30+ seconds) to run?

A:

SELECT TOP 10 * FROM [Workflow] ORDER BY ID DESC

But this query is fast (0 seconds):

B:

SELECT TOP 10 * FROM [Workflow] ORDER BY ReadTime DESC

And this is fast (0 seconds), too:

C:

SELECT TOP 10 * FROM [Workflow] WHERE SubId = '120611250634'

I get why B and C are fast:

B:
enter image description here

C:
enter image description here

But I don’t get why A takes so long when we have this:

enter image description here

Edit: The estimated execution plan using ID:

enter image description here

Edit: The estimated execution plan using ReadTime:

enter image description here

Upvotes: 3

Views: 288

Answers (2)

Luaan
Luaan

Reputation: 63772

Well, your primary key is for both ID (ASC) and ReadTime (ASC). The order is not important when you're only having a single column index, but it does matter when you have more columns in the index (a composite key).

Composite clustered keys are not really made for ordering. I'd expect that using

SELECT TOP 10 * FROM [Workflow] ORDER BY ID ASC

Will be rather fast, and the best would be

SELECT TOP 10 * FROM [Workflow] ORDER BY ID, ReadTime

Reversing the order is a tricky operation on a composite key.

So in effect, when you order by ReadTime, you have an index ready for that, and that index also knows the exact key of the row involved (both its Id and ReadTime - another good reason to keep the clustered index very narrow). It can look up all the columns rather easily. However, when you order by Id, you don't have an exact fit of an index. The server doesn't trivially know how many rows there are for a given Id, which means the top gets a bit trickier than you'd guess. In effect, your clustered index turns into a waste of space and performance (as far as those sample queries are concerned).

Seeing just the tiny part of your database, I'd say having a clustered index on Id and ReadTime is a bad idea. Why do you do that?

Upvotes: 3

parakmiakos
parakmiakos

Reputation: 3020

It looks like ID isn't a PK by itself, but along with ReadTime (based on your 3rd picture).

Therefore the index is built on the (ID,ReadTime) pair, and this index isn't used by your query.

Try adding an index on ID only.

Upvotes: 1

Related Questions