[SQL] JOIN ON 條件的重要性

進行 TOP 10 語法時發現高成本的語法，此"物料語法"是配合生管需求，針對物料去找出最近一次採購記錄、最近一次派工記錄、最近完工日期等資訊，總共 JOIN 18 個 Table，而這次的瓶頸在於要找出，"該產品物料兩年內最新的完工資料（T17 語法）"上

下圖是縮減版的"物料語法"，"T17 語法" 為這次的瓶頸，Order_NO 為派工單號，ModelCode 為物料編號

SELECT 
    Col........................
FROM AS MtData
    LEFT JOIN
        (
            SELECT 
                SY.ORDER_NO,
                SY.modelCODE,
                SY.finish_day,
                SY.QTY
            FROM PMSETORDER AS SY
                JOIN
                    (
                        SELECT 
                            MAX(order_no) AS order_no
                        FROM pmsetorder
                        WHERE priority ='P' 
                            AND OPT_NO ='' 
                            AND DONE ='Y' 
                            AND DATE >= CAST(CONVERT(char(10),DATEADD(YY,-2,getdate()),120) AS DATETIME)
                        GROUP BY ModelCODE
                    ) AS AX ON AX.order_no = SY.ORDER_NO 
        ) AS T17 ON M.MtCode = T17.ModelCode

從 T17 語法執行計畫路徑可以觀察到 PMsetOrder Table 抓出很誇張的資料量，該 Table 全部的資料量也才 130,000 筆左右，但卻撈出 1,815,541 筆資料，整個大出 14 倍左右，Orz ~~

T17 語法的疑點：

1. 針對 ModelCode 進行 GROUP BY 後，ModelCode 欄位沒有被列為 JOIN ON 條件之一
2. Order_NO 彙總後欄位是唯一的 JOIN ON 條件

下列為修改後的 T17 語法，把 ModelCode 也加入 JOIN ON 條件

SELECT 
    SY.ORDER_NO,
    SY.modelCODE,
    SY.finish_day,
    SY.QTY
FROM PMSETORDER AS SY
    JOIN
        (
            SELECT 
               ModelCode,
               MAX(order_no) AS order_no
            FROM pmsetorder
            WHERE priority ='P' 
                AND OPT_NO ='' 
                AND DONE ='Y' 
                AND DATE >= CAST(CONVERT(char(10),DATEADD(YY,-2,getdate()),120) AS DATETIME)
            GROUP BY ModelCODE
      ) AS AX ON AX.order_no = SY.ORDER_NO 
                   AND AX.ModelCode = SY.ModelCode

重新執行該物料語法，下圖為 T17 語法執行計畫路徑

下圖為 T17 語法文字執行計畫，可以明顯看出資料量減少後執行計畫變化

下表為 Logical Read 和 CPU 時間比較表

	PMSetOrder Logical Read	整體 CPU Time（ms）
原語法	392,610	6,257
修改後	7,175	888

JOIN ON 條件跟 WHERE 條件一樣重要