Агрегатный оконный оператор пакетного режима в SQL Server 2016 | Агрегатные оконные функции без фрейма
Содержание:
1. Агрегатный оконный оператор пакетного режима в SQL Server 2016;
2. Демонстрационные данные;
3. Агрегатные оконные функции без фрейма (Вы читаете данный раздел).
Агрегатные оконные функции без фрейма были реализованы в версии SQL Server 2005. Они позволяют вычислять агрегаты, подобные тем, что вычисляются в групповых запросах, но без сокрытия деталей. Это означает, что мы можем применять вычисления, в которых смешиваются детали и агрегаты, такие как процент от текущего значения в строке суммы по группе.
Для проверки быстродействия агрегатной оконной функции без фрейма применительно к таблице Transactions (обработка в построчном режиме данных в формате rowstore) я воспользуюсь следующим примером (назовем его Query 1).
Заметьте, что во всех своих тестах производительности я указываю для запроса SSMS параметр Discard results after execution, чтобы система не принимала в расчет время, необходимое для вывода 10 000 000 выходных строк. Для выбора этого параметра правой кнопкой мыши щелкните на пустом пространстве в окне запроса, выберите в контекстном меню пункт Query Options и установите нужный флажок в разделе Results, Grid, как показано на приведенном ниже экране.
Назначение параметра Discard results after execution для запроса SSMS
Теперь вы получаете как детали относительно каждой транзакции (идентификатор счета, идентификатор транзакции, текущее значение транзакции), так и текущую сумму на счете (которая представляет баланс счета). Как уже отмечалось, система обычно выдает более подробно представленные вычисления, в которых детали перемежаются с агрегатами, такими как отношение между значением в текущей транзакции и средним значением для счета и типа транзакции: 100.0 * val/AVG (val) OVER (PARTITION BY actid, SIGN (val)) — 100.00. Я исхожу из того, что вы уже знакомы с вариантами использования и с вычислениями, необходимыми для достижения этой цели. Поскольку моя задача — рассказать о том, как повысить быстродействие системы SQL Server 2016, я буду оперировать упрощенными, иногда искусственными формами оконных функций, не помещая их в более проработанные вычислительные конструкции. План выполнения запроса Query 1 представлен на рисунке ниже.
План для запроса Query 1 (построчный режим, применяемый к данным в формате rowstore)
Верхняя ветвь плана представляет собой упорядоченную проверку кластеризованного индекса. В ходе этой проверки данные сегментируются по значению actid (разделяющий элемент), а строки записываются в каталог на диске (временная таблица). Для каждого сегмента внутренняя часть верхнего цикла активирует среднюю и нижнюю ветви плана. Средняя часть плана предусматривает считывание данных из каталога с целью вычисления общей суммы (агрегата). Нижняя ветвь плана также предусматривает считывание данных из каталога для представления детального уровня данных. Средний оператор Nested Loops объединяет агрегатный и детализированный уровни данных.
Я собрал следующую статистику по выполнению данного запроса на своей системе: продолжительность — 47 секунд, процессор — 57 секунд, операции логического считывания — 20 Мбайт, запись — 40 Кбайт. На мой взгляд, этот план неэффективный. 47 секунд — это довольно много. Неэффективность плана отражается прежде всего в записи и считывании данных в дисковый каталог и из него. Обратите внимание: объем считанных данных составляет 20 Мбайт, а записанных данных — 40 Кбайт.
Речь в первую очередь идет об упомянутом каталоге. Но у этого плана есть два достоинства: во-первых, его можно строить на порядке индекса (отпадает необходимость в проведении явной операции сортировки), а во-вторых, это план параллельный. Но имеющиеся достоинства не компенсируют его недостатки. Естественно, если бы наш запрос был более сложным и включат в себя объединения, а также другие элементы и если бы при этом не было возможности создать вспомогательный индекс, нам пришлось бы пойти на дополнительные затраты по осуществлению сортировки средствами обработки в построчном режиме.
1.Часть I (Вы читаете данный раздел);
2. Часть II ;
3. Часть III.
Планируете сменить профессию IT-специалиста, целыми днями просиживающего за администрированием SQL Server 2016, на карьеру успешного азартного игрока? В этом случае, первое, что вам следует сделать, - посетить страничку http://avtomati-wulcan.com/igrat-na-dengi. Здесь вы сможете начать свои первые шаги на этом увлекательном поприще!
1. Агрегатный оконный оператор пакетного режима в SQL Server 2016;
2. Демонстрационные данные;
3.
Агрегатные оконные функции без фрейма были реализованы в версии SQL Server 2005. Они позволяют вычислять агрегаты, подобные тем, что вычисляются в групповых запросах, но без сокрытия деталей. Это означает, что мы можем применять вычисления, в которых смешиваются детали и агрегаты, такие как процент от текущего значения в строке суммы по группе.
Для проверки быстродействия агрегатной оконной функции без фрейма применительно к таблице Transactions (обработка в построчном режиме данных в формате rowstore) я воспользуюсь следующим примером (назовем его Query 1).
SELECT actid, tranid, val,
SUM(val) OVER(PARTITION BY actid) AS acttotal
FROM dbo.Transactions;Заметьте, что во всех своих тестах производительности я указываю для запроса SSMS параметр Discard results after execution, чтобы система не принимала в расчет время, необходимое для вывода 10 000 000 выходных строк. Для выбора этого параметра правой кнопкой мыши щелкните на пустом пространстве в окне запроса, выберите в контекстном меню пункт Query Options и установите нужный флажок в разделе Results, Grid, как показано на приведенном ниже экране.
Назначение параметра Discard results after execution для запроса SSMS
Теперь вы получаете как детали относительно каждой транзакции (идентификатор счета, идентификатор транзакции, текущее значение транзакции), так и текущую сумму на счете (которая представляет баланс счета). Как уже отмечалось, система обычно выдает более подробно представленные вычисления, в которых детали перемежаются с агрегатами, такими как отношение между значением в текущей транзакции и средним значением для счета и типа транзакции: 100.0 * val/AVG (val) OVER (PARTITION BY actid, SIGN (val)) — 100.00. Я исхожу из того, что вы уже знакомы с вариантами использования и с вычислениями, необходимыми для достижения этой цели. Поскольку моя задача — рассказать о том, как повысить быстродействие системы SQL Server 2016, я буду оперировать упрощенными, иногда искусственными формами оконных функций, не помещая их в более проработанные вычислительные конструкции. План выполнения запроса Query 1 представлен на рисунке ниже.
План для запроса Query 1 (построчный режим, применяемый к данным в формате rowstore)
Верхняя ветвь плана представляет собой упорядоченную проверку кластеризованного индекса. В ходе этой проверки данные сегментируются по значению actid (разделяющий элемент), а строки записываются в каталог на диске (временная таблица). Для каждого сегмента внутренняя часть верхнего цикла активирует среднюю и нижнюю ветви плана. Средняя часть плана предусматривает считывание данных из каталога с целью вычисления общей суммы (агрегата). Нижняя ветвь плана также предусматривает считывание данных из каталога для представления детального уровня данных. Средний оператор Nested Loops объединяет агрегатный и детализированный уровни данных.
Я собрал следующую статистику по выполнению данного запроса на своей системе: продолжительность — 47 секунд, процессор — 57 секунд, операции логического считывания — 20 Мбайт, запись — 40 Кбайт. На мой взгляд, этот план неэффективный. 47 секунд — это довольно много. Неэффективность плана отражается прежде всего в записи и считывании данных в дисковый каталог и из него. Обратите внимание: объем считанных данных составляет 20 Мбайт, а записанных данных — 40 Кбайт.
Речь в первую очередь идет об упомянутом каталоге. Но у этого плана есть два достоинства: во-первых, его можно строить на порядке индекса (отпадает необходимость в проведении явной операции сортировки), а во-вторых, это план параллельный. Но имеющиеся достоинства не компенсируют его недостатки. Естественно, если бы наш запрос был более сложным и включат в себя объединения, а также другие элементы и если бы при этом не было возможности создать вспомогательный индекс, нам пришлось бы пойти на дополнительные затраты по осуществлению сортировки средствами обработки в построчном режиме.
1.
2. Часть II ;
3. Часть III.
Планируете сменить профессию IT-специалиста, целыми днями просиживающего за администрированием SQL Server 2016, на карьеру успешного азартного игрока? В этом случае, первое, что вам следует сделать, - посетить страничку http://avtomati-wulcan.com/igrat-na-dengi. Здесь вы сможете начать свои первые шаги на этом увлекательном поприще!
