改善前 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改善前執行計畫
改善後執行計畫,Sort 操作子還在,有點礙眼,Orz
執行計畫比較
估計的子樹成本比較
TSQL 執行的 Statistics 是沒有甚麼改善,改善後執行計畫看起來還是很糟糕,但執行計畫成本改善不少
沒有留言:
張貼留言