Блог

Я хочу оптимизировать CTE в SQL Server. CTE занимает так много времени, чтобы выполнить запрос. Однако, когда я использую временную таблицу вместо CTE, выполнение CTE происходит быстро. Это из-за многократного присоединения к CTE в конечном запросе?

Код CTE выглядит следующим образом:

 WITH Aggregates
AS (SELECT Survey_Nm,
           Brand_Nm,
           Dealer_Nm,
           Market_Nm,
           Region_Nm,
           SUM(Base_Points) / NULLIF(SUM(Questions_Answered), 0) AS Initial_Score,
           (SUM(ExtraCredit_Points) * 1.0 / COUNT(DISTINCT Response_Id)) / 100 AS ExtraCredit_Perc,
           (SUM(Base_Points) / NULLIF(SUM(Questions_Answered), 0))
             ((SUM(ExtraCredit_Points) * 1.0 / COUNT(DISTINCT Response_Id)) / 100) AS Score
    FROM dbo.vw_RPTS_SurveyResponses
    GROUP BY GROUPING SETS((Market_Nm), (Region_Nm), (), (Survey_Nm, Brand_Nm, Dealer_Nm, Market_Nm, Region_Nm)))
SELECT SR.Survey_Nm,
       SR.Brand_Nm,
       SR.Dealer_Nm,
       SR.Market_Nm,
       SR.Region_Nm,
       SR.Initial_Score AS Initial_Dealer_Score,
       SR.ExtraCredit_Perc AS DealerExtraCredit_Perc,
       SR.Score AS Dealer_Score,
       M.Initial_Score AS Initial_Market_Score,
       M.ExtraCredit_Perc AS Market_ExtraCredit_Perc,
       M.Score AS Market_Score,
       R.Initial_Score AS Initial_Region_Score,
       R.ExtraCredit_Perc AS Region_ExtraCredit_Perc,
       R.Score AS Region_Score,
       N.Initial_Score AS Initial_National_Score,
       N.ExtraCredit_Perc AS Nation_ExtraCredit_Perc,
       N.Score AS National_Score
FROM Aggregates AS SR
    INNER JOIN Aggregates AS M
        ON M.Market_Nm = SR.Market_Nm
           AND M.Survey_Nm IS NULL
           AND M.Brand_Nm IS NULL
           AND M.Dealer_Nm IS NULL
           AND M.Region_Nm IS NULL
           AND M.Market_Nm IS NOT NULL
    INNER JOIN Aggregates AS R
        ON R.Region_Nm = SR.Region_Nm
           AND R.Survey_Nm IS NULL
           AND R.Brand_Nm IS NULL
           AND R.Dealer_Nm IS NULL
           AND R.Region_Nm IS NOT NULL
           AND R.Market_Nm IS NULL
    INNER JOIN Aggregates AS N
        ON 1 = 1
           AND N.Survey_Nm IS NULL
           AND N.Brand_Nm IS NULL
           AND N.Dealer_Nm IS NULL
           AND N.Region_Nm IS NULL
           AND N.Market_Nm IS NULL
WHERE SR.Survey_Nm IS NOT NULL
      AND SR.Brand_Nm IS NOT NULL
      AND SR.Dealer_Nm IS NOT NULL
      AND SR.Market_Nm IS NOT NULL
      AND SR.Region_Nm IS NOT NULL;