Плохая производительность T-SQL с CTE

У меня есть вопрос о производительности общих табличных выражений в SQL Server. В нашей команде разработчиков мы используем множество цепочек CTE при построении наших запросов. В настоящее время я работаю над запросом, который имел ужасную производительность. Но я обнаружил, что если я в середине цепочки вместо этого вставлю все записи до этого CTE во временную таблицу, а затем продолжу, но выбирая из этой временной таблицы, я значительно улучшу производительность. Теперь я хотел бы получить некоторую помощь, чтобы понять, применим ли этот тип изменения только к этому конкретному запросу и почему два случая, которые вы увидите ниже, так сильно различаются по производительности. Или мы можем чрезмерно использовать CTE в нашей команде и можем ли мы повысить производительность в целом, извлекая уроки из этого случая?

Пожалуйста, объясните мне, что именно здесь происходит...

Код завершен, и вы сможете запустить его на SQL Server 2008 и, возможно, 2005. Одна часть закомментирована, и моя идея состоит в том, что вы можете переключать два случая, комментируя один или другой. Вы можете увидеть, где размещать свои комментарии к блоку, я пометил эти места с помощью --block comment здесьи --end block comment здесь

Это медленно работающий случай, который по умолчанию раскомментирован. . Вот оно:

--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;

Если вы хотите попытаться понять, что я делаю на последнем шаге, у меня есть ТАКОЙ вопрос по этому поводу здесь.

Для меня вариант 1 занимает около 34 секунд, а случай 2 — около 2 секунд. Разница в том, что я сохраняю результат S во временной таблице в случае 2, а в случае 1 я использую S напрямую в своем следующем CTE.