[SQL] JOIN ON 條件的重要性

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

下圖是縮減版的"物料語法"，"T17 語法" 為這次的瓶頸

SELECT 
    ........................
FROM AS 物料
    LEFT JOIN
        (
            SELECT 
                SY.派工單號 ,
                SY.物料識別碼 ,
                SY.表定完成日 ,
                SY.派工數量
            FROM 派工單 AS SY
                JOIN
                    (
                        SELECT 
                            MAX(派工單號) AS 派工單號
                        FROM 派工單
                        WHERE 優先權 = 'P' 
                            AND 選擇性 = '' 
                            AND 是否結案 = 'Y' 
                            AND 派工日期 >= CAST(CONVERT(char(10),DATEADD(YY,-2,getdate()),120) AS DATETIME)
                        GROUP BY 物料識別碼
                    ) AS AX ON AX.派工單號 = SY.派工單號
        ) AS T17 ON M.物料識別碼 = T17.物料識別碼

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

T17 語法的疑點：

1. 針對 [物料識別碼] 進行 GROUP BY 後，[物料識別碼] 欄位沒有被列為 JOIN ON 條件之一
2. [派工單號] 彙總後欄位是唯一的 JOIN ON 條件

下列為修改後的 T17 語法，把 [物料識別碼] 也加入 JOIN ON 條件

SELECT 
    SY.派工單號 ,
    SY.物料識別碼 ,
    SY.表定完成日 ,
    SY.派工數量
FROM 派工單 AS SY
    JOIN
        (
            SELECT 
               物料識別碼 ,
               MAX(派工單號) AS 派工單號
            FROM 派工單
            WHERE 優先權 ='P' 
                AND 選擇性 = ''
                AND 是否結案 ='Y' 
                AND 派工日期 >= CAST(CONVERT(char(10),DATEADD(YY,-2,getdate()),120) AS DATETIME)
            GROUP BY 物料識別碼
      ) AS AX ON AX.派工單號 = SY.派工單號
                   AND AX.物料識別碼 = SY.物料識別碼 -- 加上該條件

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

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

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

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

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