Я взял отличное решение Шона и подправил его, чтобы избежать сортировки. Я использую временную таблицу, чтобы исключить создание/заполнение временной переменной из плана выполнения, который я собираюсь опубликовать.
-- Temp table with sample data
IF OBJECT_ID('tempdb..#Something') IS NOT NULL DROP TABLE #Something;
CREATE TABLE #Something
( TicketNo INT
, ActionDate DATETIME
, OldStatus VARCHAR(50)
, NewStatus VARCHAR(50)
);
INSERT #Something VALUES
(1001, '2014-02-14 10:17:05.000', 'Assigned', 'InProgress')
, (1001, '2014-03-05 02:03:44.000', 'InProgress', 'Reply')
, (1001, '2014-03-11 10:00:14.000', 'Reply', 'Resolved')
, (1002, '2015-03-20 04:44:14.000', 'InProgress', 'Reply')
, (1002, '2015-03-21 05:40:02.000', 'Reply', 'Resolved')
-- TOP (1) Solution
SELECT s.TicketNo, s.ActionDate, s.OldStatus, s.NewStatus,
CycleTimeSeconds = DATEDIFF(SECOND, MyLag.ActionDate, s.ActionDate)
FROM #Something AS s
OUTER APPLY
(
SELECT TOP (1) ActionDate
FROM #Something s2
WHERE s2.TicketNo = s.TicketNo
AND s2.ActionDate < s.ActionDate
ORDER BY s2.ActionDate DESC
) AS MyLag;
-- Using MAX instead of TOP (1) to avoid a DESC sort operation
SELECT s.TicketNo, s.ActionDate, s.OldStatus, s.NewStatus,
CycleTimeSeconds = DATEDIFF(SECOND, MyLag.ActionDate, s.ActionDate)
FROM #Something AS s
CROSS APPLY
(
SELECT ActionDate = MAX(ActionDate)
FROM #Something s2
WHERE s2.TicketNo = s.TicketNo
AND s2.ActionDate < s.ActionDate
) AS MyLag;
Поскольку подзапрос оценивает только один столбец, мы можем использовать функцию Aggregate без GROUP BY
. Поскольку я использую Aggregate (MAX), я всегда получаю строку назад, поэтому я изменил OUTER APPLY на CROSS APPLY. В OUTER APPLY нет ничего плохого, но его изменение удаляет скалярный оператор из этого плана выполнения - никакого прироста производительности, просто более чистый план выполнения.
![введите здесь описание изображения](https://i.stack.imgur.com/45bS2.png)
У этого подхода есть очень большое дополнительное преимущество: мы не только избегаем сортировки, мы избегаем сортировки DESCending. Если бы для ActionDate
существовал индекс, оптимизатор мог бы использовать его, чтобы избежать сортировки по методу DESCending, выполнив сканирование в обратном порядке. Добавление ограничения UNIQUE для ActionDate к исходной временной переменной (что-то, что я не предлагаю, но это работает для этого примера) запускает запрос TOP (1)
, отсортированный по ActionDate DESC.
declare @Something table
(
TicketNo int
, ActionDate datetime UNIQUE
, OldStatus varchar(50)
, NewStatus varchar(50)
)
insert @Something values
(1001, '2014-02-14 10:17:05.000', 'Assigned', 'InProgress')
, (1001, '2014-03-05 02:03:44.000', 'InProgress', 'Reply')
, (1001, '2014-03-11 10:00:14.000', 'Reply', 'Resolved')
, (1002, '2015-03-20 04:44:14.000', 'InProgress', 'Reply')
, (1002, '2015-03-21 05:40:02.000', 'Reply', 'Resolved')
SELECT TOP (1) ActionDate
FROM @Something AS s2
ORDER BY s2.ActionDate DESC;
Обратите внимание на обратное сканирование:
![введите здесь описание изображения](https://i.stack.imgur.com/ywJRc.png)
Обратное сканирование убивает параллелизм. Ицик Бен-Ган обсуждает это здесь: Избегание сортировки с По убыванию.
Одно различие между упорядоченным сканированием вперед и упорядоченным обратным сканированием заключается в том, что первое потенциально может использовать параллелизм, тогда как второе в настоящее время не имеет реализации с параллелизмом в механизме хранения.
Здесь APPLY позволяет нам выполнять операцию без сортировки с параллельной обработкой в заднем кармане оптимизатора, если это необходимо. Просто еще одно доказательство того, что APPLY — это круто... не то, чтобы нужны были какие-то дополнительные доказательства ;)
person
Alan Burstein
schedule
14.02.2020