sql-server - 内联表值 UDF 能否优于 SELECT 列列表中的等效标量 UDF？

Question

这个问题源自 SQLServer: Why avoid Table-Valued User Defined Functions? 。我开始在一些评论中提出问题，而对我评论的回复却偏离了主题。

<小时/>

这样您就不必阅读整个讨论:我从未听说过用户定义函数 (UDF) 很慢，或者应该避免。上面提到的问题中发布了一些链接，以说明它们很慢。我还是没明白，求个例子。贴了一个例子，性能差别很大。

我不可能是唯一一个没有意识到性能差异如此之大的人。我觉得这个事实应该分成一个新的问题和答案，以提高它被发现的机会。这就是“问题”。请先不要关闭，因为我想给回答者时间来发布答案。

当然，其他人也应该发布答案或示例。我特别感谢任何能帮助我理解为什么性能差异如此巨大的东西。

另请注意，我并不是在讨论在 WHERE 子句中使用 UDF。我知道这会如何阻止优化器完成其工作。当原始 UDF 是 SELECT 列列表的一部分时，我对性能差异特别感兴趣。

Answer 1

为了进行基准测试，我们创建一个包含 100 万行的表:

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，并运行它 1M 次:

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 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 的性能差异是显而易见的。