В версии SQL Server 2016 появился оператор пакетного режима Window Aggregate, который позволяет значительно повысить производительность при расчете оконных функций. Он не только гарантирует более высокое общее быстродействие, обеспечиваемое пакетной обработкой по сравнению с обработкой в построчном режиме. В новом операторе предусмотрены отдельные программные пути для каждой оконной функции. Разработчики устранили множество недостатков процедуры оптимизации исходного построчного режима. В частности, теперь мы избавлены от необходимости использования каталога на диске, что достигается за счет сохранения в памяти окна строк и применения многопроходного процесса обработки данного окна в потоковом режиме.

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

Тестовые данные

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

Напомню, что тестовые данные создаются в базе данных testwindow. Существует три таблицы, содержащие идентичные копии данных. Эти данные представляют собой 10 000 000 строк, где зафиксированы проходящие по банковским счетам транзакции (500 счетов × 20000 транзакций по каждому счету). В таблице Transactions используется только построчное представление данных с кластеризованным индексом двоичного дерева, определенным в списке ключей: (actid, tranid). В запросах к данной таблице сейчас используется только построчный метод обработки, поскольку применение пакетного режима обработки допускается лишь в том случае, если у таблицы имеется по крайней мере один индекс columnstore. Поэтому демонстрировать обработку в построчном режиме данных в формате rowstore я буду на примере этой таблицы. В таблице TransactionsCS имеется лишь кластеризованный индекс columnstore, поэтому я буду использовать ее, когда нужно будет продемонстрировать обработку в пакетном режиме данных в формате columnstore. Таблица TransactionsDCS организована так же, как таблица Transactions (кластеризованный индекс двоичного дерева), но она еще имеет фиктивный пустой отфильтрованный индекс columnstore, обеспечивающий возможность пакетной обработки. Я буду использовать эту таблицу, когда потребуется продемонстрировать пакетную обработку данных в формате rowstore.

Ранжирующие оконные функции

Язык программирования T-SQL дает возможность работать с четырьмя ранжирующими оконными функциями: ROW_NUMBER, RANK, DENSE_RANK и NTILE. Первые три я буду рассматривать в рамках единой группы, поскольку они оптимизированы аналогичным образом. Последняя функция будет рассматриваться отдельно, так как она оптимизирована особым способом. Запрос в листинге 2 (назовем его Query 1) показывает, как используются первые три функции при наличии вспомогательного индекса двоичного дерева.

С одной стороны, индекс обеспечивает охват элементов запроса, а с другой — позволяет обойтись без сортировки. Он строится по схеме POC, где P означает partitioning (распределение), O — ordering (упорядочение), а C — covering (охват). Части P и O составляют список ключей индекса, а часть C нужно просто включить в схему. Поскольку наш индекс двоичного дерева — кластеризованный, он естественным образом охватывает весь запрос. Если фильтры запроса строятся на базе тождеств, элементы фильтров помещаются в список ключей в качестве ведущих элементов, и в этом случае мы получаем схему FPOC (где F обозначает filtering, то есть фильтрацию). Ну а поскольку наш запрос не имеет фильтров на основе тождеств, часть F в индексе отсутствует, имеются лишь части POC.

План выполнения рассматриваемого запроса показан на рисунке 1. Он предусматривает обработку представленных в формате rowstore данных в построчном режиме.

 

Это не вся статья. Полная версия доступна только подписчикам журнала. Пожалуйста, авторизуйтесь либо оформите подписку.
Купить номер с этой статьей в PDF