[SQL] 限制資料筆數

收到 Block 超過 1 min 的 Mail 通知，發現下述語法，在 SSMS 上執行該語法，TSQL 執行的 Statistics 完全正常，透過執行計畫才發現異常之處

26 - 33 行語法，同事擔心抓出 PurchDetail 讀出太多資料，因此希望透過 PMSetOrder.Done 來限制過多資料被讀出

SELECT
  ..............

  CASE
    WHEN F.NO <> '' THEN F.NO
    ELSE ISNULL(p.purno, SPACE(11))
  END purno ,
  
  ..............

FROM pmfinishdate AS F
  LEFT JOIN PMMTINOUTPLAN AS MTP ON F.ORDER_NO = MTP.ORDER_NO
                                    AND F.PID = MTP.PID
  LEFT JOIN PRIVATE.DBO.EMPLOY AS EE ON EE.EMPNO = MTP.EMPNO
  LEFT JOIN 
  (
    ......................
  ) AS K ON K.ORDER_NO = F.ORDER_NO
            AND K.PID = F.PID
  JOIN 
  (
    ........................
  ) AS s ON s.order_no = F.order_no
  LEFT JOIN 
  (
    SELECT
      p1.order_no , 
      MIN(p1.purno) AS purno ,
      p1.pid
    FROM ic.dbo.purchdetail AS p1
      JOIN pmsetorder AS s1 ON p1.order_no = s1.order_no
                              AND s1.done = ''
    GROUP BY p1.order_no,p1.pid
  ) AS p ON p.order_no = f.order_no
            AND P.PID = F.PID
  LEFT JOIN 
  (
    .................................
  ) AS W ON F.ORDER_NO = W.ORDER_NO
            AND F.PID = W.PID
            AND F.WK_ID = W.WK_ID
  LEFT JOIN 
  (
    .................................
  ) AS x ON f.order_no = x.order_no
            AND f.pid = x.pid
  LEFT JOIN pmdepworkplan AS wp ON f.order_no = wp.order_no
                                   AND F.PID = WP.PID
                                   AND wp.wk_id = 'D203'
WHERE F.wk_id = 'D203'
  AND F.DONEQTY < S.QTY
ORDER BY F.F_DATE, F.TZ

透過執行計畫，可以發現 PMSetOrder.Done 限制資料是完全沒有發揮作用，反而導致 PurchDetail 利用非叢集索引 - IM_PurchDetail_OrderNO 跑 index scan 把全部資料讀出

把 26 - 33 語法修正，不在需要 PMSetOrder.Done 來限制資料

SELECT
  order_no , 
  MIN(purno) AS purno ,
  pid
FROM ic.dbo.purchdetail
GROUP BY order_no , pid

再次執行就可以發現 PurchDetail 利用 IM_PurchDetail_OrderNO 跑 Index Seek 就結束啦，而且還沒有資料輸出，Orz

改善前後 TSQL 語法成本比較，確實有改善

Statistics 時間比較

	修正前	修正後
CPU 時間	203ms	63ms
經過時間	349ms	287ms