SQL Server 2016: агрегатный оконный оператор пакетного типа | Оконные функции смещения, функции LAG и LEAD
Содержание:
1. Строки с разделителями UNBOUNDED и CURRENT ROW;
2. RANGE с разделителями UNBOUNDED и CURRENT ROW;
3. Разделители, отличные от UNBOUNDED и CURRENT ROW;
4. Оконные функции смещения, функции LAG и LEAD (Вы читаете данный раздел);
5. Функции FIRST.VALUE и LAST.VALUE.
Язык Т-SQL обеспечивает выполнение двух пар оконных функций смещения. Функции LAG и LEAD возвращают элемент из предыдущей или последующей строки соответственно. Функции FIRSTVALUE и LAST VALUE возвращают элемент из первой или последней строки окопного фрейма соответственно.
Запрос Query 8
Приведенный в коде выше запрос к таблице Transactions (я буду называть его Query 8) демонстрирует традиционную — в построчном режиме применительно к данным в представлении rowstore — оптимизацию для функции LAG (функция LEAD оптимизируется аналогичным образом).
Запрос Query 9
Программа преобразует функции LAG и LEAD в функцию LAST_ VALUE с фреймом, содержащим только запрашиваемую строку. Так, функция LAG со смещением, по умолчанию равным 1, преобразуется в функцию LAST_VALUE с фреймом ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING. Поэтому запрос Query 8 как по значению, так и по методу оптимизации эквивалентен запросу в коде выше (назовем его Query 9).
План выполнения запроса Query 8 (LAG/LEAD, построчный режим обработки данных в представлении rowstore)
План выполнения запросов Query 8 и Query 9 показан на рисунке выше.
План выполнения запроса Query 1 (ROWS, построчный режим обработки данных в представлении rowstore)
Как видите, этот план подобен тому, что был предложен для запроса Query 1 на рисунке выше. Вот какие статистические показатели я получил при его выполнении: продолжительность — 33 секунды, процессор — 32 секунды, логические операции считывания — 31 К. записи — 0.
Запрос Query 10
Если в опрашиваемой таблице имеется индекс columnstore, SQL Server может оптимизировать функции LAG и LEAD с помощью оператора пакетного режима Window Aggregate; нужно только, чтобы показатель смешения был равен 1 (значение по умолчанию). К примеру, запрос в коде выше (я буду называть его Query 10) к таблице TransactionsCS демонстрирует обработку данных в пакетном режиме после оптимизации columnstore.
План выполнения запроса Query 10 (LAG/LEAD, пакетный режим обработки данных в представлении columnstore)
План выполнения этого запроса показан на рисунке выше.
План выполнения запроса Query 2 (ROWS, пакетный режим обработки данных в представлении columnstore)
Как видите, данный план аналогичен плану, предложенному для запроса Query 2 на рисунке выше. Это план параллельный. Поскольку данные извлекаются из индекса columnstore, их необходимо сортировать. Почти все операторы используют пакетный режим обработки. Вот статистические данные, полученные мною при выполнении этого запроса: продолжительность — 10 секунд, процессор— 19 секунд, логические операции считывания — 6К, записи — 0. Обратите внимание на сокращение времени выполнения с 33 до 10 секунд.
Запрос Query 11
Показанный в коде выше запрос к таблице Transact ions DCS (я буду называть его Query 11) демонстрирует обработку данных в пакетном режиме после оптимизации rowstore.
План выполнения данного запроса представлен на рисунке ниже.
План выполнения запроса Query 11 (LAG/LEAD, обработка в пакетном режиме данных в представлении rowstore)
План выполнения запроса Query 3 (ROWS, пакетная обработка данных rowstore)
Как вы можете убедиться, данный план подобен тому, что был предложен для обработки запроса Query 3 на рисунке выше. Это план последовательный. В ходе проверки индекса двоичного дерева используется обработка в построчном режиме, но, поскольку извлекаемые данные упорядочены, необходимость в явной сортировке отсутствует. Затем оператор пакетного режима Window Aggregate выполняет расчет оконной функции. Вот какие статистические данные я получил в ходе выполнения данного запроса: продолжительность — 7 секунд, процессор — 7 секунд, логические операции считывания — 31 К, записи — 0. Время выполнения сократилось до 7 секунд!
Запрос Query 12
Как уже отмечалось, на сегодня SQL Server может применять оператор Window Aggregate для расчета функций LAG и LEAD только в том случае, если показатель смешения равен 1. При других значениях этого показателя данные функции оптимизируются с помощью операторов построчного режима. Так, запрос в коде выше (назовем его Query 12) демонстрирует использование функции LAG с показателем смещения, равным 2.
План выполнения запроса Query 8 (LAG/LEAD, построчный режим обработки данных в представлении rowstore)
План выполнения этого запроса аналогичен плану, предложенному для запроса Query 8 на рисунке выше. Вот статистические данные, которые я получил для рассматриваемого запроса: продолжительность — 31 секунда, процессор — 31 секунда, логические операции считывания — 31 К, записи — 0.
Отмечу, что некоторые части плана при соблюдении необходимых условий могут быть оптимизированы с помощью операторов пакетного режима. Так, если вы направляете запрос к таблице TransactionsCS со смещением 2, то те части плана, которые предусматривают считывание данных из индекса columnstore, сортировку и вычисление номеров строк, могут по-прежнему выполняться с помощью операторов пакетного режима. Но вычисление собственно оконной функции будет выполняться с использованием традиционных операторов построчного режима.
Показатели быстродействия функций LAG и LEAD
На рисунке выше представлены показатели, характеризующие выполнение функций LAG и LEAD со значением смещения, равным 1.
1. Строки с разделителями UNBOUNDED и CURRENT ROW;
2. RANGE с разделителями UNBOUNDED и CURRENT ROW;
3. Разделители, отличные от UNBOUNDED и CURRENT ROW;
4.
5. Функции FIRST.VALUE и LAST.VALUE.
Оконные функции смещения
Язык Т-SQL обеспечивает выполнение двух пар оконных функций смещения. Функции LAG и LEAD возвращают элемент из предыдущей или последующей строки соответственно. Функции FIRSTVALUE и LAST VALUE возвращают элемент из первой или последней строки окопного фрейма соответственно.
Функции LAG и LEAD
Запрос Query 8
Приведенный в коде выше запрос к таблице Transactions (я буду называть его Query 8) демонстрирует традиционную — в построчном режиме применительно к данным в представлении rowstore — оптимизацию для функции LAG (функция LEAD оптимизируется аналогичным образом).
Запрос Query 9
Программа преобразует функции LAG и LEAD в функцию LAST_ VALUE с фреймом, содержащим только запрашиваемую строку. Так, функция LAG со смещением, по умолчанию равным 1, преобразуется в функцию LAST_VALUE с фреймом ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING. Поэтому запрос Query 8 как по значению, так и по методу оптимизации эквивалентен запросу в коде выше (назовем его Query 9).
План выполнения запроса Query 8 (LAG/LEAD, построчный режим обработки данных в представлении rowstore)
План выполнения запросов Query 8 и Query 9 показан на рисунке выше.
План выполнения запроса Query 1 (ROWS, построчный режим обработки данных в представлении rowstore)
Как видите, этот план подобен тому, что был предложен для запроса Query 1 на рисунке выше. Вот какие статистические показатели я получил при его выполнении: продолжительность — 33 секунды, процессор — 32 секунды, логические операции считывания — 31 К. записи — 0.
Запрос Query 10
Если в опрашиваемой таблице имеется индекс columnstore, SQL Server может оптимизировать функции LAG и LEAD с помощью оператора пакетного режима Window Aggregate; нужно только, чтобы показатель смешения был равен 1 (значение по умолчанию). К примеру, запрос в коде выше (я буду называть его Query 10) к таблице TransactionsCS демонстрирует обработку данных в пакетном режиме после оптимизации columnstore.
План выполнения запроса Query 10 (LAG/LEAD, пакетный режим обработки данных в представлении columnstore)
План выполнения этого запроса показан на рисунке выше.
План выполнения запроса Query 2 (ROWS, пакетный режим обработки данных в представлении columnstore)
Как видите, данный план аналогичен плану, предложенному для запроса Query 2 на рисунке выше. Это план параллельный. Поскольку данные извлекаются из индекса columnstore, их необходимо сортировать. Почти все операторы используют пакетный режим обработки. Вот статистические данные, полученные мною при выполнении этого запроса: продолжительность — 10 секунд, процессор— 19 секунд, логические операции считывания — 6К, записи — 0. Обратите внимание на сокращение времени выполнения с 33 до 10 секунд.
Запрос Query 11
Показанный в коде выше запрос к таблице Transact ions DCS (я буду называть его Query 11) демонстрирует обработку данных в пакетном режиме после оптимизации rowstore.
План выполнения данного запроса представлен на рисунке ниже.
План выполнения запроса Query 11 (LAG/LEAD, обработка в пакетном режиме данных в представлении rowstore)
План выполнения запроса Query 3 (ROWS, пакетная обработка данных rowstore)
Как вы можете убедиться, данный план подобен тому, что был предложен для обработки запроса Query 3 на рисунке выше. Это план последовательный. В ходе проверки индекса двоичного дерева используется обработка в построчном режиме, но, поскольку извлекаемые данные упорядочены, необходимость в явной сортировке отсутствует. Затем оператор пакетного режима Window Aggregate выполняет расчет оконной функции. Вот какие статистические данные я получил в ходе выполнения данного запроса: продолжительность — 7 секунд, процессор — 7 секунд, логические операции считывания — 31 К, записи — 0. Время выполнения сократилось до 7 секунд!
Запрос Query 12
Как уже отмечалось, на сегодня SQL Server может применять оператор Window Aggregate для расчета функций LAG и LEAD только в том случае, если показатель смешения равен 1. При других значениях этого показателя данные функции оптимизируются с помощью операторов построчного режима. Так, запрос в коде выше (назовем его Query 12) демонстрирует использование функции LAG с показателем смещения, равным 2.
План выполнения запроса Query 8 (LAG/LEAD, построчный режим обработки данных в представлении rowstore)
План выполнения этого запроса аналогичен плану, предложенному для запроса Query 8 на рисунке выше. Вот статистические данные, которые я получил для рассматриваемого запроса: продолжительность — 31 секунда, процессор — 31 секунда, логические операции считывания — 31 К, записи — 0.
Отмечу, что некоторые части плана при соблюдении необходимых условий могут быть оптимизированы с помощью операторов пакетного режима. Так, если вы направляете запрос к таблице TransactionsCS со смещением 2, то те части плана, которые предусматривают считывание данных из индекса columnstore, сортировку и вычисление номеров строк, могут по-прежнему выполняться с помощью операторов пакетного режима. Но вычисление собственно оконной функции будет выполняться с использованием традиционных операторов построчного режима.
Показатели быстродействия функций LAG и LEAD
На рисунке выше представлены показатели, характеризующие выполнение функций LAG и LEAD со значением смещения, равным 1.
