[SQL] 避免 CTE 遞迴產生日期總表

剛學 CTE 時，喜歡用 CTE 來跑日期總表，發現效能實在是很糟糕，後來建立一個實體 Table - DateTable，用來記錄日期資訊，這個算是漏網之魚，^^''

;
WITH CTE
AS
(
   SELECT
     CAST(@P1 AS DATETIME) AS CTEDate UNION ALL SELECT
     DATEADD(DD, 1, CTEDate)
   FROM CTE
   WHERE DATEADD(DD, 1, CTEDate) <= CAST(@P2 AS DATETIME)
)
SELECT
  ..................
FROM 
  (
    SELECT
      ..................
    FROM 
      (
        SELECT
          ..................
        FROM CTE AS T
          JOIN [NC資料表] AS N ON N.結案時間 >= CTEDate
                             AND N.結案時間 < DATEADD(DD, 1, CTEDate)
          ..................
      ) AS TF
    GROUP BY ..................
  ) AS F
OPTION (MAXRECURSION 0)

Statistics 很糟糕外，執行計畫也跑出平行處理，下圖為部分截圖

修正後語法

SELECT
  ..................
FROM 
  (
    SELECT
      ..................
    FROM 
      (
        SELECT
          ..................
        FROM DateTable AS T
          JOIN [NC資料表] AS N ON N.結案時間 >= T.Date
                             AND N.結案時間 < DATEADD(DD, 1, T.Date)
          ..................
        WHERE T.Date BETWEEN @P1 AND @P2
      ) AS TF
    GROUP BY ..................
  ) AS F

修正前後執行計劃成本差異

 

修正前後 Statistics 差異

	修正前	修正後
CPU Time	1,562 ms	31 ms
WorkTable Logical Read	815,419	0