星期一, 7月 30, 2018

[SQL] 避免 Group By 跨資料表欄位

[SQL] 限制資料筆數 續篇,解決資料大量輸出後,發現有 Group By 跨資料表欄位情況,白話說就是 JOIN Table 之後才對多個 Table 欄位進行彙總

改善前 TSQL 如下
SELECT
  .................
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 
    (
      SELECT
        KK.ORDER_NO ,
        KK.PID ,
        KK.delivered ,
        O.WK_NAME ,
        MAX(KK.CDATE) AS CDATE ,
        MAX(KK.BOOKER) AS BOOKER ,
        MAX(KK.TZ) AS TZ ,
        MAX(KK.PRIORITY) AS PRIORITY ,
      FROM MTDELIVERBOOK AS KK
        JOIN ORGANIZATION AS O ON KK.HANDLEDEP = O.WK_ID
      GROUP BY KK.ORDER_NO ,KK.PID,KK.DELIVERED,O.WK_NAME
    ) AS K ON K.ORDER_NO = F.ORDER_NO
             AND K.PID = F.PID
  JOIN 
    (
      .................
    ) AS s ON s.order_no = F.order_no
             AND s.hx_status <> '本單暫時保留'
             AND S.DONE = ''
  LEFT JOIN 
    (
        .................
    ) 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  
把 TSQL 語法改為 GROUP BY 彙總後再 JOIN Table
SELECT
    T.* ,
    O.WK_Name
FROM
  (
    SELECT
      KK.ORDER_NO ,
      KK.PID ,
      KK.delivered ,
      KK.handledep ,
      MAX(KK.CDATE) AS CDATE ,
      MAX(KK.BOOKER) AS BOOKER ,
      MAX(KK.TZ) AS TZ ,
      MAX(KK.PRIORITY) AS PRIORITY
    FROM MTDELIVERBOOK AS KK
    GROUP BY KK.ORDER_NO ,KK.PID,KK.DELIVERED,KK.handledep  
  ) AS T
  JOIN Organization AS O ON T.handledep = O.WK_ID
改善前執行計畫

[SQL] 避免 Group By 多資料表欄位-1

改善後執行計畫,Sort 操作子還在,有點礙眼,Orz

[SQL] 避免 Group By 多資料表欄位-2

執行計畫比較

[SQL] 避免 Group By 多資料表欄位-3

估計的子樹成本比較

[SQL] 避免 Group By 多資料表欄位-4

TSQL 執行的 Statistics 是沒有甚麼改善,改善後執行計畫看起來還是很糟糕,但執行計畫成本改善不少

沒有留言:

張貼留言