[SQL] 避免在 Select 中使用 Scalar Function

避免在 Select 中使用 Scalar Function，原因在於 Query Optimizer 無法正確解析，從執行計畫、Statistics IO 內無法看出相關資源的實際使用情況，必須透過 SQL Profile 來確認實際的資源使用

Microsoft SQL Server Performance Tuning 效能調校 P551 說明

使用函數另一個要注意地方是執行計畫不會累加該函數呼叫的次數，進而算出該函數的資源比重。所以透過執行計畫呈現呼叫自定函數的 T-SQL 語法，自定函數的執行計畫耗用資源相對於整句語法可能比例很小，但其實像這樣被呼叫多次，累積的結果才是最耗資源的地方

利用計算採購單總金額來驗證

建立 getTotalPrice() 使用者自訂函數，來統計採購單內容各品項總金額 (單價 X 數量)

CREATE FUNCTION getTotalPrice(@PurNO char(11)) 
RETURNS money
BEGIN
 
  DECLARE @TotalPrice as money 

  SELECT
    @TotalPrice = SUM(ROUND(PurQty * Price , 0))
  FROM PurchDetail
  WHERE PurNO = @PurNO

  RETURN @TotalPrice
END
GO

實際執行

SET STATISTICS IO , TIME ON

SELECT 
  PurNO , 
  dbo.getTotalPrice(PurNO) AS TotalPrice
FROM Purch
WHERE PurDate BETWEEN '20180101' AND '20180630'

執行計畫中，getTotalPrice() 只被執行一次，為下圖中的 [計算存量 operator]，且沒有看見 PurchDaetail Table 出現在執行計劃內

從 operator 屬性資訊來確認 Expr1002 是 getTotalPrice()

Statistics 相關資訊
• Purch Table Logical Reade = 7
• CPU Time = 93 ms

利用 SQL Profile 的 Turning 範本來觀察 getTotalPrice() 實際運作

指定資料行，需要 TextData、Duration、Reads 這三個欄位資訊

指定特定 DB，可以較明確收集到要的資訊

執行 TSQL 語法，並從 SQL Profile 側錄結果就可以發現，getTotalPrice() 被執行多次，在執行計畫中只有一次，且 Duration 和 Reads 都明顯高於 Statistics IO 的數據

使用資源比較

	Statistics	SQL Profile
Logical Read	7	7,410
CPU Time	93ms	225ms

利用 SentryOne Plan Explorer 來觀察，可以發現 operator 上有 Warnings，明確指出使用自訂函數的效能副作用

• 延伸閱讀
• [SQL] 使用者自訂函數
• 參考資料
• Microsoft SQL Server Performance Tuning 效能調校
• T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)、(part 2)
• 效能調校-案例探討
• IT 邦討論