¿Cuál es más rápido en SUM y AVG: 0 o NULL?

Hay tres funciones agregadas que se usan más comúnmente en la práctica: CONTAR, CIM y Casarse.. El primero ya ha sido comentado anteriormente, y los otros dos tienen interesantes matices de ejecución. Pero antes un poco de teoría...

Cuando se usan funciones agregadas en términos de ejecución pueden ser dos operadores: Agregado de flujo y Coincidencia de hashdependiendo del flujo de entrada.

El primero puede requerir un conjunto preordenado de valores de entrada, mientras que Agregado de flujo no bloquea la ejecución de operadores posteriores.

En turno, Coincidencia de hash es un operador de bloqueo (con raras excepciones) y no requiere ordenar el flujo de entrada. Hash Match utiliza una tabla hash que se crea en la memoria y, en caso de una estimación incorrecta del número esperado de filas, el operador puede arrojar resultados en tempdb.
En resumen, Agregado de flujo funciona bien con pequeños conjuntos de datos ordenados, y Coincidencia de hash se adapta bien a conjuntos grandes sin clasificar y es fácil de procesar en paralelo.

Ahora que dominamos la teoría, veamos cómo funcionan las funciones agregadas.

Digamos que necesitamos calcular el precio promedio de todos los productos:

SELECT AVG(Price) FROM dbo.Price

usando una tabla con una estructura bastante simple:

CREATE TABLE dbo.Price (
    ProductID INT PRIMARY KEY,
    LastUpdate DATE NOT NULL, 
    Price SMALLMONEY NULL,
    Qty INT
)

Como tenemos agregación escalar, estamos esperando a ver Agregado de flujo en términos de rendimiento:

Este operador realiza dos operaciones de agregación. COUNT_BIG y CIM (aunque a nivel físico se realiza como una sola operación) en Precio columna:

No olvide que el promedio solo se calcula para NO NULOporque COUNT_BIG la operación utiliza una columna, no un asterisco. En consecuencia, la solicitud es la siguiente:

SELECT AVG(v)
FROM (
    VALUES (3), (9), (NULL)
) t(v)

devuelve 6 en lugar de 4.

Ahora veamos Calcular el escalarque tiene una expresión interesante para verificar la división por cero:

Expr1003 =
    CASE WHEN [Expr1004]=(0)
        THEN NULL
        ELSE [Expr1005]/CONVERT_IMPLICIT(money,[Expr1004],0)
    END

Y trata de calcular la cantidad total:

SELECT SUM(Price) FROM dbo.Price

El plan de implementación sigue siendo el mismo:

Pero si miras las operaciones que realiza Agregado de flujo…

uno puede estar un poco sorprendido. Por qué servidor SQL cuenta si solo necesito la cantidad? La respuesta está en Calcular el escalar:

[Expr1003] = Scalar Operator(CASE WHEN [Expr1004]=(0) THEN NULL ELSE [Expr1005] END)

Si no tienes en cuenta CONTARque según T-SQL semántica, si no hay cadenas en el flujo de entrada, deberíamos obtener NULOno 0. Este comportamiento es cierto para ambas agregaciones (escalares y vectoriales):

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)

Además, tal verificación se realiza para NULOy NO NULO columnas Ahora consideremos los ejemplos en los que las características se describen arriba. CIM y Casarse. son efectivos

Si desea calcular el promedio, no utilice CONTAR + SUMA:

SELECT SUM(Price) / COUNT(Price) FROM dbo.Price

Después de todo, tal solicitud sería menos efectiva que el uso explícito Casarse..

Además... no hay necesidad de transmitir explícitamente NULO a la función agregada:

SELECT
      SUM(CASE WHEN Price > 100 THEN Qty ELSE NULL END),
      SUM(CASE WHEN Price < 100 THEN Qty ELSE NULL END)
FROM dbo.Price

Porque en esta consulta:

SELECT
      SUM(CASE WHEN Price > 100 THEN Qty END),
      SUM(CASE WHEN Price < 100 THEN Qty END)
FROM dbo.Price

Optimizer reemplaza automáticamente:

Pero, ¿qué pasa si quiero obtener 0 en los resultados? NULO? La gente a menudo usa OTRO sin dudarlo:

SELECT
      SUM(CASE WHEN Price > 100 THEN Qty ELSE 0 END),
      SUM(CASE WHEN Price < 100 THEN Qty ELSE 0 END)
FROM dbo.Price

Obviamente, en este caso lograremos el resultado deseado... y una advertencia ya no confundirá la vista:

Warning: Null value is eliminated by an aggregate or other SET operation.

Aunque, es mejor escribir la consulta así:

SELECT
      ISNULL(SUM(CASE WHEN Price > 100 THEN Qty END), 0),
      ISNULL(SUM(CASE WHEN Price < 100 THEN Qty END), 0)
FROM dbo.Price

Y eso es bueno, no porque CASO el operador correrá más rápido. Ya sabemos que el optimizador inserta automáticamente OTRO NULO allí… Entonces, ¿cuáles son los beneficios de la última opción?

Resultó que las operaciones de agregación en las que NULO Los valores dominan, se procesan más rápido.

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)

Tomó:

SQL Server Execution Times:
   CPU time = 5985 ms, elapsed time = 5989 ms.

Ahora vamos a cambiar el valor:

DECLARE @i INT = 0

Y hazlo de nuevo:

SQL Server Execution Times:
   CPU time = 6437 ms, elapsed time = 6451 ms.

Esto no es tan importante, pero en ciertas situaciones todavía proporciona una base para la optimización.
El final de la obra, ¿cae el telón? No, eso no es todo...
Como dice uno de mis amigos, “No hay ni blanco ni negro… el mundo es multicolor” y por eso voy a poner un ejemplo interesante donde NULO puede doler.

Vamos a crear una función lenta y una tabla de prueba:

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

Y cumplir la consulta:

SET STATISTICS TIME ON

SELECT SUM(LEN(dbo.udf(N)))
FROM #temp

SQL Server Execution Times:
   CPU time = 9109 ms, elapsed time = 11603 ms.

Ahora intentemos agregar ES NULO para la expresión que se transmite CIM:

SELECT SUM(ISNULL(LEN(dbo.udf(N)), 0))
FROM #temp

SQL Server Execution Times:
   CPU time = 4562 ms, elapsed time = 5719 ms.

La velocidad de ejecución se ha reducido a la mitad. Debo decir que esto no es magia... sino un error servidor SQL el motor que era "Reparado" sobre Microsoft en SQL Server 2012 CTP.

La esencia del problema es la siguiente: el resultado de la expresión dentro CIM o Casarse. la función puede ser evaluada dos veces si el optimizador cree que puede volver NULO.

todo fue revisado Microsoft SQL Server 2012 (SP3) (KB3072779) - 11.0.6020.0 (X64).
Todos los planes de ejecución de dbForge Studio para SQL Server.