[SQL] 限制資料筆數 2

這篇筆記算是下列兩篇筆記相關

• [SQL] 限制資料筆數
• [SQL] UNION ALL 一定會讀出全部資料？

想法也是先限制住 PSendout、PsendBack、PSendStock 這三個 Table 資料，避免過多資料被送出，才會各別對 Table 進行篩選，下述是語法重點片段

簡易架構說明：就 1 個 Master 搭配 3 個 Detail

改善前重點語法

SELECT
  ---------------
FROM psendoutheader AS r
  JOIN psendout AS p ON r.OUT_ID = p.out_id
  JOIN poitem AS m ON p.odno = m.odno
                      AND p.modelcode = m.modelcode
WHERE r.PAYDATE = '10804'
UNION ALL
SELECT
  ---------------
FROM psendoutheader AS r
  JOIN psendstock AS p ON r.OUT_ID = p.stock_no
  JOIN poitem AS m ON p.odno = m.odno
                      AND p.modelcode = m.modelcode
WHERE r.PAYDATE = '10804'
UNION ALL
SELECT
  ---------------
FROM psendoutheader AS r
  JOIN psendback AS p ON r.OUT_ID = p.back_id
WHERE r.PAYDATE = '10804'

改善後重點語法

SELECT 
  ---------------
FROM psendoutheader AS r
  JOIN
    (
      SELECT  ---------------
      FROM psendout
      UNION ALL
      SELECT  ---------------
      FROM psendstock
      UNION ALL
      SELECT  ---------------
      FROM psendback
    ) AS p ON R.Out_ID = p.Out_ID
  LEFT JOIN POItem AS M ON p.odno = m.odno
                           AND p.modelcode = m.modelcode
WHERE r.PAYDATE = '10804'

效能改善比較

	改善前	改善後
Psendout Logical Read	188,982	993

改善前後執行計畫觀察

下圖原語法，可以看見 PsendoutHeader、PSendOut、POItem 綁在一起抓資料，Psendout 拋出大量資料

改善後語法是透過 Nested Loop 一筆一筆資料進 Psnedout 搜尋資料，資料量少超級多，顯而易見效能也會跟著改善