sql - 在 sql server 中测试标量函数与表值函数的性能

Question

好的，所以我读了一大堆文章，建议表值函数和交叉应用比标量 udf 提供更好的性能。我想以两种方式编写我的函数，然后测试以查看哪个更好 - 但我无法弄清楚我应该使用/寻找什么来了解哪个是更好的选择。

我正在使用 SQL Server 2005。我尝试在数据库引擎优化顾问中运行估计的执行计划、实际执行计划和分析查询，但我不知道它试图告诉我什么。

使用 showplan_all on/off 看起来基于表的函数将使用更多的 cpu 1.157e-06 与 8.3e-05，但表函数的总子树成本为 0.000830157 与 0.01983356。

表值函数的查询成本似乎也比标量函数的成本更高。尽管我认为这应该是更好的选择。

因此，虽然我想自己证明哪个性能更好 - 我只是不确定要在这些工具中寻找什么 - 所以任何建议都将不胜感激!

我需要根据日历日期获取学年值(基于数据库中设置的日期范围)，因此函数内容如下 - 所以无论我是基于标量还是基于表格。今年加入了其他查询。

CREATE FUNCTION fn_AcademicYear
(
    -- Add the parameters for the function here
    @StartDate DateTime
)
RETURNS 
@AcademicYear TABLE 
(
    AcademicYear int
)
AS
BEGIN

DECLARE @YearOffset int, @AcademicStartDate DateTime 

    -- Lookup Academic Year Starting Date
    SELECT @AcademicStartDate = CONVERT(DateTime,[Value])
    FROM dbo.SystemSetting
    WHERE [Key] = 'AcademicYear.StartDate'

    SET @YearOffset = DATEPART(YYYY,@StartDate) - DATEPART(YYYY,@AcademicStartDate);
    -- try setting academic looking start date to year of the date passed in
    SET @AcademicStartDate = DATEADD(YYYY, @YearOffset, @AcademicStartDate);

    IF @StartDate < @AcademicStartDate
    BEGIN
        SET @AcademicStartDate = DATEADD(YYYY, @YearOffset-1, @AcademicStartDate);
    END

      INSERT @AcademicYear
      SELECT YEAR(@AcademicStartDate)

    RETURN 

谢谢!!

Answer 1

您可能看不到预期的性能提升，因为您的表值函数是多功能的，而不是内联的。多功能 TVF 必须以与标量 UDF 相同的方式执行 - 每行一次 - 所以增益很小。

遵循 this article by Itzik Ben-Gan 中的示例(讨论了在线 TVF 的好处)，设置以下测试:

创建一个包含 100 万行的数字表:

SET NOCOUNT ON;
IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE T1;
GO

WITH
  L0 AS (SELECT 0 AS c UNION ALL SELECT 0),
  L1 AS (SELECT 0 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  L2 AS (SELECT 0 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  L3 AS (SELECT 0 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  L4 AS (SELECT 0 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  L5 AS (SELECT 0 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L5)
SELECT n INTO dbo.T1 FROM Nums WHERE n <= 1000000;

使用以下代码运行一百万次 TVF:

set statistics time on
SELECT n,DATEADD(HOUR,n,'1900-01-01'),AY.AcademicYear
FROM T1
CROSS APPLY dbo.fn_AcademicYear(DATEADD(HOUR,n,'1900-01-01')) AS AY
set statistics time off

在我的系统上，这显示了运行 DBCC dropcleanbuffers 的 3 次执行平均耗时 83 秒。每次执行之间。

如果您对标量值函数执行类似的测试，您应该对比较性能有更清晰的了解。

该测试还揭示了您的函数中似乎存在的错误。如果 AcademicYear.StartDate设置为“2010-09-01”，则为“1900-01-01”的输入返回的学年是 1789，这似乎是 1899 的预期。

为了获得最佳性能，您需要将 TVF 转换为内嵌式 - 我想出了以下内容，我相信它可以纠正错误:

CREATE FUNCTION fn_AcademicYear2
(
    @StartDate DATETIME
)
RETURNS TABLE
AS
RETURN
(
    -- Lookup Academic Year Starting Date
    WITH dtCTE
    AS
    (
        SELECT CONVERT(DATETIME,[Value]) AS dt
        FROM dbo.SystemSetting
        WHERE [KEY] = 'AcademicYear.StartDate'
    )
    SELECT CASE WHEN @StartDate >= DATEADD(YEAR,DATEDIFF(YEAR,dt,@StartDate),dt) 
                THEN YEAR(@StartDate)
                ELSE YEAR(DATEADD(YEAR,DATEDIFF(YEAR,dt,@StartDate) - 1,dt))
            END AS AcademicYear
    FROM dtCTE
)
GO

这在 3 次运行中的平均运行时间为 8.9 秒 - 几乎快了十倍。

另一件要考虑的事情是，使用 TVF 带来的性能优势可以忽略不计，除非您将其应用于多行，如本测试中所示。如果您一次在一个值上使用它，除非您有数千个并行执行的函数实例，否则您不会看到很多好处。