[SQL] 避免重覆的 WHERE 條件 2

一早打開信箱就看見自動成長的通知 mail，打開一看更是傻眼，TempDB 的自動成長 2 分鐘內被觸發 65 次，空間大約增長了 1 G，Orz ~~

利用 TOP 10 語法找疑犯，發現這段 ad-hoc T-SQL 語法，跑出超耗能的結果，且執行時間在 TempDB 自動觸發前後

分析 T-SQL 語法發現，使用者要抓 100 年至今的資料，再加上有四處重覆 WHERE 條件，導致此結果

T-SQL 片段語法，22、37、38 這三個條件是重覆的

SELECT
  ColName .............  
FROM pmsethx AS X
  ..............................
  JOIN
    (
      SELECT 
        ..........................
      FROM
        (
          SELECT 
            .....................
          FROM pmsetorder AS S1
            JOIN
              (
                SELECT 
                  ..................
                FROM pmsetodas
                WHERE (mt_num LIKE '8_%' ) AND ORDER_NO >= 'B100'
              ) AS H1 ON S1.order_no = H1.ORDER_NO
          WHERE (S1.MODEL_NO LIKE '0%') 
            AND S1.ORDER_NO >='B100'
        ) AS T1
      GROUP BY T1.ULVLNUM , T1.DLVLNUM
    )  AS Z ON X.ORDER_NO = Z.ORDER_NO
  JOIN
    (
      SELECT 
        ......................
      FROM pmsethx 
      WHERE ORDER_NO >= 'B100'  
      GROUP BY ORDER_NO 
    ) AS y ON x.order_no = y.order_no AND y.hxno = 1
WHERE X.TUBE_DIA = '3/8"' 
  AND X.TUBE_pitch = 'P29.40□' 
  AND X.EF_LENGTH = 1061 
  AND (X.mt_num like '8_%') 
  AND X.ORDER_NO > = 'B100' 

把多餘 WHERE 條件拿掉，下圖是比較表（只列出關鍵 Table）

Table	scan count 前	scan count 後	Logical Read 前	Logical Read 後
pmsethx	2,795,513	410	8,504,874	2,398
pmsetfin	24,303	500	8,508,090	112,289

CPU Time 比較表

	修改前	修改後
CPU Time (ms)	44,242	3,572

延伸閱讀
• [SQL] 標準報表 - 磁碟使用量 
• [SQL] 避免重覆的 WHERE 條件