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

85 Исходник базы данных

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

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

Функции распределения рангов это 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;

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

План для функции PERCENT_RANK

В первой части данные считываются и сегментируются по 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;

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

План выполнения функции CUME_DIST

Первая часть, которая вычисляет 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;

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

План для функции PERCENTILE_DISC

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

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

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

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

План для функции PERCENTILE_CONT

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

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

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

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

CodeChick.io - Задачи по программированию с автоматической проверкой
Пройди тесты
Лучший чат для C# программистов