I've got this simple table: RecordID (int PK), UserID (int FK), DTime (datetime), Length (int)

I need to run a simple query:

SELECT TOP(1) * FROM uTable WHERE UserID = @userid ORDER BY DTime DESC

The query is run using a Stored Procedure on SQL Server 2008 R2.

The objective is to get the last record for @userid in terms of DTime.

While checking the execution plans I've noticed the sorting (ORDER BY) is almost 80% of the cost to run this query (which is the most "expensive" query on my DB for now) - how could I change the query so I won't use ORDER BY and thus eliminate that heavy cost.



The generic way to access a table in order without a sort is a clustered index, which physically stores the rows in that order. However a table can only have one clustered index. From the documentation:

PRIMARY KEY constraints create clustered indexes automatically if no clustered index already exists on the table and a nonclustered index is not specified when you create the PRIMARY KEY constraint.

So if this is your main access pattern for this table, then perhaps making the PK unique nonclustered and (DTime desc, UserID) a composite clustered index is the way to go. But be careful not to optimize for one access pattern at the expense of any others (e.g. is there another part of your app that wants fast lookups by RecordID, you would want to make sure that wasn't suboptimal now).

| improve this answer | |
  • Wouldn't you want the leading column of the composite index to be UserID since that's what the equality predicate is on? – Justin Cave Apr 6 '11 at 18:30
  • You're probably right :-) – Gaius Apr 6 '11 at 18:32
  • wait wait... so what should I do? :) UserID can't be a PK (it can be duplicate value). Should I remove RecordID (the PK) and us (UserID, DTime) as PK? Where as DTime is DataTime? – roman Apr 6 '11 at 20:45
  • If you create the table without a PK but with a clustered index, then you can add the PK as a unique nonclustered. – Gaius Apr 6 '11 at 20:59

What is the plan for this query? Unless you're executing it orders of magnitude more than any other query in the database, which would seem to indicate a separate problem, it shocks me that this is the most expensive query in the database. Is there a composite index on (UserID, DTime)? Because it should be trivially costly to use such an index for this query.

| improve this answer | |
  • That's the query that would run most of the times on the DB. There is only an index on the PK (which is RecordID). Should I remove it and use (UserID, Dtime) as the index and PK? – roman Apr 6 '11 at 20:46
  • A table can have multiple indexes (it can only have one clustered index). The simplest approach would be to create a non-clustered index on (UserID, DTime desc)-- if there is only an index on the primary key now, creating this new index should dramatically increase performance. UserID doesn't need to be unique to be part of an index (nor does the combination of UserID and DTime need to be unique), you just need to ensure that the index is non-unique if that is the case. – Justin Cave Apr 6 '11 at 20:50

Create an index on DTime. Then run:

SELECT TOP(1) * FROM uTable WHERE DTime = max(DTime)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.