提问人：John Saunders 提问时间：7/5/2009 最后编辑：CommunityJohn Saunders 更新时间：6/13/2013 访问量：4503
内联表值 UDF 的性能能否优于 SELECT 列列表中的等效标量 UDF？
Can an inline table-valued UDF outperform the equivalent scalar UDF in a SELECT column list?
问：
这个问题源于 SQLServer：为什么要避免表值用户定义函数？。我开始在一些评论中提问，对我的评论的回复偏离了主题。
这样你就不必阅读整个讨论：我从来没有听说过用户定义函数（UDF）很慢，或者应该避免。在上面提到的问题中发布了一些链接，以说明它们很慢。我还是不明白，并要求举个例子。发布了一个示例，性能差异很大。
我不是唯一一个没有意识到会有如此大的性能差异的人。我觉得这个事实应该被分成一个新的问题和答案，以提高它被发现的机会。这是“问题”。请不要关闭，因为我想给回答者时间发布答案。
当然，其他人也应该发布答案或示例。我特别感激任何能帮助我理解为什么性能差异如此之大的东西。
另请注意，我不是在谈论在 WHERE 子句中使用 UDF。我知道这会如何阻止优化器完成其工作。我对原始 UDF 是 SELECT 列列表的一部分时的性能差异特别感兴趣。
答：
5赞
A-K
7/5/2009
#1
对于基准测试，让我们创建一个包含 1M 行的表：
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，只需将 1 添加到整数中，并运行 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，它与添加一样快，并运行 1M 次：
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 的性能差异是显而易见的。
评论
0赞
IamIC
3/2/2011
这个测试具有误导性。看一下执行计划，无疑会发现内联 UDF 没有被调用 1 M 次，而是实际上已经优化到外部查询中。相比之下，标量函数不能如此优化，因为它是一个黑匣子。像其他任何事情一样，每个都有其用途，如果使用得当，可以满足手头的目的。
0赞
A-K
3/7/2011
@IanC：你是对的，内联调用被扁平化到查询中，标量函数每行调用一次。什么是误导？我所关心和谈论的只是性能，我关于它的陈述是正确的，不是吗？
0赞
IamIC
3/8/2011
我的意思是，不了解各种 UDF 之间差异的人可能会从您的结论中得出与您想要的结论不同的结论（即正确的结论）。你说的话在技术上并没有什么不准确的。国际海事组织（IMO）帮助他人得出正确的结论很重要:)
1赞
Martin Smith
12/28/2011
由于标量 UDF 不进行数据访问，因此您应该创建它。在这种情况下，我还没有测试它是否改变了任何事情。WITH SCHEMABINDING
2赞
ZXX
8/3/2010
#2
好吧，现在你打开了一个棘手的话题：-）我认为我们需要更现实的例子，避免陷阱游戏。看起来太做作的例子总是让我怀疑。因此，我稍微重新排列了查询，直接标量 UDF 优于查询。不要相信它 - 尝试一下 - 这是在 2k8 Server Std 下的开发箱上的 SQL 2k8 上。
到目前为止，我们所了解到的是，在 WHERE 子句中使用计算列和等效项是不好的。该查询在 WHERE 子句中使用标量函数，同时假装它在 select 中。
SELECT COUNT(*) FROM(
SELECT n as X,n+1 AS ValuePlusOne
FROM dbo.Numbers
) AS t WHERE X>0
表“数字”。扫描计数 1，逻辑读取 3521，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。
SQL Server 执行时间： CPU 时间 = 234 毫秒，运行时间 = 228 毫秒。
SELECT COUNT(*) FROM(
SELECT n as X ,dbo.AddOne(n) AS ValuePlusOne
FROM dbo.Numbers
) AS t WHERE X>0
表“数字”。扫描计数 1，逻辑读取 3521，物理读取 0，预读读取 0，lob 逻辑读取 0，lob 物理读取 0，lob 预读读取 0。
SQL Server 执行时间： CPU 时间 = 202 毫秒，运行时间 = 215 毫秒。
那么，既然我们已经解决了这个问题，那么一些真实的信息和现实的用例怎么样？
我将提供 2 个用于辩论：-）但请记住，不要做作的陷阱。TVF 和标量 UDF 只是调用它来以方便的方式获取值，然后在查询中用作值或联接 - 没有人在计算任何东西。有人可以构建一个表格或说明病理数据必须如何才能看到 LCID1 和 LCID2 之间的性能差异吗？
CREATE FUNCTION [PublishingCulture] ( @XLanguage int,
@XLocale int
) RETURNS TABLE
AS
RETURN
(
select TOP 1 * from [Culture] C
where ((C.XLang = @XLanguage and C.XLoc = @XLocale)
or (C.XLang = @XLanguage and C.XLoc = 0)
or (C.XLang = 0 and C.XLoc = @XLocale)
or (C.XLang = 0 and C.XLoc = 0))
)
CREATE FUNCTION [MyLCID1] ( @XLanguage int,
@XLocale int )
RETURNS TABLE
AS
RETURN ( SELECT LCID from dbo.PublishingCulture(@XLanguage, @XLocale) )
CREATE FUNCTION [MyLCID2] ( @XLanguage int,
@XLocale int )
RETURNS int
AS
BEGIN
RETURN ( SELECT LCID from dbo.PublishingCulture(@XLanguage, @XLocale) )
END
select * from
(select Row_number() OVER(order by StartDate) as RN, Message
from [Ticker] as T
join dbo.MyLCID1(@XLanguage, @XLocale) as L on T.LCID = L.LCID
where
Getutcdate() BETWEEN StartDate AND EndDate
) AS T
where RN BETWEEN @StartIndex AND (@StartIndex + @MaxItems -1)
select * from
(select Row_number() OVER(order by StartDate) as RN, Message
from [Ticker] as T
where
LCID = dbo.PubLCID1(@XLanguage, @XLocale) AND
Getutcdate() BETWEEN StartDate AND EndDate
) AS T
where RN BETWEEN @StartIndex AND (@StartIndex + @MaxItems -1)
[Culture] 在 XLang，Xloc 上有 PK，[Ticker] 在 LCID，Id 上有 PK（Id 是人造的）和 IX 在 StartDare，EndDate，LCID 上有 IX -- 尽可能接近真实的东西，只要几行就能得到。
评论