星期日, 9月 09, 2018

[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 的數據
使用資源比較

StatisticsSQL Profile
Logical Read77,410
CPU Time93ms225ms

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

沒有留言:

張貼留言