問題描述

這個問題源于SQLServer:為什么避免使用表- 有價值的用戶定義函數?.我開始在一些評論中提出問題，對我評論的回復偏離了主題.

This question grew out of SQLServer: Why avoid Table-Valued User Defined Functions?. I began asking questions in some of the comments, and the replies to my comments moved off topic.

這樣您就不必閱讀整個討論:我從未聽說過用戶定義函數 (UDF) 很慢，或者應該避免使用.上面引用的問題中發布了一些鏈接，以說明它們很慢.我還是沒明白，求個例子.貼了個例子，性能差別很大.

So that you don't have to read the entire discussion: I had never heard it said that user defined functions (UDF) were slow, or to be avoided. Some links were posted in the question referenced above to illustrate that they were slow. I still didn't get it, and asked for an example. An example was posted, and the performance difference was huge.

我不可能是唯一沒有意識到可能存在如此大的性能差異的人.我覺得這個事實應該分成一個新的問題和答案，以提高被發現的機會.這就是問題".請不要關閉，因為我想給回答者時間來發布答案.

I can't be the only person who did not realize there could be such a large performance difference. I felt this fact should be separated into a new question and answer, to improve its chances of being found. This here is the "question". Please don't close yet, as I'd like to give the answerer time to post the answer.

當然，其他人也應該發布答案或示例.我特別感謝任何能幫助我理解為什么性能差異如此巨大的東西.

Of course, others should also post answers or examples, as well. I'd especially appreciate anything that would help me understand why the performance difference is so huge.

另請注意，我不是在談論在 WHERE 子句中使用 UDF.我知道這會如何阻止優化器完成其工作.當原始 UDF 是 SELECT 列列表的一部分時，我對性能差異特別感興趣.

Note also that I'm not talking about the use of a UDF in a WHERE clause. I'm aware of how this can prevent the optimizer from doing its job. I'm specifically interested in differences in performance when the original UDF was part of the SELECT column list.

推薦答案

為了進行基準測試，讓我們創建一個 100 萬行的表:

For benchmarking let's create a table with 1M rows:

CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY)
GO
DECLARE @i INT;
SET @i = 1;
INSERT INTO dbo.Numbers(n) SELECT 1;
WHILE @i<1024000 BEGIN
  INSERT INTO dbo.Numbers(n)
    SELECT n + @i FROM dbo.Numbers;
  SET @i = @i * 2;
END;
GO

運行簡單的內聯添加:

SELECT COUNT(*) FROM(
SELECT n,n+1 AS ValuePlusOne
FROM  dbo.Numbers
) AS t WHERE ValuePlusOne>0

   CPU time = 15 ms, elapsed time = 122 ms.

(1 row(s) affected)
Table 'Numbers'. Scan count 1, logical reads 3521, physical reads 3, read-ahead reads 3498, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 406 ms,  elapsed time = 951 ms.

創建一個標量 UDF，將一個整數加一，并運行 100 萬次:

Create a scalar UDF that just adds one to an integer, and run it 1M times:

CREATE FUNCTION dbo.[AddOne] 
(
        @value int
)
RETURNS int
AS
BEGIN
        DECLARE @Result int
        SELECT @Result = @value + 1
        RETURN @Result
END
GO

SELECT COUNT(*) FROM(
SELECT n,dbo.AddOne(n) AS ValuePlusOne
FROM  dbo.Numbers
) AS t WHERE ValuePlusOne>0

   CPU time = 15 ms, elapsed time = 122 ms.

(1 row(s) affected)
Table 'Numbers'. Scan count 1, logical reads 3521, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 108313 ms,  elapsed time = 295072 ms.

創建一個內聯 UDF，它和添加一樣快，并運行 100 萬次:

Create an inline UDF, which is just as fast as just adding, and run that 1M times:

CREATE FUNCTION dbo.[AddOneInline] 
(
        @value int
)
RETURNS TABLE
AS
RETURN(SELECT @value + 1 AS ValuePlusOne)
GO

SELECT COUNT(*) FROM(
SELECT ValuePlusOne
FROM  dbo.Numbers
CROSS APPLY dbo.[AddOneInline](n)
) AS t WHERE ValuePlusOne>0

SQL Server parse and compile time: 
   CPU time = 0 ms, elapsed time = 35 ms.

(1 row(s) affected)
Table 'Numbers'. Scan count 1, logical reads 3521, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 391 ms,  elapsed time = 403 ms.

標量 UDF 與內聯 UDF 的性能差異很明顯.

The difference in performance of a scalar UDF vs. an inline one is obvious.

這篇關于內聯表值 UDF 能否勝過 SELECT 列列表中的等效標量 UDF?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！