ejemplos - iif sql ejemplo

Rendimiento de agregación condicional

Question

Tengamos los siguientes datos

 IF OBJECT_ID(''dbo.LogTable'', ''U'') IS NOT NULL  DROP TABLE dbo.LogTable

 SELECT TOP 100000 DATEADD(day, ( ABS(CHECKSUM(NEWID())) % 65530 ), 0) datesent 
 INTO [LogTable]
 FROM    sys.sysobjects
 CROSS JOIN sys.all_columns

Quiero contar el número de filas, el número de filas del año pasado y el número de filas de los últimos diez años. Esto se puede lograr mediante la consulta de agregación condicional o mediante subconsultas de la siguiente manera

-- conditional aggregation query
SELECT
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-1,GETDATE())
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-10,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable


-- subqueries
SELECT
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-1,GETDATE())
) last_year_cnt,
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-10,GETDATE())
) last_ten_year_cnt

Si realiza las consultas y mira los planes de consulta, verá algo como

Claramente, la primera solución tiene un plan de consultas, una estimación de costos e incluso el comando SQL se ve más conciso y elegante. Sin embargo, si mide el tiempo de CPU de la consulta con SET STATISTICS TIME ON , obtengo los siguientes resultados (he medido varias veces con aproximadamente los mismos resultados)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 41 ms.

(1 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 26 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 0 ms.

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

Por lo tanto, la segunda solución tiene un rendimiento ligeramente mejor (o el mismo) que la solución que usa la agregación condicional. La diferencia se hace más evidente si creamos el índice en el atributo datesent .

CREATE INDEX ix_logtable_datesent ON dbo.LogTable(DateSent)

Luego, la segunda solución comienza a utilizar la Index Seek lugar de Table Scan de Table Scan y su rendimiento de tiempo de CPU de consulta se reduce a 16 ms en mi computadora.

Mis preguntas son dos: (1) por qué la solución de agregación condicional no supera a la solución de subconsulta al menos en el caso sin índice, (2) es posible crear un ''índice'' para la solución de agregación condicional (o reescribir la consulta de agregación condicional ) para evitar la exploración, o ¿es la agregación condicional generalmente inadecuada si nos preocupa el rendimiento?

Nota: Puedo decir que este escenario es bastante optimista para la agregación condicional, ya que seleccionamos el número de todas las filas, lo que siempre lleva a una solución que usa el escaneo. Si no se necesita el número de todas las filas, la solución indexada con subconsultas no tiene exploración, mientras que la solución con agregación condicional debe realizar la exploración de todos modos.

EDITAR

Vladimir Baranov respondió básicamente a la primera pregunta (muchas gracias). Sin embargo, la segunda pregunta sigue siendo. Puedo ver en StackOverflow las respuestas que usan soluciones de agregación condicional con bastante frecuencia y atraen mucha atención al ser aceptadas como la solución más elegante y clara (y, a veces, ser propuestas como la solución más eficiente). Por lo tanto, voy a generalizar ligeramente la pregunta:

¿Podría darme un ejemplo, donde la agregación condicional supera notablemente la solución de la subconsulta?

Para simplificar, supongamos que los accesos físicos no están presentes (los datos están en la memoria caché del búfer) ya que los servidores de la base de datos de hoy siguen siendo la mayoría de sus datos en la memoria.

Answer 1

Breve resumen

• El rendimiento del método de subconsultas depende de la distribución de los datos.
• El rendimiento de la agregación condicional no depende de la distribución de datos.

El método de las subconsultas puede ser más rápido o más lento que la agregación condicional, depende de la distribución de datos.

Naturalmente, si la tabla tiene un índice adecuado, es probable que las subconsultas se beneficien de él, ya que el índice permitiría escanear solo la parte relevante de la tabla en lugar del escaneo completo. Es poco probable que tener un índice adecuado beneficie significativamente al método de agregación condicional, ya que de todos modos escaneará el índice completo. El único beneficio sería si el índice es más estrecho que la tabla y el motor tendría que leer menos páginas en la memoria.

Sabiendo esto puedes decidir qué método elegir.

Primer examen

Hice una tabla de prueba más grande, con 5M filas. No había índices en la mesa. Medí las estadísticas de E / S y CPU utilizando el Explorador de planes de Sentry de SQL. Utilicé SQL Server 2014 SP1-CU7 (12.0.4459.0) Express de 64 bits para estas pruebas.

De hecho, sus consultas originales se comportaron como usted describió, es decir, las subconsultas fueron más rápidas a pesar de que las lecturas fueron 3 veces más altas.

Después de algunos intentos en una tabla sin índice, reescribí su agregado condicional y DATEADD variables para mantener el valor de DATEADD expresiones DATEADD .

En general, el tiempo se hizo significativamente más rápido.

Luego reemplacé SUM con COUNT y se volvió un poco más rápido de nuevo.

Después de todo, la agregación condicional se convirtió en algo tan rápido como las subconsultas.

Calentar el caché (CPU = 375)

SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

Subconsultas (CPU = 1031)

SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-1,GETDATE())
) last_year_cnt,
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,-10,GETDATE())
) last_ten_year_cnt
OPTION (RECOMPILE);

Agregación condicional original (CPU = 1641)

SELECT -- conditional original
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-1,GETDATE())
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,-10,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Agregación condicional con variables (CPU = 1078)

DECLARE @VarYear1 datetime = DATEADD(year,-1,GETDATE());
DECLARE @VarYear10 datetime = DATEADD(year,-10,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE 0 END) AS last_year_cnt,
    SUM(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Agregación condicional con variables y COUNT en lugar de SUM (CPU = 1062)

SELECT -- conditional variable, count, not sum
    COUNT(*) AS all_cnt,
    COUNT(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE NULL END) AS last_year_cnt,
    COUNT(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Según estos resultados, mi conjetura es que CASE invocó DATEADD para cada fila, mientras que WHERE fue lo suficientemente inteligente como para calcularlo una vez. Plus COUNT es un poquito más eficiente que SUM .

Al final, la agregación condicional es solo un poco más lenta que las subconsultas (1062 vs 1031), tal vez porque WHERE es un poco más eficiente que CASE en sí misma y, además, WHERE filtra unas pocas filas, por lo que COUNT tiene que procesar menos filas.

En la práctica, usaría la agregación condicional, porque creo que la cantidad de lecturas es más importante. Si su tabla es pequeña para ajustarse y permanecer en el grupo de búferes, cualquier consulta será rápida para el usuario final. Pero, si la tabla es más grande que la memoria disponible, entonces espero que la lectura del disco ralentice significativamente las subconsultas.

Segunda prueba

Por otro lado, filtrar las filas lo antes posible también es importante.

Aquí hay una ligera variación de la prueba, que lo demuestra. Aquí establezco el umbral para que sea GETDATE () + 100 años, para asegurarme de que ninguna fila cumpla con los criterios del filtro.

Calentar el caché (CPU = 344)

SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

Subconsultas (CPU = 500)

SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt, 
(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,100,GETDATE())
) last_year_cnt
OPTION (RECOMPILE);

Agregación condicional original (CPU = 937)

SELECT -- conditional original
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > DATEADD(year,100,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Agregación condicional con variables (CPU = 750)

DECLARE @VarYear100 datetime = DATEADD(year,100,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,
    SUM(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

Agregación condicional con variables y COUNT en lugar de SUM (CPU = 750)

SELECT -- conditional variable, count, not sum
    COUNT(*) AS all_cnt,
    COUNT(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

A continuación se muestra un plan con subconsultas. Puede ver que 0 filas entraron en el Agregado de Arroyos en la segunda subconsulta, todas se filtraron en el paso de Exploración de Tabla.

Como resultado, las subconsultas son de nuevo más rápidas.

Tercera prueba

Aquí cambié los criterios de filtrado de la prueba anterior: todos > fueron reemplazados con < . Como resultado, el recuento condicional contó todas las filas en lugar de ninguna. ¡Sorpresa sorpresa! La consulta de agregación condicional tomó los mismos 750 ms, mientras que las subconsultas se convirtieron en 813 en lugar de 500.

Aquí está el plan para las subconsultas:

¿Podría darme un ejemplo, donde la agregación condicional supera notablemente la solución de la subconsulta?

Aquí está. El rendimiento del método de subconsultas depende de la distribución de los datos. El rendimiento de la agregación condicional no depende de la distribución de datos.

El método de las subconsultas puede ser más rápido o más lento que la agregación condicional, depende de la distribución de datos.

Sabiendo esto puedes decidir qué método elegir.

Detalles de bonificación

Si pasa el mouse sobre el operador Table Scan , puede ver el Actual Data Size en diferentes variantes.

1. COUNT(*) simple COUNT(*) :

2. Agregación condicional:

3. Subconsulta en prueba 2:

4. Subconsulta en prueba 3:

Ahora queda claro que la diferencia en el rendimiento probablemente se deba a la diferencia en la cantidad de datos que fluye a través del plan.

En el caso de COUNT(*) simple COUNT(*) no hay una Output list (no se necesitan valores de columna) y el tamaño de los datos es el más pequeño (43MB).

En caso de agregación condicional, esta cantidad no cambia entre las pruebas 2 y 3, siempre es de 72 MB. Output list tiene una columna de datesent .

En el caso de las subconsultas, esta cantidad cambia según la distribución de datos.