Что быстрее: 0 или NULL?

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

Если хотите поделиться этой статьей с англоязычной аудиторией:
What is faster inside SUM & AVG: 0 or NULL?
  • +19
  • 27,2k
  • 9
Поделиться публикацией
Похожие публикации
Реклама помогает поддерживать и развивать наши сервисы

Подробнее
Реклама
Комментарии 9
  • 0
    Я не большой специалист по БД, поэтому сразу же возник вопрос: а как повлияет индекс (и частичный индекс на NULL / not NULL) на скорость вычисления?
    Ведь если NULL много, то может можно как-то их отдельно более быстро посчитать и учесть при суммировании? Да и возможны различные особенности внутренней реализации, скажем, более быстрое получение данных из индекса…
    • 0
      В индексе нет NULL значении, так что он будет ускорять арифметические операции.
      В Oracle будет fast full scan index при полном сканировании или range scan при частичном без физического обращения к таблице.
      • –1
        В индексе есть NULL значения. В большинстве субд включая mssql про который статья.
      • +2
        если NULL много лучше всего фильтрованный покрывающий индекс сделать — where col is not null. И запрос с таким же условием выполнять — ну и будет индекс скан.
        • +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:

          • 0
            Искал в документации по MSSQL, где было бы сказано про NULL и index, чтот нигде не написано.
            В Oracle строго оговорено, что по умолчанию NULL не попадает в индекс.
            • +1
              тем не менее это так. Уникальный индекс кстати допускает наличие одного нула, и не допускает второй. Да и поиск нула идет по индексу.
        • 0
          Как оказалось, операции агрегирования, в которых преобладают NULL значения обрабатываются быстрее.


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

            Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.