Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
Ares_ekb Member Откуда: Екатеринбург Сообщений: 1587 |
Здравствуйте! Пытаюсь оптимизировать запрос. Первичные данные собираются на нескольких серверах (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 Вот, что получается:
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] Ответить | Цитировать Сообщить модератору |
SomewhereSomehow Member Откуда: Moscow Сообщений: 2480 Блог |
Попробую ответить по порядку. Тестовые данные, чтобы пояснять примеры (на реальных данных ТС, ситуация может быть другой, но думаю не сильно):
Можно ли избавиться от Lazy Spool? Для этого, нужно понять, зачем он вообще тут используется. Это оптимизация применяемая сервером, для того, чтобы исключить повторное обращение к таблице для подсчета агрегата и применения его к каждой строке. Этот вид спула еще называют Common Subexpression Spool. Можно сравнить планы двух запросов, они будут практически одинаковы (есть небольшое отличие в оценке в 10 строк, обусловленное тем что оптимизатор шел к этим планам разными путями).
В спуле сохраняются посчитанные для каждой группы значения агрегатов, чтобы потом соединить их с основой таблицей и получить значение агрегата напротив каждой строки таблицы. Правило отвечающее за эту оптимизацию называется GenGbApplySimple. Если его отключить и выполнить запрос, то вы увидите план, похожий на вариант 2. Отдельно подчеркну, что это не является решением и я привожу это как иллюстрацию того что происходит.
Документированных способов отключить эту оптимизацию - нет. Можно попытаться влиять на количество и стоимость вариантов, например, запретив параллельный план – тогда спула не будет, т.к. такой вариант окажется более дорогим, но на производительности это скажется в обратную сторону. Почему нет спула с 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%.
![]()
Повторимость эксперимента зависит от среды и данных Да, спил исчез и стало быстрее. Однако какой ценой! Нужно подумать, о нагрузке в целом. Представьте себе, если у вас и так не очень много памяти и тут один запрос забирает себе огромную часть. Ограничение на максимальное число памяти для запроса введено не просто так и нужно быть с этим аккуратным. В будущем, возможны большие ожидания если не ошибаюсь 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 ![]()
К сожалению, этот запрос сервер тоже не параллелит. И к сожалению, флаг трассировки 8649 недокументирован, а значит неподдерживается, кроме того трассировка требует привилегий. (Кстати, если кто-то хочет чтобы был хинт форсирующий параллельный план - голосуем тут). На тему форсирования параллельности предлагают еще такой способ, но я его не пробовал, не буду говорить. также, есть вариант, создать параллельный план при помощи флага и прибить его при помощи plan guide. В случае параллельного выполнения время сокращается вот так (для 4 потоков):
В результате всех манипуляций получается около 28 секунд - самое лучшее выполнение, против 98 секунд - время выполнения первого запроса. Примерно в 3 раза лучше. У вас скорее всего будут другие цифры и эффекты, но я постарался максимально описать общий процесс принятия решений оптимизатором и направление в котором можно копать дальше. |
||||||||||||
22 июл 13, 23:51 [14601646] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |