sql server - string_agg - T-SQL mal desempeño con CTE

string_agg sql server 2014

Question

Tengo una pregunta de rendimiento sobre las expresiones de tabla comunes en SQL Server. En nuestro equipo de desarrolladores, utilizamos muchos CTE de encadenamiento cuando construimos nuestras consultas. Actualmente estoy trabajando en una consulta que tuvo un rendimiento terrible. Pero descubrí que si en el medio de la cadena insertaba todos los registros hasta ese CTE en una tabla temporal y luego continuaba, pero seleccionando de esa tabla temporal mejoré significativamente el rendimiento. Ahora me gustaría obtener ayuda para comprender si este tipo de cambio solo se aplica a esta consulta específica y por qué los dos casos que verá a continuación difieren mucho en el rendimiento. ¿O podríamos sobreutilizar los CTE en nuestro equipo y podemos obtener un rendimiento general al aprender de este caso?

Por favor, trata de explicarme exactamente lo que está pasando aquí ...

El código está completo y podrá ejecutarlo en SQL Server 2008 y probablemente en 2005 también. Una parte está comentada y mi idea es que puede cambiar los dos casos comentando uno u otro. Puedes ver dónde poner tus comentarios de bloque, he marcado estos lugares con --block comment here y --end block comment here

Es el caso de ejecución lenta el que no está comentado por defecto. Aquí estás:

--Declare tables to use in example.
CREATE TABLE #Preparation 
(
    Date DATETIME NOT NULL
    ,Hour INT NOT NULL
    ,Sales NUMERIC(9,2)
    ,Items INT
);

CREATE TABLE #Calendar
(
    Date DATETIME NOT NULL
)

CREATE TABLE #OpenHours
(
    Day INT NOT NULL,
    OpenFrom TIME NOT NULL,
    OpenTo TIME NOT NULL
);

--Fill tables with sample data.
INSERT INTO #OpenHours (Day, OpenFrom, OpenTo)
VALUES
    (1, ''10:00'', ''20:00''),
    (2, ''10:00'', ''20:00''),
    (3, ''10:00'', ''20:00''),
    (4, ''10:00'', ''20:00''),
    (5, ''10:00'', ''20:00''),
    (6, ''10:00'', ''20:00''),
    (7, ''10:00'', ''20:00'')

DECLARE @CounterDay INT = 0, @CounterHour INT = 0, @Sales NUMERIC(9, 2), @Items INT;

WHILE @CounterDay < 365
BEGIN
    SET @CounterHour = 0;
    WHILE @CounterHour < 5
    BEGIN
        SET @Items = CAST(RAND() * 100 AS INT);
        SET @Sales = CAST(RAND() * 1000 AS NUMERIC(9, 2));
        IF @Items % 2 = 0
        BEGIN
            SET @Items = NULL;
            SET @Sales = NULL;
        END

        INSERT INTO #Preparation (Date, Hour, Items, Sales)
        VALUES (DATEADD(DAY, @CounterDay, ''2011-01-01''), @CounterHour + 13, @Items, @Sales);

        SET @CounterHour += 1;
    END
    INSERT INTO #Calendar (Date) VALUES (DATEADD(DAY, @CounterDay, ''2011-01-01''));
    SET @CounterDay += 1;
END

--Here the query starts.
;WITH P AS (
    SELECT DATEADD(HOUR, Hour, Date) AS Hour
        ,Sales
        ,Items
    FROM #Preparation
),
O AS (
        SELECT DISTINCT DATEADD(HOUR, SV.number, C.Date) AS Hour
        FROM #OpenHours AS O
            JOIN #Calendar AS C ON O.Day = DATEPART(WEEKDAY, C.Date)
            JOIN master.dbo.spt_values AS SV ON SV.number BETWEEN DATEPART(HOUR, O.OpenFrom) AND DATEPART(HOUR, O.OpenTo)
),
S AS (
    SELECT O.Hour, P.Sales, P.Items
    FROM O
        LEFT JOIN P ON P.Hour = O.Hour
)

--block comment here case 1 (slow performing)
--With this technique it takes about 34 seconds.
,N AS (
        SELECT  
            A.Hour
            ,A.Sales AS SalesOrg
            ,CASE WHEN COALESCE(B.Sales, C.Sales, 1) < 0
                THEN 0 ELSE COALESCE(B.Sales, C.Sales, 1) END AS Sales
            ,A.Items AS ItemsOrg
            ,COALESCE(B.Items, C.Items, 1) AS Items
        FROM S AS A
        OUTER APPLY (SELECT TOP 1 *
                     FROM S
                     WHERE Hour <= A.Hour
                        AND Sales IS NOT NULL
                        AND DATEDIFF(DAY, Hour, A.Hour) = 0                      
                     ORDER BY Hour DESC) B
        OUTER APPLY (SELECT TOP 1 *
                     FROM S
                     WHERE Sales IS NOT NULL
                        AND DATEDIFF(DAY, Hour, A.Hour) = 0
                     ORDER BY Hour) C
    )
--end block comment here case 1 (slow performing)

/*--block comment here case 2 (fast performing)
--With this technique it takes about 2 seconds.
SELECT * INTO #tmpS FROM S;

WITH
N AS (
        SELECT  
            A.Hour
            ,A.Sales AS SalesOrg
            ,CASE WHEN COALESCE(B.Sales, C.Sales, 1) < 0
                THEN 0 ELSE COALESCE(B.Sales, C.Sales, 1) END AS Sales
            ,A.Items AS ItemsOrg
            ,COALESCE(B.Items, C.Items, 1) AS Items
        FROM #tmpS AS A
        OUTER APPLY (SELECT TOP 1 *
                     FROM #tmpS
                     WHERE Hour <= A.Hour
                        AND Sales IS NOT NULL
                        AND DATEDIFF(DAY, Hour, A.Hour) = 0                      
                     ORDER BY Hour DESC) B
        OUTER APPLY (SELECT TOP 1 *
                     FROM #tmpS
                     WHERE Sales IS NOT NULL
                        AND DATEDIFF(DAY, Hour, A.Hour) = 0
                     ORDER BY Hour) C
    )
--end block comment here case 2 (fast performing)*/
SELECT * FROM N ORDER BY Hour


IF OBJECT_ID(''tempdb..#tmpS'') IS NOT NULL DROP TABLE #tmpS;

DROP TABLE #Preparation;
DROP TABLE #Calendar;
DROP TABLE #OpenHours;

Si desea probar y entender lo que estoy haciendo en el último paso, tengo una pregunta SO al respecto here .

Para mí, el caso 1 tarda unos 34 segundos y el caso 2 tarda unos 2 segundos. La diferencia es que almaceno el resultado de S en una tabla temporal en el caso 2, en el caso 1, uso S en mi siguiente CTE directamente.

Answer 1

CTE es solo un atajo de sintaxis. Ese CTE se ejecuta (y se vuelve a ejecutar) en la unión. Con el #temp se evalúa una vez y luego los resultados se reutilizan en la unión.

La documentación es engañosa.

MSDN_CTE

Una expresión de tabla común (CTE) se puede considerar como un conjunto de resultados temporal.

Este artículo lo explica mejor.

PapaCTEarticle

Un CTE es un buen ajuste para este tipo de escenario, ya que hace que el T-SQL sea mucho más legible (como una vista), pero se puede usar más de una vez en una consulta que sigue inmediatamente en el mismo lote. Por supuesto, no está disponible más allá de ese alcance. Además, el CTE es una construcción a nivel de idioma, lo que significa que SQL Server no crea internamente tablas temporales o virtuales. La consulta subyacente del CTE se llamará cada vez que se haga referencia en la consulta inmediatamente siguiente.

Echa un vistazo a los parámetros de valor de tabla

TVP

Tienen la estructura como un #temp pero no tan alto. Son de solo lectura pero parece que solo necesitas de solo lectura. La creación y eliminación de un #temp variará, pero en un servidor de bajo a medio es un hit de 0,1 segundos y con TVP no hay prácticamente ningún hit.

Answer 2

Un CTE es esencialmente solo una vista desechable. Casi nunca hará una consulta más rápido que simplemente poniendo el código CTE en una cláusula FROM como una expresión de tabla.

En su ejemplo, el problema real son las funciones de fecha que creo.

Su primer caso (lento) requiere que las funciones de fecha se ejecuten para cada fila.

Para su segundo caso (más rápido), se ejecutan una vez y se almacenan en una tabla.

Esto normalmente no es tan notable a menos que haga algún tipo de lógica en el campo derivado de la función. En su caso, usted está haciendo un ORDER BY Hour , que es muy costoso. En su segundo ejemplo, es una ordenación simple en un campo, pero en el primero está ejecutando esa función para cada fila, LUEGO ordenando.

Para una lectura más profunda en CTE , vea esta pregunta en DBA.SE.