Есть три агрегатные функции, которые чаще всего используются на практике: 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.

Если хотите поделиться этой статьей с англоязычной аудиторией, то прошу использовать ссылку на перевод:
What is faster inside SUM & AVG: 0 or NULL?

Теперь позвольте пару слов сказать по другой теме...

Уже давно меня посещала мысль собрать в одном месте людей, которым интересны базы данных и все что с ними связано. В конце этого месяца, я планирую организовать небольшую встречу юзер-группы по SQL Server. В рамках нее планируется 2 доклада. Один будет от меня, в рамках которого я постараюсь рассказать про «подводные камни» при работе с XML и XQuery, затронуть вопросы производительности и показать пару интересных трюков. Второй доклад тоже есть, но пока под вопросом… поэтому если есть желающие выступить и поделиться чем-то интересным по SQL Server пишите мне на почту указанную в профиле.

Место проведения: Украина, г. Харьков, ул. Маломясницкая, 9/11 (ст. метро пр. Гагарина) на базе академии «ШАГ»
Время начала: 29 января 2016 в 18:00

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

Комментарии (9)


  1. buriy
    14.01.2016 11:00

    Я не большой специалист по БД, поэтому сразу же возник вопрос: а как повлияет индекс (и частичный индекс на NULL / not NULL) на скорость вычисления?
    Ведь если NULL много, то может можно как-то их отдельно более быстро посчитать и учесть при суммировании? Да и возможны различные особенности внутренней реализации, скажем, более быстрое получение данных из индекса…


    1. pihel
      14.01.2016 11:56

      В индексе нет NULL значении, так что он будет ускорять арифметические операции.
      В Oracle будет fast full scan index при полном сканировании или range scan при частичном без физического обращения к таблице.


      1. Ivan22
        14.01.2016 14:49
        -1

        В индексе есть NULL значения. В большинстве субд включая mssql про который статья.


    1. Ivan22
      14.01.2016 14:50
      +2

      если NULL много лучше всего фильтрованный покрывающий индекс сделать — where col is not null. И запрос с таким же условием выполнять — ну и будет индекс скан.


    1. AlanDenton
      15.01.2016 12:24
      +2

      И того что я вижу, то SQL Server как-то «хитро» не обрабатывает NULL отдельно от других значений при агрегации. Поскольку NULL это точно такое же значение, как и 0, 'abc' и т.д. Оно занимает место на страницах:

      USE tempdb
      GO
      
      IF OBJECT_ID('tempdb.dbo.#temp') IS NOT NULL
          DROP TABLE #temp
      GO
      
      SELECT TOP(1000000) val = NULLIF(ROW_NUMBER() OVER (ORDER BY 1/0) % 2, 1)
      INTO #temp
      FROM [master].dbo.spt_values s1
      CROSS JOIN [master].dbo.spt_values s2
      GO
      
      CREATE NONCLUSTERED INDEX ix1 ON #temp (val)
      GO
      CREATE NONCLUSTERED INDEX ix2 ON #temp (val) WHERE val IS NOT NULL
      GO
      

      Теперь этим запросом посмотрим сколько места занимают индексы:

      SELECT i.name, a.total_pages, a.used_pages
      FROM sys.indexes i
      JOIN sys.partitions p ON i.[object_id] = p.[object_id] AND i.index_id = p.index_id
      JOIN sys.allocation_units a ON p.[partition_id] = a.container_id
      WHERE i.[object_id] = OBJECT_ID('#temp')
          AND i.name IS NOT NULL
      

      Как можно увидеть NULL хранятся в ix1 в противном случае, его размер был бы идентичный ix2:

      name        total_pages   used_pages
      ----------- ------------- ------------
      ix1         2739          2734
      ix2         1369          1366
      

      С точки зрения производительности… Чем меньше данных нужно вычитать из индекса и обработать, тем быстрее отработает запрос:

      SET STATISTICS IO ON
      SET STATISTICS TIME ON
      
      SELECT SUM(val) FROM #temp WITH(INDEX(ix1))
      SELECT SUM(val) FROM #temp WITH(INDEX(ix1)) WHERE val IS NOT NULL
      SELECT SUM(val) FROM #temp WITH(INDEX(ix2)) WHERE val IS NOT NULL
      

      Table '#temp_000000000005'. Scan count 1, logical reads 2729, physical reads 0, ...
       SQL Server Execution Times:
         CPU time = 125 ms,  elapsed time = 123 ms.
      
      Table '#temp_000000000005'. Scan count 1, logical reads 1372, physical reads 0, ...
       SQL Server Execution Times:
         CPU time = 62 ms,  elapsed time = 64 ms.
      
      Table '#temp__000000000005'. Scan count 1, logical reads 1365, physical reads 0, ...
       SQL Server Execution Times:
         CPU time = 47 ms,  elapsed time = 51 ms.
      

      План выполнения полученный в dbForge:


      1. pihel
        18.01.2016 11:37

        Искал в документации по MSSQL, где было бы сказано про NULL и index, чтот нигде не написано.
        В Oracle строго оговорено, что по умолчанию NULL не попадает в индекс.


        1. Ivan22
          18.01.2016 14:12
          +1

          тем не менее это так. Уникальный индекс кстати допускает наличие одного нула, и не допускает второй. Да и поиск нула идет по индексу.


  1. kemsky
    14.01.2016 16:30

    Как оказалось, операции агрегирования, в которых преобладают NULL значения обрабатываются быстрее.


    Было бы интересно посмотреть более подробную статистику в профайлере, бенчмарк сам по себе ничего не объясняет.


  1. LangovoyAndrey
    15.01.2016 00:40
    +2

    Спасибо за интересную статью!