[SQL] 強制指定索引

Top 10 Turning 時，發現同事這段語法非常耗能，調整過索引和了解商業邏輯後去改 T-SQL 語法，最後還是很醜陋的語法，醜陋歸醜陋，但效能有提升就好 ~~

縮減後的 Turning 語法（只列出文章要記錄部分）， 重點在 POItem Table 身上

SELECT 
    DA.custno, 
    sum(DA.price * DA.LEFT_QTY) AS ALLMONEY
FROM
    (
        SELECT 
            bA.custno,
            bA.po,
            iA.model_no,
            iA.opt_no,
            iA.PRICE,
            iA.left_qty,
            iA.asm_memo,
            iA.FINISH_DAY
        FROM pobasic as bA
            JOIN poitem as iA ON bA.odno=iA.odno
            JOIN pmfinishdate as MA ON MA.order_no=IA.ordeR_no AND MA.pid='A1' AND MA.doneqty>0
        WHERE bA.custno <>'09084'
            AND IA.left_qty > 0
    ) AS DA
WHERE DA.finish_day < CAST(CONVERT(char(10),GETDATE(),112) AS datetime)
GROUP BY DA.custno

調整過後，發現 POItem Table Scan Count（掃描計數）似乎有點詭異

資料表 'poitem'。掃描計數 3452，邏輯讀取 27600，實體讀取 1791

再觀察執行計畫就更是一頭霧水啦，原因是從 POItem 內抓取 5 筆資料，竟然就佔整體執行計畫成本的 38%，這 5 筆資料還真的是蠻貴的

從執行計畫 operator 中可以觀察到 Query Optimizer 是利用 PK_Poitem 來搜尋資料且 T-SQL 語法中只有一處用到 POItem Table，了解語法商業邏輯後，強制使用索引 IX_POItem_LeftQty 來搜尋資料並觀察 Logical Read

JOIN POItem AS iA WITH(INDEX(IX_POITEM_LEFTQTY)) ON bA.odno=iA.odno

資料表 'poitem'。掃描計數 1，邏輯讀取 2827，實體讀取 2

整段語法內的 Table Logical Read 和 CPU Time 都有些許下降，只列出重點 Table POItem 使用不同索引的比較表

使用索引	scan count	logical read	physical read
PK_POItem	3,452	27,600	1,791
IX_POItem_LeftQty	1	2,827	2

第一次遇上 Query Optimizer 選錯索引的情況，再觀察統計資訊是否老舊，發現也沒有老舊問題，只能說這語法太醜陋誤導 Query Optimize，而強制 Query Optimizer 使用特定索引，要定期觀察該語法，畢竟資料會隨著時間而增加

延伸閱讀
• [SQL] View 和 WHERE 條件