星期二, 7月 31, 2018

[SQL] 避免欄位相加進行比對

觀察 TOP 10 CPU 時間 DMV 語法時,發現下面語法跑了 200 多次且 CPU Time 都超過 1 秒,Orz

原 TSQL 語法,是把欄位利用 + 串接在一起後,來進行資料比對,這一看就知道一定會 GG 的
INSERT INTO pmmtinoutplan (................)
  SELECT
    .............................
  FROM PMFINISHDATE AS F
    JOIN PMSETORDER AS S ON F.ORDER_NO = S.ORDER_NO
  WHERE
    AND .............................
    AND F.ORDER_NO + F.PID NOT IN 
      ( 
        SELECT
          ORDER_NO + PID
        FROM pmmtinoutplAN
      )
改善後 TSQL 語法,改寫為利用 NOT EXISTS 來判斷資料是否存在
INSERT INTO pmmtinoutplan (................)
  SELECT
    .............................
  FROM PMFINISHDATE AS F
    JOIN PMSETORDER AS S ON F.ORDER_NO = S.ORDER_NO
  WHERE
    AND .............................
    AND NOT EXISTS
      (
        SELECT 1
        FROM pmmtinoutplan AS M 
        WHERE F.Order_NO = M.Order_NO 
          AND F.PID = M.PID
      )
測試環境效能資料,只用 insert 一筆資料來測試而已喔

修正前修正後
CPU Time875 ms0 ms
PMMtInoutPlan Logical Read36,833279

沒有留言:

張貼留言