Suitable indexes for sorting by ranking function

Question

I have a table that stores the relationship between parents and children. They can change over time, and you need to keep a complete history so that I can ask how the relationship was at any time.

There is something like this in the table (I deleted several columns, primary key, etc. to reduce noise):

CREATE TABLE [tblRelation]( [dtCreated] [datetime] NOT NULL, [uidNode] [uniqueidentifier] NOT NULL, [uidParentNode] [uniqueidentifier] NOT NULL ) 

My query for getting relationships at a specific time looks like this (suppose @dt is a date-time with the right date):

 SELECT * FROM ( SELECT ROW_NUMBER() OVER (PARTITION BY r.uidNode ORDER BY r.dtCreated DESC) ix, r.* FROM [tblRelation] r WHERE (r.dtCreated < @dt) ) r WHERE r.ix = 1 

This query works well. However, performance is still not as good as we would like. Considering the implementation plan, it mainly comes down to clustered index scanning (36% of the cost) and sorting (63% of the cost).

What indexes should be used to speed up this query? Or is there a better way to fulfill this query in this table?

Answer 1

The ideal index for this query would consist of the key columns uidNode , dtCreated and include the columns of all the remaining columns in the table to make the index when you return r.* . If a query usually returns only a relatively small number of rows (which seems to be due to the WHERE r.ix = 1 filter), it might not be worthwhile to cover the index, since the cost of key searches may not outweigh the negative effects of a large index on CUD statements.

Answer 2

Window / rank functions on SQL Server 2005 are not always optimal (based on the answers here). Apparently better in SQL Server 2008

Another alternative is something like this. I will have a non-clustered index (uidNode, dtCreated) INCLUDE any other columns required by SELECT. Given what Martin Smith said about the search.

 WITH MaxPerUid AS ( SELECT MAX(r.dtCreated) AS MAXdtCreated, r.uidNode FROM MaxPerUid WHERE r.dtCreated < @dt GROUP BY r.uidNode ) SELECT ... FROM MaxPerUid M JOIN MaxPerUid R ON M.uidNode = R.uidNode AND M.MAXdtCreated = R.dtCreated