Есть три агрегатные функции, которые чаще всего используются на практике: COUNT, SUM и AVG. И если первая уже обсуждалась ранее, то с остальными есть интересные нюансы с производительностью. Но давайте обо всем по порядку…
При использовании агрегатных функций на плане выполнения, в зависимости от входного потока, может встречаться два оператора: Stream Aggregate и Hash Match.
Для выполнения первого может требоваться предварительно отсортированный входной набор значений и при этом Stream Aggregate не блокирует выполнение последующих за ним операторов.
В свою очередь, Hash Match является блокирующим оператором (за редким исключением) и не требует сортировки входного потока. Для работы Hash Match используется хеш-таблица, которая создается в памяти и в случае неправильной оценки ожидаемого количества строк, оператор может сливать результаты в tempdb.
Итого получается, что Stream Aggregate хорошо работает на небольших отсортированных наборах данных, а Hash Match хорошо справляется с большими не отсортированными наборами и хорошо поддается параллельной обработке.
Теперь, когда мы преодолели теорию начнем смотреть как работают агрегатные функции.
Предположим, что нам нужно подсчитать среднюю цену среди всех продуктов:
SELECT AVG(Price) FROM dbo.Price
По таблице с достаточно простой структурой:
CREATE TABLE dbo.Price (
ProductID INT PRIMARY KEY,
LastUpdate DATE NOT NULL,
Price SMALLMONEY NULL,
Qty INT
)
Поскольку у нас происходит скалярная агрегация, на плане выполнения мы ожидаемо увидим Stream Aggregate:
Внутри этот оператор выполняет две агрегирующие операции COUNT_BIG и SUM (хотя на физическом уровне выполняется это как одна операция) по столбцу Price:
Не забываем, что среднее вычисляется только для NOT NULL, поскольку операция COUNT_BIG идет по столбцу, а не со звездочкой. Соответственно, такой запрос:
SELECT AVG(v)
FROM (
VALUES (3), (9), (NULL)
) t(v)
вернет в качестве результата не 4, а 6.
Теперь посмотрим на Compute Scalar, внутри которого есть интересное выражение для проверки деления на ноль:
Expr1003 =
CASE WHEN [Expr1004]=(0)
THEN NULL
ELSE [Expr1005]/CONVERT_IMPLICIT(money,[Expr1004],0)
END
Попробуем подсчитать общую сумму:
SELECT SUM(Price) FROM dbo.Price
План выполнения останется прежним:
Но если посмотреть на операции, которые выполняет Stream Aggregate…
можно капельку удивиться. Зачем SQL Server подсчитывает количество, если мне нужна только сумма? Ответ кроется в Compute Scalar:
[Expr1003] = Scalar Operator(CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END)
Если не брать во внимание COUNT, то согласно семантике языка T-SQL, когда нет строк во входном потоке, то мы должны возвращать NULL, а не 0. Такое поведение работает как для скалярной, так и для векторной агрегации:
SELECT LastUpdate, SUM(Price)
FROM dbo.Price
GROUP BY LastUpdate
OPTION(MAXDOP 1)
Expr1003 = Scalar Operator(CASE WHEN [Expr1008]=(0) THEN NULL ELSE [Expr1009] END)
Более того, такая проверка делается как для NULL, так и для NOT NULL столбцов. Теперь рассмотрим примеры в которых будут полезны описанные выше особенности SUM и AVG.
Если мы хотим посчитать среднее, то не нужно использовать COUNT + SUM:
SELECT SUM(Price) / COUNT(Price) FROM dbo.Price
Поскольку такой запрос будет менее эффективным, чем явное использование AVG.
Далее… Явно передавать NULL в агрегатную функцию нет необходимости:
SELECT
SUM(CASE WHEN Price < 100 THEN Qty ELSE NULL END),
SUM(CASE WHEN Price > 100 THEN Qty ELSE NULL END)
FROM dbo.Price
Поскольку в такой конструкции:
SELECT
SUM(CASE WHEN Price < 100 THEN Qty END),
SUM(CASE WHEN Price > 100 THEN Qty END)
FROM dbo.Price
Оптимизатор подстановку делает автоматически:
Но что, если я хочу получить 0 в результатах вместо NULL? Очень часто используют ELSE и не задумываются:
SELECT
SUM(CASE WHEN Price < 100 THEN Qty ELSE 0 END),
SUM(CASE WHEN Price > 100 THEN Qty ELSE 0 END)
FROM dbo.Price
Очевидно, что в таком случае мы достигнем желаемого… да и одно предупреждение перестанет мозолить глаза:
Warning: Null value is eliminated by an aggregate or other SET operation.
Хотя лучше всего писать запрос вот так:
SELECT
ISNULL(SUM(CASE WHEN Price < 100 THEN Qty END), 0),
ISNULL(SUM(CASE WHEN Price > 100 THEN Qty END), 0)
FROM dbo.Price
И это хорошо не потому, что оператор CASE станет работать быстрее. Мы то уже знаем, что оптимизатор туда подставляет ELSE NULL автоматом… Так в чем же преимущества последнего варианта?
Как оказалось, операции агрегирования, в которых преобладают NULL значения обрабатываются быстрее.
SET STATISTICS TIME ON
DECLARE @i INT = NULL
;WITH
E1(N) AS (
SELECT * FROM (
VALUES
(@i),(@i),(@i),(@i),(@i),
(@i),(@i),(@i),(@i),(@i)
) t(N)
),
E2(N) AS (SELECT @i FROM E1 a, E1 b),
E4(N) AS (SELECT @i FROM E2 a, E2 b),
E8(N) AS (SELECT @i FROM E4 a, E4 b)
SELECT SUM(N) -- 100.000.000
FROM E8
OPTION (MAXDOP 1)
Выполнение у меня заняло:
SQL Server Execution Times:
CPU time = 5985 ms, elapsed time = 5989 ms.
Теперь меняем:
DECLARE @i INT = 0
И выполняем повторно:
SQL Server Execution Times:
CPU time = 6437 ms, elapsed time = 6451 ms.
Не так существенно, но повод для оптимизации тем не менее это дает в определенных ситуациях.
Конец спектакля и занавес? Нет. Это еще не все…
Как говорил один мой знакомый: «Нет ни черного, ни белого… Мир многоцветен» и поэтому напоследок приведу интересный пример, когда NULL может вредить.
Создадим медленную функцию и тестовую таблицу:
USE tempdb
GO
IF OBJECT_ID('dbo.udf') IS NOT NULL
DROP FUNCTION dbo.udf
GO
CREATE FUNCTION dbo.udf (@a INT)
RETURNS VARCHAR(MAX)
AS BEGIN
DECLARE @i INT = 1000
WHILE @i > 0 SET @i -= 1
RETURN REPLICATE('A', @a)
END
GO
IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
DROP TABLE #temp
GO
;WITH
E1(N) AS (
SELECT * FROM (
VALUES
(1),(1),(1),(1),(1),
(1),(1),(1),(1),(1)
) t(N)
),
E2(N) AS (SELECT 1 FROM E1 a, E1 b),
E4(N) AS (SELECT 1 FROM E2 a, E2 b)
SELECT *
INTO #temp
FROM E4
И выполним запрос:
SET STATISTICS TIME ON
SELECT SUM(LEN(dbo.udf(N)))
FROM #temp
SQL Server Execution Times:
CPU time = 9109 ms, elapsed time = 11603 ms.
Теперь попробуем результат выражения, который передается в SUM, обернуть в ISNULL:
SELECT SUM(ISNULL(LEN(dbo.udf(N)), 0))
FROM #temp
SQL Server Execution Times:
CPU time = 4562 ms, elapsed time = 5719 ms.
Скорость выполнения сократилась в 2 раза. Сразу скажу, что это не магия… А баг в движке SQL Server-а, который Microsoft уже «вроде как» исправила в SQL Server 2012 CTP.
Суть проблемы в следующем: результат выражения внутри функций SUM или AVG может выполняться дважды, если оптимизатор считает, что может вернуться NULL.
Всем спасибо за внимание.
Все тестировалось на Microsoft SQL Server 2012 (SP3) (KB3072779) — 11.0.6020.0 (X64).
Планы выполнения брал из dbForge Studio.
Теперь позвольте пару слов сказать по другой теме...
Уже давно меня посещала мысль собрать в одном месте людей, которым интересны базы данных и все что с ними связано. В конце этого месяца, я планирую организовать небольшую встречу юзер-группы по SQL Server. В рамках нее планируется 2 доклада. Один будет от меня, в рамках которого я постараюсь рассказать про «подводные камни» при работе с XML и XQuery, затронуть вопросы производительности и показать пару интересных трюков. Второй доклад тоже есть, но пока под вопросом… поэтому если есть желающие выступить и поделиться чем-то интересным по SQL Server пишите мне на почту указанную в профиле.
Место проведения: Украина, г. Харьков, ул. Маломясницкая, 9/11 (ст. метро пр. Гагарина) на базе академии «ШАГ»
Время начала: 29 января 2016 в 18:00
Участие полностью бесплатное, но количество место ограничено.
По этой причине нужно пройти предварительную регистрацию, чтобы я мог знать сколько людей планирует посетить встречу.
Автор: AlanDenton
