原 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 Time | 875 ms | 0 ms |
PMMtInoutPlan Logical Read | 36,833 | 279 |
沒有留言:
張貼留言