Sql-Server

標量 UDF 與 TVF 的效率

  • July 31, 2018

我正在嘗試為我的公司優化匯總程式碼,但遇到了一個非常特殊的問題。我將許多標量函式轉換為 TVF,它們似乎都比原來的執行得更快,這很棒。但是,在呼叫它們的查詢中,它們的執行速度比原來慢得多。這是我的更新的基本大綱:

SELECT col1, ..., colx,
   (CASE WHEN x <= 0 OR y <= 0 OR z <= 0 OR z = x 
         THEN output
       WHEN valX <= 0 
         THEN output
       WHEN minimum.min < 1.0 THEN 1.0
       ELSE minimum.min
       END) AS Q,
FROM Tbl1...tblx (series of inner joins)
CROSS APPLY dbo.inlinemin(val1, val2) AS minimum

這是原始的基本輪廓:

SELECT col1, ..., colx,
   (CASE WHEN x <= 0 OR y <= 0 OR z <= 0 OR z = x 
         THEN output
       WHEN valX <= 0 
         THEN output
       ELSE maximum(minimum(val1,val2),1.0)
       END) AS Q,
FROM Tbl1...tblx (series of inner joins)

數字或多或少相同,邏輯也是如此。唯一的區別是我的函式“inlineMin”是一個 TVF,而不是“最大”和“最小”原始標量函式。這些函式非常簡單,只返回兩個傳遞參數之間的最大值或最小值。甚至執行計劃也差不多。從合併連接到雜湊匹配有一個變化,但是,這種差異的成本是最小的,並且無法解釋經過時間和 cpu 時間的急劇變化。

當我在匯總查詢之外執行函式時,我的函式比大型數據集的原始函式更快。考慮到 TVF 與標量 UDF 相比的工作方式,這是有道理的。但是,當我在查詢中呼叫它們時,我的更新版本執行速度大約慢了 6 倍。交叉應用(似乎)不是問題,因為離開交叉應用並簡單地使用舊功能

SELECT ...
   ELSE maximum(minimum(val1,val2),1.0)
   END) AS Q,
FROM Tbl1...tblx (series of inner joins)
CROSS APPLY inlinemin(val1, val2) AS NotUsedHere

與原始程式碼大致一樣高效。只有當我在 select 中包含我的函式的輸出時,查詢才會顯著變慢。

據我了解,該函式被呼叫並在交叉應用處執行,這意味著即使它不在選擇中,它也應該計算一個值,那麼為什麼不將它包含在選擇中會更快呢?此外,如果上述情況為假,為什麼我的函式本身會更快,但在查詢中使用時執行速度卻明顯變慢?

編輯:

這是我為替換原始而編寫的內聯 TVF

CREATE FUNCTION [dbo].[InlineMin](@val1 FLOAT, @val2 FLOAT)
RETURNS TABLE WITH SCHEMABINDING 
AS
RETURN
   SELECT minVal =
   CASE    WHEN @val1 < @val2
           THEN @val1
   ELSE
           ISNULL(@val2,@val1)
END

這是我重寫的匿名查詢計劃: https ://www.brentozar.com/pastetheplan/?id=ryKR_Q0Em

以及原始的匿名查詢計劃: https ://www.brentozar.com/pastetheplan/?id=SJsS1-A4X

據我了解,該函式被呼叫並在交叉應用處執行,這意味著即使它不在選擇中,它也應該計算一個值,那麼為什麼不將它包含在選擇中會更快呢?

優化器非常擅長刪除計算最終結果中不需要的表達式的子樹(頂級投影)。當您從選擇列表中刪除該值時,計算該值所需的工作根本沒有完成。

此外,如果上述情況為假,為什麼我的函式本身會更快,但在查詢中使用時執行速度卻明顯變慢?

這很難從匿名計劃中詳細評估。然而,刪除標量 T-SQL 函式允許優化器考慮並行計劃。您可能希望使用OPTION (MAXDOP 1)查詢提示測試您重寫的查詢,以查看所選串列計劃與原始計劃的比較情況。

並行計劃並不總是更好(儘管只有在優化器看來成本較低時才會選擇它們)。您的案例成本相對較低,因此優化器認為不值得探索大量備選方案。在某些情況下,考慮串列和並行計劃所花費的時間會對最終計劃的質量產生反作用。

如果這有點含糊,我深表歉意,但匿名計劃確實很難具體化。在所有條件相同的情況下,內聯函式目前的性能將優於標量函式。可悲的是,所有事情很少是平等的。

引用自:https://dba.stackexchange.com/questions/213611