標量 UDF 與 TVF 的效率
我正在嘗試為我的公司優化匯總程式碼,但遇到了一個非常特殊的問題。我將許多標量函式轉換為 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)
查詢提示測試您重寫的查詢,以查看所選串列計劃與原始計劃的比較情況。並行計劃並不總是更好(儘管只有在優化器看來成本較低時才會選擇它們)。您的案例成本相對較低,因此優化器認為不值得探索大量備選方案。在某些情況下,考慮串列和並行計劃所花費的時間會對最終計劃的質量產生反作用。
如果這有點含糊,我深表歉意,但匿名計劃確實很難具體化。在所有條件相同的情況下,內聯函式目前的性能將優於標量函式。可悲的是,所有事情很少是平等的。