На мой взгляд, самое примечательное усовершенствование, реализованное в версии SQL Server 2016, — это включение в нее оператора пакетного режима Window Aggregate, который позволяет значительно повысить производительность системы при выполнении большинства оконных функций. Данная статья завершает серию, посвященную этому оператору. В первой статье, где речь шла о пакетной обработке, я показал, как можно пользоваться ее достоинствами вне зависимости от того, представлены ли обрабатываемые данные в формате columnstore (см. Windows IT Pro/RE № 9 за 2016 год). Кроме того, я затронул проблему бесфреймовых оконных агрегатов и привел более высокие показатели быстродействия, получаемые при использовании оператора Window Aggregate. Во второй статье серии, опубликованной в предыдущем номере журнала, мы рассмотрели ранжирующие и статистические оконные функции. На этот раз речь пойдет об агрегирующих оконных функциях с фреймами, а также оконных функциях смещения.
Тестовые данные
В завершающей статье серии я буду использовать те же тестовые данные, что и в первых двух статьях. Если у вас их нет под рукой, сформируйте такие данные с помощью кода, приведенного в листинге 1.
Напомню, что тестовые данные создаются в базе данных с именем testwindow. Имеется три таблицы, содержащие идентичные копии данных, которые состоят из 10 000 000 строк, представляющих транзакции на банковских счетах (500 счетов на 20 000 транзакций на каждом счету). В таблице Transactions используется только построчное представление данных с кластеризованным индексом двоичного дерева, определенным в списке ключей (actid, tranid). Запросы к данной таблице сегодня допускают обработку данных исключительно в построчном режиме; это связано с требованием, в соответствии с которым пакетный режим обработки данных может быть применен лишь при наличии хотя бы одного индекса columnstore. Поэтому я воспользуюсь этой таблицей для демонстрации обработки данных в представлении rowstore в построчном режиме. В таблице TransactionsCS имеется только кластеризованный индекс columnstore, поэтому я буду использовать ее, когда мне нужно будет продемонстрировать обработку данных в представлении columnstore в пакетном режиме. Таблица TransactionsDCS организована таким же образом, как таблица Transactions (кластеризованный индекс двоичного дерева), но в ней еще имеется пустой отфильтрованный индекс columnstore, позволяющий обеспечить возможность пакетной обработки. Я буду использовать эту таблицу, когда мне нужно будет продемонстрировать обработку в пакетном режиме данных в представлении rowstore.
Агрегирующие оконные функции с фреймом
Агрегирующие оконные функции с фреймом дают возможность выполнять такие классические расчеты с целью анализа данных, как вычисление нарастающих итогов, показателей за период с начала года по указанную дату, с начала месяца по указанную дату, скользящих средних показателей и многие другие. Если только вы используете разделители фреймов UNBOUNDED и CURRENT ROW, существует возможность, что оптимизатор SQL Server воспользуется оператором пакетного режима Window Aggregate. В случае применения других разделителей возможности оптимизатора ограничены. Далее в статье будут представлены различные категории фреймов и способы их оптимизации.
Строки с разделителями UNBOUNDED и CURRENT ROW
Пожалуй, чаще других используется такая спецификация фрейма: ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (или в более краткой форме — ROWS UNBOUNDED PRECEDING). Она представляет строки от начала раздела (точка нижней границы исключается) и до текущей строки. Это фрейм, который указывается, когда мы хотим вычислить промежуточную сумму. Спецификация фрейма ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING, которая используется не столь часто, оптимизируется аналогичным образом.
Запрос в листинге 2 (я буду называть его Query 1) к таблице Transactions (rowstore) обеспечивает получение простой промежуточной суммы.
План выполнения этого запроса показан на рисунке 1.
.jpg) |
| Рисунок 1. План выполнения запроса Query 1 (ROWS, построчный режим обработки данных в представлении rowstore) |
Поскольку в таблице Transactions нет индекса columnstore, оптимизатор в данном случае может использовать только построчный режим обработки данных. Этот план последовательный. В ходе его реализации выполняется упорядоченная проверка кластеризованного индекса, и при таком подходе нет необходимости в явной сортировке строк при расчете оконной функции. План предусматривает использование операторов Segment и Sequence Project для определения числа строк. Таким образом выясняется, какие строки являются частью фрейма текущей строки. Сведения о том, как вычисляется количество строк, можно найти во второй статье серии. Далее в плане предусмотрено сегментирование строк для получения агрегирующего значения в соответствии с содержимым разделяющего столбца actid. Для этого используется еще один оператор Segment.
Затем планируется запись строк в буфер (оператор Window Spool). Предполагается, что буфер окна содержит фрейм строк, который будет агрегироваться оператором Stream Aggregate. Поскольку фрейм имеет спецификацию ROWS UNBOUNDED PRECEDING, теоретически буфер окна должен содержать одну строку для первой строки раздела, две строки для второй и т. д.; для n-ной строки предполагается наличие n строк. Если бы дела обстояли именно таким образом, буфер должен был бы иметь (n+n2)/2 строк для раздела, содержащего n строк, и, соответственно, масштабироваться квадратично.
К счастью, всякий раз, когда фрейм начинается со спецификации UNBOUNDED PRECEDING, выбирается ускоренный порядок оптимизации: берется результат предыдущей строки и вычисляется новая промежуточная сумма посредством добавления значения текущей строки. Любопытно здесь вот что: почему в буфер поступает 10 000 000 строк, а выходит их из него в два раза больше?
Оказывается, поскольку оператор Stream Aggregate первоначально предназначался для групповых запросов, он вычисляет групповые результаты и не учитывает детали. Но в связи с тем, что оконные функции не предусматривают отбрасывание деталей, план наряду с групповой строкой включает в буфер строку деталей, чтобы возвратить как детали, так и результат обобщения. Таким образом, хотя ускоренная оптимизация обеспечивает линейное масштабирование, сохраняется возможность более эффективной оптимизации посредством, с одной стороны, отказа от записи в буфер двух строк на каждую базовую строку, а с другой — полного отказа от буфера при использовании данного классического фрейма.
При выполнении данного запроса на своем компьютере я получил следующие статистические данные: продолжительность — 28 секунд, процессор — 28 секунд, логические операции считывания — 31К, записи — 0.
Для демонстрации обработки в пакетном режиме данных в представлении columnstore с помощью нового оператора Window Aggregate я буду использовать запрос, приведенный в листинге 3 (буду называть его Query 2).
План выполнения данного запроса представлен на рисунке 2.
.jpg) |
| Рисунок 2. План выполнения запроса Query 2 (ROWS, пакетный режим обработки данных в представлении columnstore) |
На этот раз мы имеем дело с параллельным планом, реализующим обработку данных в пакетном режиме. Данные извлекаются из индекса columnstore, отсюда снижение издержек на операции ввода-вывода; однако необходимость сортировки данных для вычисления оконной функции сохраняется, хотя при этом используется оператор пакетного режима Sort. Затем почти все чудеса происходят с привлечением оператора пакетного режима Window Aggregate; он заменяет следующие пять операторов из предыдущего плана: Segment, Sequence Project, Segment, Window Spool и Stream Aggregate. В дополнение к очевидным достоинствам пакетного режима обработки этот оператор обеспечивает оптимизированный путь кода для всех оконных функций. Снижающие эффективность обстоятельства, связанные с использованием буфера и появлением двух строк на каждую базовую строку, уже не сдерживают производительность. Наконец, при нулевом числе строк оператор Compute Scalar возвращает значение NULL, а оператор Gather Streams собирает строки из нескольких потоков в один поток строк.
При выполнении данного запроса я получил следующие статистические данные: продолжительность — 8 секунд, процессор — 19 секунд, логические операции считывания — 6К, записи — 0. Обратите внимание на сокращение времени выполнения с 28 до 8 секунд.
По-видимому, самая затратная часть этого плана — это явно заданная сортировка. Необходимость сортировки можно было бы исключить, предварительно отсортировав данные в индексе двоичного дерева, но ведь для нас важно располагать индексом columnstore, пусть даже фиктивным, чтобы иметь возможность обрабатывать данные в пакетном режиме. Для демонстрации стратегии «пакетная обработка данных в представлении rowstore» я буду пользоваться запросом, приведенным в листинге 4 (назовем его Query 3), к таблице TransactionsDCS (rowstore с фиктивным columnstore).
План выполнения этого запроса представлен на рисунке 3.
.jpg) |
| Рисунок 3. План выполнения запроса Query 3 (ROWS, пакетная обработка данных rowstore) |
Этот план последовательный. Он предполагает извлечение предварительно отсортированных данных из кластеризованного индекса двоичного дерева в ходе обработки в построчном режиме. Далее строки преобразуются в пакеты, и оконная функция вычисляется с помощью оператора пакетного режима Window Aggregate. Обратите внимание на то, что, если у вас в таблице имеются как индекс rowstore, так и подлинный индекс columnstore, оптимизатор отдает предпочтение не стратегии rowstore, а обработке данных в пакетном режиме.
Вот статистические показатели, полученные мною при выполнении данного запроса: продолжительность — 7 секунд, процессор — 7 секунд, логические операции считывания — 31К, записи — 0. Есть одно свойство, отсутствие которого весьма заметно снижает ценность данного плана; я имею в виду параллелизм. На данный момент система SQL Server 2016 все еще не позволяет эффективно использовать сочетание параллельно упорядоченного оператора Index Scan и операторов Window Aggregate. Предстоит еще кое-что усовершенствовать в ядре, и, будем надеяться, у нас появится такая возможность. И тогда показатели времени выполнения, указанные в статье, снизятся еще больше.
RANGE с разделителями UNBOUNDED и CURRENT ROW
Вообще-то я не люблю пробуждать в людях слишком высокие ожидания до того, как им будет продемонстрирована какая-нибудь новинка, но здесь я сделаю исключение. То, что я опишу сейчас, — это, на мой взгляд, самое значительное преимущество из тех, что дает применение оператора Window Aggregate. Некоторым образом оно связано с недостатком метода построчной обработки оконных агрегатов с использованием блока фрейма RANGE. Рассмотрим запрос, приведенный в листинге 5 (назовем его Query 4).
Логическое различие между ROWS и RANGE состоит в том, как они трактуют связи в упорядочивающих значениях в тех случаях, когда упорядочение не является уникальным. Предположим, например, что таблица включает столбец, содержащий дату совершения транзакции, и что мы использовали этот столбец в предложении, определяющем порядок в окне. Если в случае использования оператора ROWS вычисляется промежуточная сумма, не включающая связи, то при использовании оператора RANGE связи при подсчете учитываются. Так, при использовании оператора RANGE во всех строках, приходящихся на одну и ту же дату, будет указываться одна и та же промежуточная сумма. Как вы можете догадаться, в случаях, когда упорядочение в рамках разделения является уникальным, а именно с таким случаем мы имеем дело, ибо упорядочение строится на идентификаторе транзакции, операторы ROWS и RANGE имеют одинаковое значение.
План выполнения этого запроса представлен на рисунке 4.
.jpg) |
| Рисунок 4. План выполнения запроса Query 4 (RANGE, построчная обработка данных в представлении rowstore) |
В этом плане не выполняется построчный расчет чисел. Здесь используются два оператора Segment — один для разделения окна, а другой — для определения места, где изменяется значение упорядочения. Остальное, пожалуй, выполняется так же, как при вычислениях с использованием ROWS, как было показано выше для запроса Query 1 на рисунке 1. Итак, можно предположить, что показатели выполнения запросов Query 4 и Query 1 аналогичны. Однако если бы все было так просто!
В рассматриваемом нами плане есть скрытая проблема, имеющая отношение к производительности. Всякий раз, когда вы выбираете вариант RANGE, SQL Server использует дисковый буфер для оператора Window Spool (в отличие от размещаемого в оперативной памяти буфера, который система использует в варианте ROWS в ускоренном режиме). Дисковый буфер, в сущности, представляет собой рабочую таблицу базы данных tempdb со всеми обычными непроизводительными затратами, связанными с подсистемой ввода-вывода и блокировками. Следовательно, расчет по варианту RANGE может занять на порядок больше времени, чем аналогичный расчет по варианту ROWS, даже в том случае, когда упорядочение уникально. Опять же, об этом обстоятельстве вы не можете узнать, просматривая план. Вот статистические данные, полученные мною при выполнении данного запроса: продолжительность — 221 секунда, процессор — 204 секунды, логические операции считывания — 104К, записи — 31К. Отмечу, что продолжительность выполнения данного запроса на порядок больше, чем у запроса Query 1. Кроме того, должен обратить ваше внимание на чрезмерное количество логических операций считывания. Если перед выполнением запросов вы активируете настройку STATISTICS IO, то на выходе получите запись для буфера с именем worktable. В этой записи количество логических операций считывания будет равно нулю при использовании варианта ROWS и составлять весьма большое число в случае применения варианта RANGE. Это один из способов убедиться, что вы имеете дело с дисковым буфером. Другой способ — запустить сеанс Extended Events с событием window_spool_ondisk_warning.
Ситуация усугубляется тем, что, если вы опустите предложение с блоком фрейма окна, стандартный SQL определит, что спецификация RANGE UNBOUNDED PRECEDING должна применяться по умолчанию. Таким образом, запрос в листинге 6 семантически эквивалентен запросу Query 4 с теми же низкими показателями быстродействия.
Не могу и подсчитать, как часто я видел людей, использующих RANGE, даже не подозревая об этом (себя я тоже отношу к их числу). Особенно трудно обращать на это внимание в случаях, когда вы испытываете собственный код с использованием небольшого набора тестовых данных и упорядочение уникально в рамках данного раздела, как в нашем примере. Только вдумайтесь, как много систем используют подобный код с запросами, выполняемыми на порядок медленнее, чем должно. И вот оказывается, что оператор Window Aggregate решает и эту проблему. По существу, он оптимизирует ROWS и RANGE аналогичным образом.
Показанный в листинге 7 запрос (назовем его Query 5) демонстрирует использование пакетного режима при обработке данных в представлении columnstore.
Этот план напоминает приведенный на рисунке 2 план для запроса Query 2. Вот какие статистические данные я получил по итогам выполнения данного запроса: продолжительность — 9 секунд, процессор — 21 секунда, логические операции считывания — 6К, записи — 0.
Запрос, приведенный в листинге 8 (я буду называть его Query 6), демонстрирует использование пакетного режима обработки данных в представлении rowstore.
Этот план аналогичен плану выполнения запроса Query 3 (см. рисунок 3). Вот какие статистические данные я получил по итогам его выполнения: продолжительность — 7 секунд, процессор — 7 секунд, логические операции считывания — 31К, записи — 0.
Характеристики выполнения запросов с первого по шестой представлены на рисунке 5.
.jpg) |
| Рисунок 5. Характеристики выполнения агрегирующих функций с разделителями UNBOUNDED и CURRENT ROW |
Разделители, отличные от UNBOUNDED и CURRENT ROW
В случае использования агрегирующих оконных функций с фреймами, среди разделителей которых нет ни UNBOUNDED, ни CURRENT ROW, даже если вы опрашиваете данные в представлении columnstore, при расчете оконных функций всегда найдется дело для операторов построчного режима. И все же часть работы можно поручить операторам пакетного режима.
Допустим, вы вычисляете агрегирующую оконную функцию с фреймом, который начинается со спецификации UNBOUNDED PRECEDING (ускоренная оптимизация), но не заканчивается CURRENT ROW; например: SUM (val) с фреймом ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING. Если вы адресуете запрос к таблице Transactions (данные в представлении rowstore), то получите оптимизацию, подобную той, которая выполнялась в запросе Query 1 на рисунке 1, поскольку она все еще считается ускоренной.
Если вы направите запрос к таблице TransactionsCS (данные в представлении columnstore), то та часть плана, которая вычисляет количество строк, будет оптимизироваться с помощью оператора пакетного режима Window Aggregate, однако собственно агрегат будет оптимизирован с помощью традиционных операторов построчного режима для случаев с применением ускоренного варианта.
Если же вы адресуете запрос к таблице TransactionsDCS (rowstore плюс фиктивный индекс columnstore), оптимизатор просто выберет вариант с обработкой всех данных в построчном режиме, и вы получите план, подобный плану выполнения запроса Query 1 на рисунке 1.
Если фрейм не будет начинаться со спецификации UNBOUNDED PRECEDING, формально он не будет относиться к категории фреймов, подлежащих ускоренной оптимизации. Однако в случае, если агрегат рассматривается как накопительный (таковыми, скажем, считаются SUM, COUNT, AVG, но не MIN и MAX), оптимизатор может выполнить один неускоренный расчет на базе двух ускоренных расчетов; например, расчет SUM (val) с фреймом ROWS BETWEEN 99 PRECEDING AND CURRENT ROW может быть выполнен как SUM (val) с фреймом ROWS UNBOUNDED PRECEDING минус SUM (val) с фреймом ROWS BETWEEN UNBOUNDED PRECEDING AND 100 PRECEDING. Запрос в листинге 9 (я буду называть его Query 7) к таблице Transactions (rowstore) демонстрирует данную стратегию с использованием оптимизации в построчном режиме данных в представлении row store.
План выполнения данного запроса показан на рисунке 6.
.jpg) |
| Рисунок 6. План выполнения запроса Query 7 (ROWS 99 PRECEDING AND CURRENT ROW, обработка в построчном режиме данных в представлении rowstore) |
В верхней части плана вычисляется агрегат ComulativeBottom (ROWS UNBOUNDED PRECEDING), а в нижней части — агрегат CumulativeTop (ROWS BETWEEN UNBOUNDED PRECEDING AND 100 PRECEDING). Следующий за этим оператор Compute Scalar вычитает значение CumulativeTop из значения CumulativeBottom, в результате чего получается конечный агрегат. Вот статистические данные, полученные мною в ходе выполнения данного запроса: продолжительность — 59 секунд, процессор — 59 секунд, логические операции считывания — 31К, записи — 0.
Говоря об аналогичном запросе к таблице TransactionsCS (columnstore), отмечу, что для расчета значения агрегата CumulativeBottom план использует оператор пакетного режима Window Aggregate. Далее он предусматривает вычисление номеров строк с помощью оператора Window Aggregate и выполнение расчета агрегата CumulativeTop в традиционном ускоренном варианте построчного режима. Вот статистические данные, которые я получил по результатам выполнения данного запроса: продолжительность — 34 секунды, процессор — 34 секунды, логические операции считывания — 5К, записи — 0. Обратите внимание: по сравнению с работой в традиционном построчном режиме обработки скорость выполнения запроса в данном случае сократилась вдвое.
Аналогичным образом, если вы адресуете запрос к таблице TransactionsDCS (rowstore + фиктивный индекс columnstore), в соответствии с этим планом вычисление агрегата ComulativeBottom будет выполняться с помощью оператора Window Aggregate, далее число строк будет рассчитываться с помощью оператора Window Aggregate, а расчет агрегата ComulativeTop будет выполняться с помощью традиционных операторов построчного режима в ускоренном варианте. Отличие от предыдущего случая состоит в том, что данные извлекаются из сбалансированного дерева и потому необходимости в явно выраженной сортировке нет. Вот статистические данные по производительности, полученные мною в данном случае: продолжительность — 33 секунды, процессор — 33 секунды, логические операции считывания — 5К, записи — 0.
Допустим, вы вычисляете оконную функцию SUM (val) с фреймом ROWS BETWEEN 99 PRECEDING AND 1 PRECEDING. При обращении к таблице Transactions (rowstore) план предусматривает расчет номеров строк и двух ускоренных агрегатов с помощью операторов построчного режима.
При обращении к таблице TransactionsCS (columnstore) план предусматривает расчет номеров строк с помощью оператора Window Aggregate, а вычисление двух агрегатов — с помощью операторов ускоренного построчного режима.
При обращении к таблице TransactionsDCS (данные в представлении rowstore плюс фиктивный индекс columnstore) оптимизатор отдает предпочтение плану, где используется только обработка в построчном режиме, как при работе с запросом к таблице Transactions.
При использовании ненакопительного агрегата, такого как MIN и MAX, с фреймом, который не начинается со спецификации UNBOUNDED PRECEDING (к примеру, с фреймом ROWS BETWEEN 99 PRECEDING AND 1 PRECEDING), оптимизатор не может прибегать к тому способу, в соответствии с которым он вычисляет два агрегата в ускоренном режиме и выполняет окончательный расчет на основании двух первых.
Во время выполнения запроса к таблице Transactions (rowstore) и номер строки, и агрегат вычисляются с помощью операторов построчного режима, а ускоренная оптимизация не используется, поэтому все применимые строки фреймов должны быть записаны в буфер окна. Если запрос адресован к таблице TransactionsCS (columnstore), план предусматривает вычисление номеров строк с помощью оператора Window Aggregate, а вычисление агрегата — с помощью операторов построчного режима.
При выполнении запроса к таблице TransactionsDCS (данные в представлении rowstore плюс фиктивный индекс columnstore) план предполагает расчет и номеров строк, и агрегата с помощью операторов построчного режима — так же, как при обработке запроса к таблице Transactions.
Оконные функции смещения
Язык T-SQL обеспечивает выполнение двух пар оконных функций смещения. Функции LAG и LEAD возвращают элемент из предыдущей или последующей строки соответственно. Функции FIRST_VALUE и LAST_VALUE возвращают элемент из первой или последней строки оконного фрейма соответственно.
Функции LAG и LEAD
Приведенный в листинге 10 запрос к таблице Transactions (я буду называть его Query 8) демонстрирует традиционную — в построчном режиме применительно к данным в представлении rowstore — оптимизацию для функции LAG (функция LEAD оптимизируется аналогичным образом).
Программа преобразует функции LAG и LEAD в функцию LAST_VALUE с фреймом, содержащим только запрашиваемую строку. Так, функция LAG со смещением, по умолчанию равным 1, преобразуется в функцию LAST_VALUE с фреймом ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING. Поэтому запрос Query 8 как по значению, так и по методу оптимизации эквивалентен запросу в листинге 11 (назовем его Query 9).
План выполнения запросов Query 8 и Query 9 показан на рисунке 7.
.jpg) |
| Рисунок 7. План выполнения запроса Query 8 (LAG/LEAD, построчный режим обработки данных в представлении rowstore) |
Как видите, этот план подобен тому, что был предложен для запроса Query 1 на рисунке 1. Вот какие статистические показатели я получил при его выполнении: продолжительность — 33 секунды, процессор — 32 секунды, логические операции считывания — 31К, записи — 0.
Если в опрашиваемой таблице имеется индекс columnstore, SQL Server может оптимизировать функции LAG и LEAD с помощью оператора пакетного режима Window Aggregate; нужно только, чтобы показатель смещения был равен 1 (значение по умолчанию). К примеру, запрос в листинге 12 (я буду называть его Query 10) к таблице TransactionsCS демонстрирует обработку данных в пакетном режиме после оптимизации columnstore.
План выполнения этого запроса показан на рисунке 8.
.jpg) |
| Рисунок 8. План выполнения запроса Query 10 (LAG/LEAD, пакетный режим обработки данных в представлении columnstore) |
Как видите, данный план аналогичен плану, предложенному для запроса Query 2 на рисунке 2. Это план параллельный. Поскольку данные извлекаются из индекса columnstore, их необходимо сортировать. Почти все операторы используют пакетный режим обработки. Вот статистические данные, полученные мною при выполнении этого запроса: продолжительность — 10 секунд, процессор — 19 секунд, логические операции считывания — 6К, записи — 0. Обратите внимание на сокращение времени выполнения с 33 до 10 секунд.
Показанный в листинге 13 запрос к таблице TransactionsDCS (я буду называть его Query 11) демонстрирует обработку данных в пакетном режиме после оптимизации rowstore.
План выполнения данного запроса представлен на рисунке 9.
.jpg) |
| Рисунок 9. План выполнения запроса Query 11 (LAG/LEAD, обработка в пакетном режиме данных в представлении rowstore) |
Как вы можете убедиться, данный план подобен тому, что был предложен для обработки запроса Query 3 на рисунке 3. Это план последовательный. В ходе проверки индекса двоичного дерева используется обработка в построчном режиме, но, поскольку извлекаемые данные упорядочены, необходимость в явной сортировке отсутствует. Затем оператор пакетного режима Window Aggregate выполняет расчет оконной функции. Вот какие статистические данные я получил в ходе выполнения данного запроса: продолжительность — 7 секунд, процессор — 7 секунд, логические операции считывания — 31К, записи — 0. Время выполнения сократилось до 7 секунд!
Как уже отмечалось, на сегодня SQL Server может применять оператор Window Aggregate для расчета функций LAG и LEAD только в том случае, если показатель смещения равен 1. При других значениях этого показателя данные функции оптимизируются с помощью операторов построчного режима. Так, запрос в листинге 14 (назовем его Query 12) демонстрирует использование функции LAG с показателем смещения, равным 2.
План выполнения этого запроса аналогичен плану, предложенному для запроса Query 8 на рисунке 7. Вот статистические данные, которые я получил для рассматриваемого запроса: продолжительность — 31 секунда, процессор — 31 секунда, логические операции считывания — 31К, записи — 0.
Отмечу, что некоторые части плана при соблюдении необходимых условий могут быть оптимизированы с помощью операторов пакетного режима. Так, если вы направляете запрос к таблице TransactionsCS со смещением 2, то те части плана, которые предусматривают считывание данных из индекса columnstore, сортировку и вычисление номеров строк, могут по-прежнему выполняться с помощью операторов пакетного режима. Но вычисление собственно оконной функции будет выполняться с использованием традиционных операторов построчного режима.
На рисунке 10 представлены показатели, характеризующие выполнение функций LAG и LEAD со значением смещения, равным 1.
.jpg) |
| Рисунок 10. Показатели быстродействия функций LAG и LEAD |
Функции FIRST_VALUE и LAST_VALUE
Как уже отмечалось, внутренние механизмы системы преобразуют функции LAG и LEAD в функцию LAST_VALUE. Когда мы используем функцию LAST_VALUE в режиме, эквивалентном функциям LAG и LEAD с показателем смещения, равным 1 (с фреймом ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING или 1 FOLLOWING AND 1 FOLLOWING), то получаем оптимизацию, аналогичную оптимизации для функций LAG и LEAD, как описано в предыдущем разделе. В иных случаях (когда функция LAST_VALUE используется со смещением, не равным 1, или когда функция FIRST_VALUE применяется с любым значением смещения), оптимизация расчета самой оконной функции выполняется с помощью традиционных операторов построчного режима. При выполнении той части плана, где речь идет о считывании данных из индекса columnstore, о сортировке и о вычислении номеров строк, можно по-прежнему пользоваться операторами пакетного режима.
Оператор пакетного режима Window Aggregate, реализованный в версии SQL Server 2016, при выполнении большинства оконных функций обеспечивает значительное повышение быстродействия. Этот оператор можно использовать при работе с агрегирующими оконными функциями без фрейма, ранжирующими и статистическими оконными функциями с классическим фреймом (где применяются разделители UNBOUNDED и CURRENT ROW), а также при работе с оконными функциями смещения LAG и LEAD с классическим показателем смещения, равным 1.
Этот оператор можно использовать даже в тех случаях, когда данные извлекаются из источника, где они хранятся в представлении rowstore, но при условии, что в опрашиваемой таблице имеется по меньшей мере один индекс columnstore. Допускается «сосуществование» данных в представлениях как columnstore, так и rowstore; в этом случае право выбора источника для любого запроса предоставляется оптимизатору. Если вы имеете дело с ситуацией, в которой вам требуются только данные в представлении rowstore, можете создать фиктивный отфильтрованный индекс columnstore с единственной целью обеспечить возможность обработки данных в пакетном режиме. Этот оператор демонстрирует гораздо более широкие возможности масштабирования со степенью параллелизма (degree of parallelism, DOP) n>1 против степени DOP=1, характеризующей обработку в построчном режиме. Строчка It Just Runs Faster, описывающая повышенное быстродействие версии SQL Server 2016, полностью справедлива. Будем надеяться, что в дальнейшем специалисты Microsoft внесут в свой продукт множество подобных усовершенствований и возможности оконных функций тоже будут расширены.
-- база данных testwindow SET NOCOUNT ON; IF DB_ID(N'testwindow') IS NULL CREATE DATABASE testwindow; GO USE testwindow; GO -- вспомогательная функция GetNums DROP FUNCTION IF EXISTS dbo.GetNums; GO CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE AS RETURN WITH L0 AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)), L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B), L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B), L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B), L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B), L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B), Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum FROM L5) SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n FROM Nums ORDER BY rownum; GO -- Таблица транзакций с 10 миллионами строк (200 счетов х 50000 транзакций на каждом счету) -- Традиционный построчный индекс B-tree DROP TABLE IF EXISTS dbo.Transactions; CREATE TABLE dbo.Transactions ( actid INT NOT NULL, tranid INT NOT NULL, val MONEY NOT NULL, CONSTRAINT PK_Transactions PRIMARY KEY(actid, tranid) ); GO DECLARE @num_partitions AS INT = 200, @rows_per_partition AS INT = 50000; INSERT INTO dbo.Transactions WITH (TABLOCK) (actid, tranid, val) SELECT NP.n, RPP.n, (ABS(CHECKSUM(NEWID())%2)*2-1) * (1 + ABS(CHECKSUM(NEWID())%5)) FROM dbo.GetNums(1, @num_partitions) AS NP CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP; GO -- TransactionsCS -- Кластеризованный индекс columnstore DROP TABLE IF EXISTS dbo.TransactionsCS; SELECT * INTO dbo.TransactionsCS FROM dbo.Transactions; CREATE CLUSTERED COLUMNSTORE INDEX idx_cs ON dbo.TransactionsCS; -- TransactionsDCS -- Традиционный построчный индекс B-tree -- + фиктивный пустой отфильтрованный некластеризованный индекс columnstore -- чтобы иметь возможность использовать операторы пакетного режима DROP TABLE IF EXISTS dbo.TransactionsDCS; SELECT * INTO dbo.TransactionsDCS FROM dbo.Transactions; ALTER TABLE dbo.TransactionsDCS ADD CONSTRAINT PK_TransactionsDCS PRIMARY KEY(actid, tranid); CREATE NONCLUSTERED COLUMNSTORE INDEX idx_cs_dummy ON dbo.TransactionsDCS(actid) WHERE actid = -1 AND actid = -2;
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING) AS balance FROM dbo.Transactions;
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING) AS balance FROM dbo.TransactionsCS;
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS UNBOUNDED PRECEDING) AS balance FROM dbo.TransactionsDCS;
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING) AS balance FROM dbo.Transactions;
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid) AS balance FROM dbo.Transactions;
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING) AS balance FROM dbo.TransactionsCS;
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid RANGE UNBOUNDED PRECEDING) AS balance FROM dbo.TransactionsDCS;
SELECT actid, tranid, val, SUM(val) OVER(PARTITION BY actid ORDER BY tranid ROWS BETWEEN 99 PRECEDING AND CURRENT ROW) AS last100 FROM dbo.Transactions;
SELECT actid, tranid, val, LAG(val) OVER(PARTITION BY actid ORDER BY tranid) AS prevval FROM dbo.Transactions;
SELECT actid, tranid, val, LAST_VALUE(val) OVER(PARTITION BY actid ORDER BY tranid ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prevval FROM dbo.Transactions;
SELECT actid, tranid, val, LAG(val) OVER(PARTITION BY actid ORDER BY tranid) AS prevval FROM dbo.TransactionsCS;
SELECT actid, tranid, val, LAG(val) OVER(PARTITION BY actid ORDER BY tranid) AS prevval FROM dbo.TransactionsDCS;
SELECT actid, tranid, val, LAG(val, 2) OVER(PARTITION BY actid ORDER BY tranid) AS prev2val FROM dbo.TransactionsDCS;