Optimize ROW_NUMBER() with date index

t-sql

Solution

You need a POC index. Have a look at the Indexing Guidelines part of SQL Server 2012: How to Write T-SQL Window Functions, Part 3.

So in your case you need to put `Price_Id` as the first column in the index.

create index IX_Samples_POC on dbo.Samples(Price_Id, date desc);

But even with the index in place there is no guarantee that SQL Server will use it. You are querying all the rows and all the columns so chances are that you will get a clustered index scan or a table scan anyway since you probably don't have all the other columns included in your index (the clustered key is included automatically).

Depending on what your table structure look like you might get a faster plan using the index if you use `row_number` in a derived table and joins the result back to the table where you fetch the rest of the columns.

Something like this assuming you have a primary key ID that is also the clustered key.

select S.*,
       T.r
from Samples as S
  inner join (
             select ID,
                    row_number() over(partition by Price_Id 
                                        order by date desc) as r
             from dbo.Samples
             ) as T
    on S.ID = T.ID;

Another option is to include the columns you need in the index. Probably not a good idea if you actually need all columns.

create index IX_Samples_POC on dbo.Samples(Price_Id, date desc) 
  include(Col1, Col2);

The major difference between a covering index compared to not using an index at all is that SQL Server has to do a sort of the rows to be able to enumerate them. The index already provides that sort.

Problem

I have this simple query which is hopefully pretty self-explanatory. ``` SELECT ROW_NUMBER() OVER (PARTITION BY Price_Id ORDER BY date DESC) r, * FROM Samples ``` My problem is that I have over 80000 records and it takes about 3 seconds to perform that query. My question is if there is anything I can do to increase performance? I am thinking of an Index, and came up with something like ``` CREATE INDEX IX_Samples_Date ON Samples (Date DESC) ``` But I have to admit I have never worked with indexes before, and my attempt above did not work.

Original source