Оптимизация ROW_NUMBER () в SQL Server
У нас есть много машин, которые записывают данные в базу данных в спорадических интервалах. Для каждой записи я хотел бы получить период времени между этой записью и предыдущей записью.
Я могу сделать это использование ROW_NUMBER следующим образом:
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
Проблема, она действительно не торопится (несколько минут на таблице с приблизительно 10k записи) - я пытался создать отдельный indicies на Machine_ID и Date_Time и единственном присоединенном индексе, но ничто не помогает.
Там должен так или иначе переписать этот запрос для движения быстрее?
6 ответов
Как он соотносится с этой версией?:
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, и для получения правильных результатов я предполагаю, что это уникально.
Вы не упомянули, что скрыто в *, и это иногда может иметь большое значение, поскольку индекс Machine_ID, Date_Time обычно не покрывает, и если у вас много столбцов или в них много данных,. ..
У меня были странные проблемы с производительностью при использовании CTE в SQL Server 2005. Во многих случаях замена CTE реальной временной таблицей решала проблему.
Я бы попробовал это, прежде чем продолжать использовать CTE.
Я так и не нашел объяснения наблюдаемым мною проблемам с производительностью, и у меня действительно не было времени разбираться в их первопричинах. Однако я всегда подозревал, что движок не может оптимизировать CTE так же, как он может оптимизировать временную таблицу (которую можно проиндексировать, если требуется дополнительная оптимизация).
Обновление
После вашего комментария о том, что это представление, я бы сначала протестировал запрос с помощью временной таблицы, чтобы увидеть, работает ли это лучше.
Если это так, и использование сохраненной процедуры не является вариантом, вы можете подумать о том, чтобы превратить текущий CTE в индексированное / материализованное представление. Вы захотите ознакомиться с предметом, прежде чем идти по этому пути, так как будет ли это хорошей идеей, зависит от множества факторов, не в последнюю очередь от того, как часто данные обновляются.
Что делать, если вы используете триггер сохранить последнюю метку времени и каждый раз вычитать, чтобы получить разницу?
Если вам часто требуются эти данные, а не вычислять их каждый раз, когда вы их извлекаете, почему бы не добавить столбец и не вычислять / заполнять его всякий раз, когда добавляется строка?
(Состав Ремуса index сделает запрос быстрым; его однократный запуск должен сделать его еще быстрее.)
Если количество строк в dbo.DataTable велико, вероятно, вы столкнулись с проблемой из-за самостоятельного присоединения CTE к самому себе.В блоге есть сообщение, в котором подробно объясняется проблема здесь
. Иногда в таких случаях я прибегал к созданию временной таблицы, чтобы вставить результат запроса CTE, а затем выполнять соединения с этой временной таблицей (хотя обычно это было для случаев, когда требуется большое количество объединений с временной таблицей - в случае одного соединения разница в производительности будет менее заметной)
Заданные разделение и порядок ROW_NUMBER() требуют индекса на (Machine_ID, Date_Time) для удовлетворения за один проход:
CREATE INDEX idxMachineIDDateTime ON DataTable (Machine_ID, Date_Time);
Раздельные индексы на Machine_ID и Date_Time помогут мало, если вообще помогут.