測試環境建置
Sales.SaleOrderDetail.UnitPrice 預設並沒有 Index,透過下列語法先把 Index 建立出來並實際執行 TSQL 語法來取得執行計畫
從 Index Scan Operator 內發現有隱示轉換發生,原來 UnitPrice 資料型態是 money,0.1 數字會被視為 numeric,以資料型態轉換優先權來看,UnitPrice 會被轉換為 numeric 來進行運算,為避免模糊焦點,之後數字 0.1 會明確轉型為 money
修改前後執行計畫比較
統計資訊上排序 (相異排序) 造成的 Worktable 就消失
USE AdventureWorks2022
GO
DROP INDEX IF EXISTS IX_SalesOrderDetail_UnitPrice ON [Sales].[SalesOrderDetail]
CREATE INDEX IX_SalesOrderDetail_UnitPrice ON [Sales].[SalesOrderDetail] (UnitPrice)
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300
TSQL 修正 - 隱式轉換
-- 原語法
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300
-- 修正後
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * CAST(0.10 as money) [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * CAST(0.10 as money) > 300
- 延伸閱讀
- [SQL] 避免隱含式轉換
TSQL 修正 - 排序 (相異排序)
從排序 (相異排序) operator 中觀察,DISTINCT 是針對 SalesOrderID、UnitPrice、[10% Commission] 這三個欄位來排序並進行重覆篩選,但 [10% Commission] 是計算欄位,不用包含在 DISTINCT 才是
-- 原語法
SELECT DISTINCT SalesOrderID, UnitPrice, UnitPrice * 0.10 [10% Commission]
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300
-- 修正後
SELECT
T.* ,
UnitPrice * 0.10 [10% Commission]
FROM
(
SELECT DISTINCT SalesOrderID, UnitPrice
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * 0.10 > 300
) AS T
修改前後執行計畫比較
數學運算
經過上述修正後的 TSQL 語法
SELECT
T.* ,
UnitPrice * CAST(0.10 as money) [10% Commission]
FROM
(
SELECT DISTINCT SalesOrderID, UnitPrice
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice * CAST(0.10 as money) > 300
) AS T
實際執行可以得到下圖執行計畫,因為對 UnitPrice 進行數學運算,導致無法吃到 Index Seek 且從 Plan Explorer 內觀察的話,還有錯誤訊息
修正 TSQL 語法,不要針對欄位進行數學運算
SELECT
T.* ,
UnitPrice * CAST(0.10 as money) [10% Commission]
FROM
(
SELECT DISTINCT SalesOrderID, UnitPrice
FROM [Sales].[SalesOrderDetail]
WHERE UnitPrice > 3000 -- 不針對欄位進行數學運算
) AS T
改善前後 TSQL 語法執行計畫比較