星期四, 8月 02, 2018

[SQL] WHERE 條件合理性

利用 DMV 語法抓出有發生平行處理執行計劃時發現下述語法
SELECT
  S.ORDER_NO ,
  I.ODNO
FROM PMSETORDER AS S
  LEFT JOIN POITEM AS I ON S.ORDER_NO = I.ORDER_NO
WHERE S.ORDER_NO = @P1
  OR i.odno = @P2
[SQL] WHERE 條件合理性-1

從 DMV 抓出傳入的參數值是派工單號,然後更神奇的事情是,Odno 欄位是訂單編號,永遠不會成立的 WHERE 條件,Orz

修正 TSQL,把 Odno 條件拿掉
SELECT
  S.ORDER_NO ,
  I.ODNO
FROM PMSETORDER AS S
  LEFT JOIN POITEM AS I ON S.ORDER_NO = I.ORDER_NO
WHERE S.ORDER_NO = @P1

[SQL] WHERE 條件合理性-2
執行計畫比較

[SQL] WHERE 條件合理性-3

沒有留言:

張貼留言