Оптимизация аналитических функций в T-SQL

Работа с базами данных в .NET Framework --- Оконные функции T-SQL --- Оптимизация аналитических функций

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

Оптимизация функций распределения рангов

Функции распределения рангов это PERCENT_RANK и CUME_DIST. Как вы помните, функция PERCENT_RANK вычисляется как (rk - 1) / (nr - 1), где rk — ранг строки, а nr — число строк в секции. Вычисление строк в секции предусматривает использование итератора Table Spool, о чем говорилось ранее. Для вычисления ранга применяется итератор Sequence Project. План, вычисляющий PERCENT_RANK, просто включает оба итератора. В качестве примера посмотрите на такой запрос:

SELECT testid, studentid, score,
  PERCENT_RANK() OVER(PARTITION BY testid ORDER BY score) AS percentrank
FROM Stats.Scores;

План этого запроса:

В первой части данные считываются и сегментируются по testid. Затем одна по одной строки секции записываются во временную таблицу, которая затем считывается дважды — сначала для вычисления числа строк (nr), а затем для получения строк подробных данных. После этого строки подробных данных их агрегаты соединяются. Затем итераторы Segment и Sequence Project используются для вычисления ранга (rk). Наконец, итератор Compute Scalar вычисляет результат функции PERCENT_RANK как (rk - 1)/(nr - 1).

Что касается CUME_DIST, то эта функция вычисляет np/nr; где nr — то же, что и раньше (число строк в секции), а np — число строк, которые предшествуют или находятся на одном уровне с текущей строкой. В качестве примера посмотрите на такой запрос:

SELECT testid, studentid, score,
  CUME_DIST()    OVER(PARTITION BY testid ORDER BY score) AS cumedist
FROM Stats.Scores;

План этого запроса:

Первая часть, которая вычисляет nr, совпадает с планом PERCENT_RANK. Вторая часть чуть сложнее. Для вычисления np сервер SQL Server должен забежать вперед текущей строки. Также в плане есть два итератора Segment — первый сегментирует строки по элементу сегментирования (testid), а второй сегментирует по элементам сегментирования и упорядочения (testid и score). Однако вместо итератора Sequence Project используются новые итераторы Window Spool и Stream Aggregate iterators в «быстром» режиме для вычисления числа строк, которые предшествуют или находятся на одном уровне с текущей строкой. Наконец итератор Compute Scalar вычисляет значение CUME_DIST как np/nr.

Оптимизация функций обратного распределения

Оптимизация функций обратного распределения PERCENTILE_CONT и PERCENTILE_DISC сложнее, чем функций распределения рангов. Я начну с PERCENTILE_DISC. Посмотрите на следующий запрос:

SELECT testid, score,
  PERCENTILE_DISC(0.5) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentiledisc
FROM Stats.Scores;

План этого запроса:

План выполняет следующие операции:

• Первый набор из восьми итераторов в нижней правой части отвечает за вычисление числа строк для каждой строки в соответствующей секции testid. В плане это число называется PartitionSizeN.

• Последующие итераторы Segment и Sequence Project вычисляют номер строки в секции testid при упорядочении по оценкам. В плане этот номер называется RowNumberN.

• Первый итератор Compute Scalar вычисляет номер строки, содержащей процентиль данной секции. При этом применяется такое выражение (упрощено): CeilingTargetRowN = ceiling(@pct * PartitionSize1013), где @pct является входным значением процента для функции (в нашем случае 0,5).

• Второй итератор Compute Scalar вычисляет выражение, которое называется PartialSumN. Это выражение возвращает нужный процентиль, если номер текущего столбца (RowNumberN) равен значению MIN(1, CeilingTargetRowN), в противном случае возвращается NULL. Проще говоря, PartialSumN принимает полезное значение, только если является нужным процентилем, а в противном случае возвращает NULL.

• В последней части надо извлечь из каждой секции не равный NULL процентиль (PartialSumN) и связать его со строками подробных данных. Для этого в плане снова используется итератор Table Spool. План сегментирует данные по testid и сохраняется строки текущей секции в спуле — по одной секции за раз. Затем план два раза считывает спул — сначала для получение не равных NULL процентилей с помощью агрегата MAX(PartialSumN) (результат называется PercentileResultN), а затем для получения подробных данных. После этого план соединяет подробные данные и агрегаты.

• Последняя часть - проверка размера секции. Если он нулевой, возвращается NULL, а в противном случае — PercentileResultN.

Что касается функции PERCENTILE_CONT, для обсуждения плана я воспользуюсь следующим запросом:

SELECT testid, score,
  PERCENTILE_CONT(0.5) WITHIN GROUP(ORDER BY score) OVER(PARTITION BY testid) AS percentilecont
FROM Stats.Scores;

План этого запроса показан на рисунке ниже:

Как видите, общая структура плана похожа на функцию PERCENTILE_DISC. Однако есть ряд различий. Одно отличие заключается в итераторах Compute Scalar, которые находятся справа, сразу за вычислением числа строк, а второе отличие — второй итератор Stream Aggregate. Я начну с итераторов Compute Scalar:

• Первый итератор Compute Scalar вычисляется номер целевой строки, включая дробную часть: TargetRowN = 1 + @pct * (PartitionSizeN - 1).

• Второй итератор Compute Scalar вычисляет минимум и максимум для TargetRowN, присваивая им соответственно имена FloorTargetRowN и CeilingTargetRowN.

• Третий итератор Compute Scalar вычисляет выражение, которое называется PartialSumN. Если интерполяция не требуется, PartialSumN возвращает значение процентиля, если текущая строка является целевой, или 0 в противном случае. Если интерполяция нужна, PartialSumN возвращает часть интерполированного значения, если текущая строка является минимумом или максимумом для целевой строки. В противном случае возвращается ноль.

Другое отличие плана от PERCENTILE_DISC заключается в том, что в итераторе Stream Aggregate используется агрегат SUM, а не MAX. Причина в том, что на этот раз может потребоваться более одного элемента, а также в том, что может возникнуть необходимость суммировать части, используемые для вычисления интерполированного значения.

Итак, в этих нескольких статьях я рассказал об оптимизации оконных функций SQL Server. Мне пришлось представить много подробной информации, но я надеюсь, что вы в ней не утонули. Особый интерес представляют новый оптимизированный итератор Window Spool iterator и улучшенный итератор Stream Aggregate, а также оптимизированные таблицы в памяти, которые в этих итераторах используются. Остались некоторые странности в оптимизации, в частности те, что относятся к по-видимому ненужным сортировкам, но я надеюсь, что ситуация улучшится в будущем. Достичь совершенства невозможно, но нужно к нему стремиться. В любом случае, в сравнении с другими методами получения аналогичных результатов, SQL Server обеспечивает очень эффективную работу оконных функций.

Далее мы поговорим о практическом использовании оконных функций и в некоторых случаях сравним полученные решения с более традиционными методами, чтобы увидеть, насколько более эффективны новые функции и функциональность.