在SQL Server中优化ROW_NUMBER() [英] Optimizing ROW_NUMBER() in SQL Server
问题描述
我们有许多机器会不定期地将数据记录到数据库中.对于每个记录,我想获取从此记录到上一个记录之间的时间段.
We have a number of machines which record data into a database at sporadic intervals. For each record, I'd like to obtain the time period between this recording and the previous recording.
我可以使用ROW_NUMBER进行以下操作:
I can do this using 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
问题是,它真的很慢(在大约有1万个条目的表上花费了几分钟)-我尝试在Machine_ID和Date_Time上创建单独的索引,并使用单个连接索引,但是没有帮助
The problem is, it goes really slow (several minutes on a table with about 10k entries) - I tried creating separate indicies on Machine_ID and Date_Time, and a single joined-index, but nothing helps.
反正有没有重写此查询以使其运行得更快?
Is there anyway to rewrite this query to go faster?
推荐答案
与该版本相比如何?:
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
还是这个版本?:
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
在Machine_ID,Date_Time上使用索引,并且为了获得正确的结果,我都认为这是唯一的.
Both would perform best with an index on Machine_ID, Date_Time and for correct results, I'm assuming that this is unique.
您没有提到*中隐藏的内容,这有时可能意味着很多,因为Machine_ID和Date_Time索引通常不会被覆盖,并且如果您那里有很多列或它们有很多数据,. ..
You haven't mentioned what is hidden away in * and that can sometimes means a lot since a Machine_ID, Date_Time index will not generally be covering and if you have a lot of columns there or they have a lot of data, ...
这篇关于在SQL Server中优化ROW_NUMBER()的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!