SQL Server 2000和2005中用户定义函数的性能

Question

我们有两个表，Customer和CustomerEvent都包含几百万行。在SQL Server2000上，我们部署了一个名为fn_CustomerEvent的自定义项，它根据两个参数CustomerID和EventCode返回TRUE或FALSE。

SELECT dbo.fn_CustomerEvent(1345678, 'Music')

UDF代码为：

CREATE FUNCTION [dbo].[fn_CustomerEvent](@CustomerID INT, @EviCode NVARCHAR(10))
RETURNS NVARCHAR(10)
AS
BEGIN
    DECLARE @List NVARCHAR(10)

    SELECT @List = CASE 
                     WHEN COUNT(*) > 0 THEN 'TRUE'
                     ELSE 'FALSE'
                   END 
    FROM CustomerEvent
    WHERE   
         CustomerID = @CustomerID 
         AND EviCode = @EviCode

    RETURN @List
END

在SQL Server 2000上的性能非常好。3秒内返回TOP5000行。例如,

SELECT TOP 5000 
     CustomerID, dbo.fn_CustomerEvent(1345678, 'Music')
FROM [Table1] 

但现在，我们要迁移到SQL Server 2005。相同的代码，相同的UDF，但性能从3秒急剧下降到1分20秒。

谁能为我指出一个正确的方向，我应该从哪里开始优化性能？

Answer 1

对每一行评估标量UDF (即5000次)。您可以调用它一次，然后将结果存储在一个变量中

DECLARE @Result nvarchar(10)
SELECT @Result = dbo.fn_CustomerEvent(1345678, 'Music') 

SELECT TOP 5000 
     CustomerID, @Result
FROM [Table1] 

或者你可以使用内联TVF (我也会用EXISTS代替COUNT)

CREATE FUNCTION CustomerEvent (@CustomerID INT, 
                               @EviCode    NVARCHAR(10)) 
RETURNS TABLE 
AS 
  RETURN 
    (SELECT CASE 
              WHEN EXISTS(SELECT * 
                          FROM   CustomerEvent 
                          WHERE  CustomerID = @CustomerID 
                                 AND EviCode = @EviCode) THEN 'TRUE' 
              ELSE 'FALSE' 
            END) 

有关此技术的更多信息，请参阅Scalar functions, inlining, and performance: An entertaining title for a boring post。

Answer 2

UDF有一个很大的问题:它们不能与索引一起工作。如果您想要代码重用并保持性能，我通常会构建一个计算列(可以被索引)或视图。

Answer 3

CREATE FUNCTION CustomerEvent (@CustomerID INT, 
                               @EviCode    NVARCHAR(10)) 
RETURNS TABLE 
AS 
  RETURN 
    (SELECT COALESCE((SELECT 'TRUE' FROM CustomerEvent
                    WHERE   
                         CustomerID = @CustomerID 
                         AND EviCode = @EviCode)
                , 'FALSE'))

检查索引，重建它们并更新您的统计数据。