Optimizing ROW_NUMBER () in SQL Server

Question

We have several machines that write data to the database at intervals. For each record, I would like to get the time interval between this record and the previous record.

I can do this with ROW_NUMBER as follows:

WITH TempTable AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY Machine_ID ORDER BY Date_Time) AS Ordering FROM dbo.DataTable ) SELECT [Current].*, Previous.Date_Time AS PreviousDateTime FROM TempTable AS [Current] INNER JOIN TempTable AS Previous ON [Current].Machine_ID = Previous.Machine_ID AND Previous.Ordering = [Current].Ordering + 1 

The problem is that it goes very slowly (a few minutes in a table with about 10 thousand records). I tried to create separate pointers to Machine_ID and Date_Time and one combined index, but nothing helps.

Is there a way to rewrite this query faster?

Answer 1

How to compare this with this version ?:

 SELECT x.* ,(SELECT MAX(Date_Time) FROM dbo.DataTable WHERE Machine_ID = x.Machine_ID AND Date_Time < x.Date_Time ) AS PreviousDateTime FROM dbo.DataTable AS x 

Or is this version ?:

 SELECT x.* ,triang_join.PreviousDateTime FROM dbo.DataTable AS x INNER JOIN ( SELECT l.Machine_ID, l.Date_Time, MAX(r.Date_Time) AS PreviousDateTime FROM dbo.DataTable AS l LEFT JOIN dbo.DataTable AS r ON l.Machine_ID = r.Machine_ID AND l.Date_Time > r.Date_Time GROUP BY l.Machine_ID, l.Date_Time ) AS triang_join ON triang_join.Machine_ID = x.Machine_ID AND triang_join.Date_Time = x.Date_Time 

Both will work better with the Machine_ID, Date_Time index, and to get the right results, I assume this is unique.

You did not mention what is hidden in *, and this can sometimes mean a lot, since the Machine_ID, Date_Time index is usually not distributed, and if you have many columns or they have a lot of data, ...

Answer 2

This section and the order of ROW_NUMBER () require an index on (Machine_ID, Date_Time) to satisfy in one pass:

 CREATE INDEX idxMachineIDDateTime ON DataTable (Machine_ID, Date_Time); 

Separate indexes on Machine_ID and Date_Time will not help much, if any.

Answer 3

If the number of rows in dbo.DataTable is large, then it is likely that you encountered a problem due to the fact that the CTE itself joins itself. There is a blog post explaining the problem in detail here

Sometimes in such cases, I resorted to creating a temporary table to insert the result of the CTE query into, and then performing joins with this temporary table (although this was usually the case when there are a large number of joins against the temp table - in the case of a single join, the performance difference will be less noticeable)

Answer 4

I had some strange performance issues using CTE in SQL Server 2005. In many cases, replacing CTE with a real temporary table solved the problem.

I would try this before moving on using CTE.

I never found any explanation of the performance problems that I saw, and actually did not have time to figure out the main reasons. However, I always suspected that the engine could not optimize the CTE in the same way that it could optimize a temporary table (which can be indexed if more optimization is required).

Update

After your comment that this is a view, I would first test the query using the temp table to make sure this works better.

If so, and using a stored procedure is not an option, you might consider making the current CTE an indexed / materialized representation. You will want to read this topic before taking this path, as it is a good idea, it depends on many factors, not least of how often the data is updated.

Answer 5

What if you use a trigger to store the last timestamp, subtract each time to get the difference?

Answer 6

If you need this data often rather than calculating it every time you retrieve the data, why not add a column and calculate / fill it when you add a row?

(a complex Remus index will make the request quick; running it only once should make it even faster).