[SQL] 避免對欄位進行數學運算

在官方文章 Troubleshoot high-CPU-usage issues in SQL Server 的 Step 6: Investigate and resolve SARGability issues 段落是討論 SARG，記錄文章範例並延伸

測試環境建置

Sales.SaleOrderDetail.UnitPrice 預設並沒有 Index，透過下列語法先把 Index 建立出來並實際執行 TSQL 語法來取得執行計畫

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 修正 - 隱式轉換

從 Index Scan Operator 內發現有隱示轉換發生，原來 UnitPrice 資料型態是 money，0.1 數字會被視為 numeric，以資料型態轉換優先權來看，UnitPrice 會被轉換為 numeric 來進行運算，為避免模糊焦點，之後數字 0.1 會明確轉型為 money

-- 原語法
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

修改前後執行計畫比較

統計資訊上排序 (相異排序) 造成的 Worktable 就消失

• 延伸閱讀
• [SQL] STRING_SPLIT 執行計畫
• [SQL] 避免 Group By 跨資料表欄位

數學運算

經過上述修正後的 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 語法執行計畫比較

• 延伸閱讀
• [SQL] 避免欄位比較
• [SQL] 避免欄位相加進行比對