Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Сортировка частично отсортированных данных в плане  [new]
Ares_ekb
Member

Откуда: Екатеринбург
Сообщений: 1769
 		Здравствуйте!

Пытаюсь оптимизировать запрос. Первичные данные собираются на нескольких серверах (NODE), на каждом из которых в момент времени EVENT_TIME фиксируются события EVENT_CODE, связанные с сообщением TRACK_ID. Мне нужно сгруппировать эти протоколы по TRACK_ID, каждую группу отсортировать по EVENT_TIME и для каждого события каждого сообщения получить время прошедшее после предыдущего события (DELAY), само предыдущее событие (PREV_EVENT_CODE) и т.п.

Проблема заключается в том, что время на разных серверах может немного расходиться - достаточно даже десятков миллисекунд. Возможна такая ситуация, что на сервере А сообщение обрабатывалось с 11:22:33.123 по 11:22:33.777, а на сервере Б - с 11:22:33.555 по 11:22:33.999 - интервалы пересекаются.

Самое правильное решение конечно упорядочивать события не по времени, а по счетчику, который монотонно возрастает/уменьшается при каждом событии. Но пока это невозможно.

Поэтому сначала для сообщения на каждом сервере берется минимальное время - определяется в каком порядке сообщение проходило через узлы. И протоколы упорядочиваются сначала по узлам от первого к последнему, а потом уже по времени.

Самый тривиальный запрос с GROUP BY:
declare @a tinyint, @b datetime2(3), @c varchar(100), @d varchar(50), @e char(36), @f date, @g time(0), @h int;
SELECT @a = p.NODE, @b = p.EVENT_TIME, @c = p.EVENT_CODE, @d = p.MODULE, @e = p.TRACK_ID
      ,@h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0)
      ,@h = DATEDIFF(ms, t.RECEIVE_TIME, [EVENT_TIME])
      ,@c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
      ,@d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
FROM [dbo].[PROTOCOLS] p
INNER JOIN (SELECT TRACK_ID, NODE, MIN(EVENT_TIME) RECEIVE_TIME
              FROM [dbo].[PROTOCOLS]
              GROUP BY TRACK_ID, NODE) t ON t.TRACK_ID = p.TRACK_ID AND t.NODE = p.NODE
Избавляемся от Lazy Spool'ов с помощью LEFT JOIN:
SELECT @a = p.NODE, @b = p.EVENT_TIME, @c = p.EVENT_CODE, @d = p.MODULE, @e = p.TRACK_ID
      ,@h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0)
      ,@h = DATEDIFF(ms, t.RECEIVE_TIME, [EVENT_TIME])
      ,@c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
      ,@d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
FROM [dbo].[PROTOCOLS] p
LEFT JOIN (SELECT TRACK_ID, NODE, MIN(EVENT_TIME) RECEIVE_TIME
              FROM [dbo].[PROTOCOLS]
              GROUP BY TRACK_ID, NODE) t ON t.TRACK_ID = p.TRACK_ID AND t.NODE = p.NODE
Вместо группировки используем ROW_NUMBER()=1:
SELECT @a = p.NODE, @b = p.EVENT_TIME, @c = p.EVENT_CODE, @d = p.MODULE, @e = p.TRACK_ID
      ,@h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0)
      ,@h = DATEDIFF(ms, t.RECEIVE_TIME, [EVENT_TIME])
      ,@c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
      ,@d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
FROM [dbo].[PROTOCOLS] p
INNER JOIN (SELECT TRACK_ID, NODE, RECEIVE_TIME FROM (
              SELECT TRACK_ID, NODE, EVENT_TIME RECEIVE_TIME
                    ,ROW_NUMBER() OVER (PARTITION BY TRACK_ID, NODE ORDER BY EVENT_TIME) RN
                FROM [dbo].[PROTOCOLS]) t WHERE RN = 1) t ON t.TRACK_ID = p.TRACK_ID AND t.NODE = p.NODE OPTION (MERGE JOIN)
Вместо группировки FIRST_VALUE(EVENT_TIME):
SELECT @a = p.NODE, @b = p.EVENT_TIME, @c = p.EVENT_CODE, @d = p.MODULE, @e = p.TRACK_ID
      ,@h = ISNULL(DATEDIFF(ms, LAG(p.[EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, p.EVENT_TIME), p.[EVENT_TIME]), 0)
      ,@h = DATEDIFF(ms, t.RECEIVE_TIME, p.[EVENT_TIME])
      ,@c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, p.EVENT_TIME)
      ,@d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, p.EVENT_TIME)
FROM [dbo].[PROTOCOLS] p
INNER JOIN (SELECT ID, TRACK_ID, NODE, EVENT_TIME, FIRST_VALUE(EVENT_TIME) OVER (PARTITION BY TRACK_ID, NODE ORDER BY EVENT_TIME) RECEIVE_TIME
              FROM [dbo].[PROTOCOLS]) t ON t.ID = p.ID
Вариант без JOIN'ов:
SELECT @a = p.NODE, @b = p.EVENT_TIME, @c = p.EVENT_CODE, @d = p.MODULE, @e = p.TRACK_ID
      ,@h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY p.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0)
      ,@h = DATEDIFF(ms, p.RECEIVE_TIME, [EVENT_TIME])
      ,@c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY p.RECEIVE_TIME, EVENT_TIME)
      ,@d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY p.RECEIVE_TIME, EVENT_TIME)
FROM (SELECT p.NODE, p.EVENT_TIME, p.EVENT_CODE, p.MODULE, p.TRACK_ID, p.EVENT_DATE, p.EVENT_TIME_OF_DAY
            ,FIRST_VALUE(EVENT_TIME) OVER (PARTITION BY TRACK_ID, NODE ORDER BY EVENT_TIME) RECEIVE_TIME
        FROM [dbo].[PROTOCOLS] p) p

Вот, что получается:
Таблица "PROTOCOLS". Число просмотров 5, логических чтений 89463, физических чтений 2, упреждающих чтений 85740, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 12, логических чтений 17933938, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Время ЦП = 986984 мс, затраченное время = 350335 мс.

Таблица "PROTOCOLS". Число просмотров 10, логических чтений 146769, физических чтений 3, упреждающих чтений 71030, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Время ЦП = 857827 мс, затраченное время = 325322 мс.

Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "PROTOCOLS". Число просмотров 2, логических чтений 142076, физических чтений 2, упреждающих чтений 71037, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Время ЦП = 654469 мс, затраченное время = 680831 мс.

Таблица "PROTOCOLS". Число просмотров 10, логических чтений 174157, физических чтений 3, упреждающих чтений 169370, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 7549781, логических чтений 40074374, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Время ЦП = 1792625 мс, затраченное время = 625052 мс.

Таблица "PROTOCOLS". Число просмотров 5, логических чтений 89478, физических чтений 2, упреждающих чтений 85740, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 7549781, логических чтений 40074374, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Время ЦП = 1480079 мс, затраченное время = 484221 мс.

2-ой вариант с GROUP BY и LEFT JOIN самый быстрый. Можно как-нибудь избавиться от Lazy Spool без хаков типа использования LEFT JOIN?
3-ий вариант с ROW_NUMBER()=1 использует меньше всего процессорного времени, но недостаточно распараллеленный, из-за чего проигрывает. Интересно, можно ли его как-то ускорить?

В планах выдается предупреждение, что для сортировки используется tempdb. Можно ли это как-то исправить кроме как увеличением количества памяти?

И наверное самый главный вопрос. После JOIN'а события уже упорядочены по TRACK_ID, зачем MS SQL сортирует по нему повторно? Почему нельзя просто досортировать? Я думаю, это давало бы гигантский выигрыш в производительности!

К сообщению приложен файл (double-over.rar - 18Kb) cкачать
21 июл 13, 16:24    [14595094]     Ответить | Цитировать Сообщить модератору
 Re: Сортировка частично отсортированных данных в плане  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
 		Попробую ответить по порядку.
Тестовые данные, чтобы пояснять примеры (на реальных данных ТС, ситуация может быть другой, но думаю не сильно):
+ Тестовые данные
use master;
go
if db_id('test') is not null drop database test;
create database test;
go
use test;
go
if object_id('[dbo].[PROTOCOLS]') is not null drop table [dbo].[PROTOCOLS]; --!! be careful, droping table - check current DB
create table [dbo].[PROTOCOLS] (
       NODE tinyint not null, 
       EVENT_TIME datetime2(3) not null, 
       EVENT_CODE varchar(100) not null, 
       MODULE varchar(50) not null, 
       TRACK_ID char(36) not null
);
go
with nums(n) as (
       select top(6788355) row_number() over(order by(select null)) from master..spt_values v1,master..spt_values v2,master..spt_values v3
)
insert into [dbo].[PROTOCOLS]
select
       n%5,
       dateadd(ms,n,'20000101'),
       'CODE'+str(n%9,1),
       'MODEULE'+str(n%9,1),
       n%(1089310/5)
from nums;
go
create clustered index IX_PROTOCOLS_TRACK_ID_NODE_EVENT_TIME_ID on [dbo].[PROTOCOLS](TRACK_ID, NODE, EVENT_TIME);
go


Можно ли избавиться от Lazy Spool?
Для этого, нужно понять, зачем он вообще тут используется. Это оптимизация применяемая сервером, для того, чтобы исключить повторное обращение к таблице для подсчета агрегата и применения его к каждой строке. Этот вид спула еще называют Common Subexpression Spool.
Можно сравнить планы двух запросов, они будут практически одинаковы (есть небольшое отличие в оценке в 10 строк, обусловленное тем что оптимизатор шел к этим планам разными путями).
+ запрос 1 (+переписанный, иллюстрирующий план и причину спула)

declare @a tinyint, @b datetime2(3), @c varchar(100), @d varchar(50), @e char(36), @f date, @g time(0), @h int;
SELECT 
      @a = p.NODE, 
      @b = p.EVENT_TIME, 
      @c = p.EVENT_CODE, 
      @d = p.MODULE, 
      @e = p.TRACK_ID,
      @h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0),
      @h = DATEDIFF(ms, t.RECEIVE_TIME, [EVENT_TIME]),
      @c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME),
      @d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
FROM 
      [dbo].[PROTOCOLS] p
      INNER JOIN (
            SELECT TRACK_ID, NODE, MIN(EVENT_TIME) RECEIVE_TIME
        FROM [dbo].[PROTOCOLS]
            GROUP BY TRACK_ID, NODE
      ) t ON t.TRACK_ID = p.TRACK_ID AND t.NODE = p.NODE;
go
declare @a tinyint, @b datetime2(3), @c varchar(100), @d varchar(50), @e char(36), @f date, @g time(0), @h int;
with p as
(
select 
	*,
	RECEIVE_TIME = min(EVENT_TIME) over (partition by p.TRACK_ID, p.NODE)
from
	[dbo].[PROTOCOLS] p
)
SELECT 
      @a = p.NODE, 
      @b = p.EVENT_TIME, 
      @c = p.EVENT_CODE, 
      @d = p.MODULE, 
      @e = p.TRACK_ID,
      @h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY p.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0),
      @h = DATEDIFF(ms, p.RECEIVE_TIME, [EVENT_TIME]),
      @c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY p.RECEIVE_TIME, EVENT_TIME),
      @d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY p.RECEIVE_TIME, EVENT_TIME)
FROM 
      p;
go

+ план
Картинка с другого сайта.

В спуле сохраняются посчитанные для каждой группы значения агрегатов, чтобы потом соединить их с основой таблицей и получить значение агрегата напротив каждой строки таблицы.

Правило отвечающее за эту оптимизацию называется GenGbApplySimple. Если его отключить и выполнить запрос, то вы увидите план, похожий на вариант 2. Отдельно подчеркну, что это не является решением и я привожу это как иллюстрацию того что происходит.
+ Отключаем оптимизацию GenGbApplySimple
declare @a tinyint, @b datetime2(3), @c varchar(100), @d varchar(50), @e char(36), @f date, @g time(0), @h int;
SELECT 
      @a = p.NODE, 
      @b = p.EVENT_TIME, 
      @c = p.EVENT_CODE, 
      @d = p.MODULE, 
      @e = p.TRACK_ID,
      @h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0),
      @h = DATEDIFF(ms, t.RECEIVE_TIME, [EVENT_TIME]),
      @c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME),
      @d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
FROM 
      [dbo].[PROTOCOLS] p
      INNER JOIN (
            SELECT TRACK_ID, NODE, MIN(EVENT_TIME) RECEIVE_TIME
        FROM [dbo].[PROTOCOLS]
            GROUP BY TRACK_ID, NODE
      ) t ON t.TRACK_ID = p.TRACK_ID AND t.NODE = p.NODE
option(recompile, queryruleoff GenGbApplySimple)
;
go

+ План без GenGbApplySimple
Картинка с другого сайта.


Документированных способов отключить эту оптимизацию - нет.
Можно попытаться влиять на количество и стоимость вариантов, например, запретив параллельный план – тогда спула не будет, т.к. такой вариант окажется более дорогим, но на производительности это скажется в обратную сторону.

Почему нет спула с Left Join. Дело в том, что Common Subexpression Spool применяется для каждой строки, если у нас Left Join – это необязательно. По этому, такой «хак», как вы выразились избавляет от спула, но, обратите внимание, у вас теперь два обращения к таблице PROTOCOLS.

Почему не параллелится вариант с ROW_NUMBER() и можно ли его ускорить
План с ROW_NUMBER не параллелится по той причине, что оптимизатор считает, что в данном случае никакого выигрыша не будет. Возможно, это происходит по причине заниженной оценки, в результате фильтра по row_numer().
Картинка с другого сайта.

Есть флаг трассировки 8649, который позволяет построить параллельный план (если такой возможен в принципе). Можете попробовать выполнить запрос с этим флагом и без него на ваших данных. На тестовых данных в моей тестовой среде выигрыш получился примерно 133003 ms последовательный - против 97190 ms параллельный. Не слишком впечатляет.

По поводу spill и tempdb.
Если не брать план, в котором оценка искажается из-за фильтра по row_number(), то во всех остальных планах, оценка почти соответствует действительности. Поэтому, можно исключить spill из-за неверной оценки и недооценки памяти. Скорее памяти для запроса действительно не хватает. На моей домашней машине, где я сейчас выполняю тесты 6 ГБ памяти всего и ограничений по количеству доступной памяти серверу нет, однако, у меня тоже присутствует spill. Вот сколько данных рассчитывается запихнуть в сортировку в моих тестах
Картинка с другого сайта.
Судя по вашему плану у вас чуть меньше, но примерно порядок такой же 790 МБ. Если сервер небогат на память, то спил вполне вероятен. По умолчанию, сервер выделяет одному запросу 25% от памяти в дефолтной рабочей группе. Соответственно, даже если память у сервера есть запрос не получит ее всю.
Вот эксперимент на моей машине с увеличением максимального гранта с 25% до 75%.
+ увеличиваем максимальный грант запросу
set statistics time on
declare @a tinyint, @b datetime2(3), @c varchar(100), @d varchar(50), @e char(36), @f date, @g time(0), @h int;
SELECT 
      @a = p.NODE, 
      @b = p.EVENT_TIME, 
      @c = p.EVENT_CODE, 
      @d = p.MODULE, 
      @e = p.TRACK_ID,
      @h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0),
      @h = DATEDIFF(ms, t.RECEIVE_TIME, [EVENT_TIME]),
      @c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME),
      @d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
FROM 
      [dbo].[PROTOCOLS] p
      INNER JOIN (
            SELECT TRACK_ID, NODE, MIN(EVENT_TIME) RECEIVE_TIME
        FROM [dbo].[PROTOCOLS]
            GROUP BY TRACK_ID, NODE
      ) t ON t.TRACK_ID = p.TRACK_ID AND t.NODE = p.NODE
;
set statistics time off
go
alter workload group [default] with (request_max_memory_grant_percent = 75);
alter resource governor reconfigure;
go
set statistics time on
declare @a tinyint, @b datetime2(3), @c varchar(100), @d varchar(50), @e char(36), @f date, @g time(0), @h int;
SELECT 
      @a = p.NODE, 
      @b = p.EVENT_TIME, 
      @c = p.EVENT_CODE, 
      @d = p.MODULE, 
      @e = p.TRACK_ID,
      @h = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME), [EVENT_TIME]), 0),
      @h = DATEDIFF(ms, t.RECEIVE_TIME, [EVENT_TIME]),
      @c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME),
      @d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, EVENT_TIME)
FROM 
      [dbo].[PROTOCOLS] p
      INNER JOIN (
            SELECT TRACK_ID, NODE, MIN(EVENT_TIME) RECEIVE_TIME
        FROM [dbo].[PROTOCOLS]
            GROUP BY TRACK_ID, NODE
      ) t ON t.TRACK_ID = p.TRACK_ID AND t.NODE = p.NODE
;
set statistics time off
go
alter workload group [default] with (request_max_memory_grant_percent = 25);
alter resource governor reconfigure;
go


Картинка с другого сайта.

статистика
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 139246 ms, elapsed time = 97809 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 113394 ms, elapsed time = 39061 ms.

Повторимость эксперимента зависит от среды и данных

Да, спил исчез и стало быстрее. Однако какой ценой! Нужно подумать, о нагрузке в целом. Представьте себе, если у вас и так не очень много памяти и тут один запрос забирает себе огромную часть. Ограничение на максимальное число памяти для запроса введено не просто так и нужно быть с этим аккуратным. В будущем, возможны большие ожидания если не ошибаюсь RESOURCE_SEMAPHORE, которые будут свидетельствовать о том, что либо этот запрос ждет пока освободится память, либо другие ждут пока этот запрос освободит память. В общем, я бы лучше постарался избавиться от сортировки и улучшить план, нежели чем бороться со спилом пытаясь сортировку полностью запихнуть в память.

Почему нельзя до-сортировать
Если посмотреть по каким полям идет сортировка, то видно, что одно из полей это Expr1007, которое ни что иное, как пред посчитанный агрегат, которого нет в индексе и который идет средним в сортировке. Сначала, для обеспечения partition by нужно отсортировать по TRACK_ID, потом первым в order by идет Expr1007 и потом уже EVENT_TIME. Ни один индекс напрямую не подходит.

Последние два варианта я не тестировал, но видно, что они используют on-disk window spool (посмотрите на кол-во чтений Worktable в примере 2 (0 чтений) и в примерах 4,5 (много чтений)). Сервер использует диск, если в качестве фрейма указан тип range, а не row. Если не указано ничего (как у вас в запросе), то используется как раз range. Так что, лучше в функции first_value явно укажите rows between unbound preceding. Должно быть шустрее.

Пробуем избавиться от сортировки
Я бы попробовал сделать следующее. Надеюсь, ничего не напутал в логике.
1. Посчитать заранее агрегаты по группам.
2. В рамках каждой группы посчитать остальное.
set statistics time on
if object_id('tempdb..#t') is not null drop table #t;
SELECT TRACK_ID, NODE, MIN(EVENT_TIME) RECEIVE_TIME into #t FROM [dbo].[PROTOCOLS] GROUP BY TRACK_ID, NODE;
create clustered index ixc on #t(TRACK_ID, NODE, RECEIVE_TIME);
declare @a tinyint, @b datetime2(3), @c varchar(100), @d varchar(50), @e char(36), @f date, @g time(0), @h int;
select
      @a = p.NODE,
      @b = p.EVENT_TIME, 
      @c = p.EVENT_CODE, 
      @d = p.MODULE, 
      @e = p.TRACK_ID,
	  @h = h1,
	  @h = h2,
	  @c = c,
	  @d = d
from 
	#t t
	cross apply (
		SELECT 
			  p.NODE,
			  p.EVENT_TIME, 
			  p.EVENT_CODE, 
			  p.MODULE, 
			  p.TRACK_ID,
			  h1 = ISNULL(DATEDIFF(ms, LAG([EVENT_TIME]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, p.EVENT_TIME), [EVENT_TIME]), 0),
			  h2 = DATEDIFF(ms, t.RECEIVE_TIME, [EVENT_TIME]),
			  c = LAG([EVENT_CODE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, p.EVENT_TIME),
			  d = LAG([MODULE]) OVER (PARTITION BY p.TRACK_ID ORDER BY t.RECEIVE_TIME, p.EVENT_TIME)
		FROM 
			  [dbo].[PROTOCOLS] p
		where 
			t.TRACK_ID = p.TRACK_ID AND t.NODE = p.NODE
	) p
--option(querytraceon 8649)
set statistics time off
go

Картинка с другого сайта.
Статистика
SQL Server Execution Times:
CPU time = 8003 ms, elapsed time = 8096 ms.
SQL Server Execution Times:
CPU time = 5040 ms, elapsed time = 2407 ms.
SQL Server parse and compile time:
CPU time = 343 ms, elapsed time = 350 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 46520 ms, elapsed time = 46666 ms.


К сожалению, этот запрос сервер тоже не параллелит. И к сожалению, флаг трассировки 8649 недокументирован, а значит неподдерживается, кроме того трассировка требует привилегий. (Кстати, если кто-то хочет чтобы был хинт форсирующий параллельный план - голосуем тут). На тему форсирования параллельности предлагают еще такой способ, но я его не пробовал, не буду говорить.
также, есть вариант, создать параллельный план при помощи флага и прибить его при помощи plan guide.

В случае параллельного выполнения время сокращается вот так (для 4 потоков):
статистика параллельного выполнения
SQL Server Execution Times:
CPU time = 8065 ms, elapsed time = 8109 ms.
SQL Server Execution Times:
CPU time = 5195 ms, elapsed time = 2375 ms.
SQL Server parse and compile time:
CPU time = 358 ms, elapsed time = 360 ms.
(1 row(s) affected)
SQL Server Execution Times:
CPU time = 57596 ms, elapsed time = 17455 ms.


В результате всех манипуляций получается около 28 секунд - самое лучшее выполнение, против 98 секунд - время выполнения первого запроса. Примерно в 3 раза лучше.
У вас скорее всего будут другие цифры и эффекты, но я постарался максимально описать общий процесс принятия решений оптимизатором и направление в котором можно копать дальше.
22 июл 13, 23:51    [14601646]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить