SQL SERVER中用戶定義標量函數（scalar user defined function）的性能問題

用戶定義函數（UDF）分類 

     SQL SERVER中的用戶定義函數（User Defined Functions 簡稱UDF）分為標量函數（Scalar-Valued Function）和表值函數(Table-Valued Function)。其中表值函數又分為Inline table-valued functions和Multistatement table-valued functions。

    用戶定義函數（UDF）在 SQL Server 中發揮重要的作用。用戶定義函數可以用於執行複雜的邏輯，可以接受參數並返回數據。很多時候我們需要寫複雜的邏輯，不能使用單個查詢編寫。在這種情況下，用戶定義函數（UDF）發揮了重要的作用。關於用戶定義函數的優點，可以參考官方文檔“用戶定義函數”。如下所示：

用戶定義函數的優點

在 SQL Server 中使用用戶定義函數有以下優點：

• 允許模塊化程式設計。

  只需創建一次函數並將其存儲在資料庫中，以後便可以在程式中調用任意次。用戶定義函數可以獨立於程式源代碼進行修改。

• 執行速度更快。

  與存儲過程相似，Transact-SQL 用戶定義函數通過緩存計劃併在重覆執行時重用它來降低 Transact-SQL 代碼的編譯開銷。這意味著每次使用用戶定義函數時均無需重新解析和重新優化，從而縮短了執行時間。

  和用於計算任務、字元串操作和業務邏輯的 Transact-SQL 函數相比，CLR 函數具有顯著的性能優勢。Transact-SQL 函數更適用於數據訪問密集型邏輯。

• 減少網路流量。

  基於某種無法用單一標量的表達式表示的複雜約束來過濾數據的操作，可以表示為函數。然後，此函數便可以在 WHERE 子句中調用，以減少發送至客戶端的數字或行數。

UDF標量函數（Scalar-Valued Function）影響性能案例

   官方文檔說用戶定義函數（UDF）的執行速度更快，意思是性能非常好，如果你對此深信不疑的話，那麼我只能呵呵了，其實關於用戶定義函數，尤其是標量函數，需要合理使用。有些場景使用不當，則有可能造成性能問題。關於UDF的標量函數會引起性能的問題，下麵我們先看一個我構造的例子吧（AdventureWorks2014），我們需要查詢某個產品有多少訂單（其實也是優化過程中遇到，然後我在此處構造類似這樣的一個案例）

USE AdventureWorks2014;

GO

CREATE FUNCTION Sales.FetchProductOrderNum

(

    @ProuctID  INT

) RETURNS INT

BEGIN

    DECLARE @SaleOrderNum INT;

    SELECT @SaleOrderNum=COUNT(SalesOrderID)  FROM Sales.SalesOrderDetail 

    WHERE ProductID=@ProuctID

    GROUP BY ProductID;

    RETURN @SaleOrderNum;

END

GO

我們知道Sales.SalesOrderDetail表裡面ProductID=870的訂單數量有4688，而ProductID=897的訂單數量只有2條記錄。那麼執行下麵語句時，性能會有什麼差異呢？

SET STATISTICS TIME ON;

SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetail

WHERE ProductID=870

SELECT DISTINCT ProductID, Sales.FetchProductOrderNum(ProductID) FROM Sales.SalesOrderDetail

WHERE ProductID=897

SET STATISTICS TIME OFF;

為什麼會有這種情況，這是因為SQL語句裡面調用用戶定義標量函數（UDF Scalar Function），都是逐行調用用戶定義函數，這樣需要為每行去提取用戶定義函數的定義，然後去執行這些定義，從而導致了性能問題；更深層次的原因是因為函數採用了過程式的處理方法，而SQL Server查詢數據則是基於數據集合的，這樣在採用過程式的逐行處理時，SQL Server性能就會顯著降低。

那麼我來分析看看這兩個SQL的實際執行計劃：從下麵實際執行計劃，我們可以看到第一個SQL語句執行計劃從Index Seek 到Compute Scalar的數據流變粗了。這個表示第一個SQL語句的Index Seek返回的數據要多。

接下來，我們從Compute Scalar（進行一個標量計算並返回計算值）裡面可以看到Actual Number of Rows 的值為4688 和2 。

而Compute Scalar在此處就是調用標量函數，而標量函數調用總是需要資源開銷和時間的，當調用次數從2次變為4688次時，elapsed time 和CPU time當然會翻了好多倍。實際環境中，用戶定義標量函數的邏輯比上面簡單的案例更複雜，資源開銷更大，所以有時候你會看到性能差距非常懸殊的SQL案例，在工作中我就發現過這樣的情況，有些開發人員對自定義標量函數使用不當影響性能不甚瞭解。甚至是完全不知情。他們對此振振有詞：你看我SQL語句是一樣的，只是參數不同，效率差別這麼大。肯定是資料庫出現了阻塞或性能問題。要麼是伺服器的性能問題，反正我SQL是沒有問題的，你看這一條語句執行才一秒，換個參數就要一分多鐘，這不是你資料庫性能問題，那是什麼? 這樣的一個偽邏輯讓我很無語。（習慣性就讓我和資料庫、伺服器背了一個大黑鍋）。

   回到正題，上面兩個SQL語句的實際執行計劃的Cost比值為81%：19%；Compute Scalar（進行一個標量計算並返回計算值）的Number of Executions都是1次。但是實際的CPU time &elapsed time的比值比這個大了好多。另外第一個SQL的Compute Scalar的代價比值居然只有1%。為什麼會這樣呢？我們是不是很迷惑？

關於這個大家疑惑的地方，T-SQL User-Defined Functions: the good, the bad, and the ugly (part 1)裡面給了我們一個闡述，截取文章中兩段在此(翻譯如有不當，請參考原文)：

英文：

      However, you may not be aware that the “Actual Execution Plan” is a dirty rotten liar. Or maybe I should say that the terms “Actual Execution Plan” and “Estimated Execution Plan” are misleading. There is only one execution plan, it gets created when the queries are compiled, and then the queries are executed. The only difference between the “Actual” and the “Estimated” execution plan is that the estimated plan only tells you the estimates for how many rows flow between iterators and how often iterators are executed, and the actual plan adds the actual data for that. But no “actual operator cost” or “actual subtree cost” is added to the corresponding estimated values – and since those costs are the values that the percentages are based on, the percentages displayed in an actual execution plan are still based only on the estimates.

翻譯：

    然而，你可能不知道“實際執行計劃”其實是一個骯髒的爛騙子，或者我應該說“實際執行計劃”和“估計執行計劃”誤導你了。當查詢語句編譯後，只有一個實際的執行計劃。“實際執行計劃”與“估計執行計劃”的區別就在於“估計執行計劃”只告訴你估計了有多少行流向迭代和迭代器執行頻率，而實際執行計劃將實際數據應用進來。但是“實際操作成本”或“實際子樹成本”並沒有添加到“實際執行計劃”的估計值裡面， 因為這些代價都是基於百分比的值，在實際執行計劃中顯示的百分比仍然基於只估計數。

英文：

    But note that, again, the execution plan is lying. First, it implies that the UDF is invoked only once, which is not the case. Second, look at the cost. You may think that the 0% is the effect of rounding down, since a single execution of the function costs so little in relation to the cost of accessing and aggregating 100,000 rows. But if you check the properties of the iterators of the plan for the function, you’ll see that all operator and subtree costs are actually estimated to be exactly 0. This lie is maybe the worst of all – because it’s not just the plan lying to us, it is SQL Server lying to itself. This cost estimate of 0 is actually used by the query optimizer, so all plans it produces are based on the assumption that executing the function is free. As a result, the optimizer will not even consider optimizations it might use if it knew how costly calling a scalar UDF actually is.

翻譯：

但是需要再次註意，執行計劃在欺騙你，首先，它意味著只調用了UDF一次，其實不是這樣。其次，從成本（Cost）來看，你可能會認為0%是向下舍入影響，因為單次執行函數的開銷如此之小，以至於執行100,000次的成本也很小。但如果你檢查執行計劃的功能迭代器的屬性，你會發現所有的操作代價和子樹代價實際的估計為0，這是一個最糟糕的謊言。 因為它可能不只是為了欺騙我們，而是SQL SERVER為了欺騙它自己。實際上是查詢優化器認為調用函數的成本為0，因此它生成的所有執行計劃都是基於調用UDF是免費的。其結果是即使調用標量UDF的代價非常昂貴，查詢優化器也不會考慮優化它。

如何優化UDF標量函數（Scalar-Valued Function）

如何優化上面SQL語句呢？從原理上來講就是不用用戶定義函數或減少調用次數。 其實我在實際應用中，減少調用次數一般通過下麵方法優化：

1：減少用戶定義標量函數調用次數（子查詢）

SET STATISTICS TIME ON;

SELECT ProductID, Sales.FetchProductOrderNum(ProductID)

FROM(

    SELECT DISTINCT ProductID FROM Sales.SalesOrderDetail

WHERE ProductID=870) T

SET STATISTICS TIME OFF;

2：減少用戶定義標量函數調用次數（臨時表）

SET STATISTICS TIME ON;

SELECT DISTINCT ProductID INTO #SalesOrderDetail FROM Sales.SalesOrderDetail

WHERE ProductID=870;

SELECT ProductID, Sales.FetchProductOrderNum(ProductID)

FROM #SalesOrderDetail

SET STATISTICS TIME OFF;

為什麼要用臨時表呢？不是子查詢就可以解決問題嗎？問題是實際應用當中，有些邏輯複雜的地方需要藉助臨時表解決，有時候子查詢反而不是一個好的解決方法。

另外，我們來看看Performance Considerations of User-Defined Functions in SQL Server 2012這篇文章中，測試UDF的性能案例，本想單獨翻譯這篇文章，不過結合這篇文章，在此實驗驗證也是個不錯的選擇。下麵案例全部來自這篇博客。我們先準備測試環境:

CREATE FUNCTION dbo.Triple(@Input INT) 

       RETURNS INT 

AS 

BEGIN; 

  DECLARE @Result INT; 

  SET @Result = @Input * 3; 

  RETURN @Result; 

END; 

GO 

CREATE TABLE dbo.LargeTable 

  (KeyVal INT NOT NULL PRIMARY KEY, 

   DataVal INT NOT NULL CHECK (DataVal BETWEEN 1 AND 10) 

  );

WITH Digits 

AS (SELECT d FROM (VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9)) AS d(d)) 

INSERT INTO dbo.LargeTable (KeyVal, DataVal) 

SELECT 1000000 * sm.d 

     + 100000 * ht.d + 10000 * tt.d + 1000 * st.d 

     + 100 * h.d + 10 * t.d + s.d + 1, 

       10 * RAND(CHECKSUM(NEWID())) + 1 

FROM   Digits AS s,  Digits AS t,  Digits AS h, 

       Digits AS st, Digits AS tt, Digits AS ht, 

       Digits AS sm;

GO

CREATE INDEX NCL_LargeTable_DataVal ON dbo.LargeTable (DataVal);

GO

SET STATISTICS TIME ON; 

SELECT MAX(dbo.Triple(DataVal)) AS MaxTriple 

FROM dbo.LargeTable AS d; 

SELECT MAX(3 * DataVal) AS MaxTriple 

FROM dbo.LargeTable AS d; 

SET STATISTICS TIME OFF;

如上所示，用戶定義的標量函數dbo.Triple，測試用的的一個表dbo.LargeTable ，以及構造了1000000行數據。從下麵我們可以看到用戶定義標量函數性能確實很糟糕。

下麵測試4中寫法的性能。相信這個簡單的腳本，大家都能看懂，在此不做過多描述、說明：

DBCC DROPCLEANBUFFERS WITH NO_INFOMSGS;