SELECT SO_Order.fdate, SO_Order.fsn FROM so_order INNER JOIN so_orderitem ON CHARINDEX(so_Orderitem.fid, SO_Order.fid) >= 1 WHERE so_order.FOrderDate = '2015-09-06';
so_order.fid 為逗號分隔字串(如 'A001,B002')。so_orderitem 41 萬筆,原查詢 12 秒,執行計畫顯示 Index Scan。
使用 CTE 將 fid 拆成兩列(最多兩段),再與 so_orderitem 以等值連接,利用索引查尋。
;WITH x0 AS (
SELECT fdate, fsn,
LEFT(fid, CASE WHEN CHARINDEX(',', fid, 1) = 0 THEN 0 ELSE CHARINDEX(',', fid, 1) - 1 END) AS fid
FROM so_order
WHERE FOrderDate = '2015-09-06'
UNION ALL
SELECT fdate, fsn,
RIGHT(fid, LEN(fid) - CHARINDEX(',', fid, 1)) AS fid
FROM so_order
WHERE FOrderDate = '2015-09-06'
)
SELECT SO_Order.fdate, SO_Order.fsn
FROM x0 so_order
INNER JOIN so_orderitem ON so_Orderitem.fid = SO_Order.fid;
so_orderitem 從 Index Scan 變為 Index Seek。
STRING_SPLIT (SQL 2016+) 或自訂分割函數搭配 CROSS APPLY。