内联表值的UDF能否优于选择列列表中的等效标量UDF?

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

好吧,现在你打开了一个棘手的话题:-)我认为我们需要更现实的例子,避免玩捉迷藏游戏。看起来太做作的例子总是让我怀疑。所以 我稍微重新安排了查询,直接标量UDF的性能优于查询 . 不要相信它-试试看-这是在SQL2K8上,在2k8服务器Std下的开发盒上。

到目前为止,我们所学到的只是在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 

SQL Server执行时间: CPU时间= 202毫秒

那么,既然我们已经解决了这个问题,那么一些真实的信息和真实的用例呢?

我将提供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)