在 sql server 中测试标量函数与表值函数的性能 [英] Testing performance of Scalar vs Table-valued functions in sql server

问题描述

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

OK so I have read a whole bunch of articles suggesting table-value functions and cross apply give better performance than a scalar udf. I wanted to write my function in both ways and then test to see which one is better - but I cannot figure out what I'm supposed to use/look for to understand which is the better option.

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

I'm using SQL Server 2005. I've tried running the estimated execution plan, the actual execution plan and analyze query in database engine tuning advisor and I don't know what it is trying to tell me.

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

Using showplan_all on /off it looks like the table based function will use more cpu 1.157e-06 vs 8.3e-05 but the table function has a total subtree cost of 0.000830157 vs 0.01983356.

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

The query cost of the table valued function also seems to have a higher cost than the scalar one. Even though I thought it was supposed to be the better option.

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

So whilst I'd like to prove it myself which one gives the better performance - I'm just not sure what to look for in these tools - so any suggestions would be appreciated!

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

I need to get an academic year value (based on a date range set in the database) based on a calendar date so the function contents are below - so its just whether I go scalar or table based. This year feeds into other queries..

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 

谢谢！！

推荐答案

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

You may not be seeing the performance gains you expect because your table-valued function is multifunction, not in-line. Multi-function TVFs have to be executed in the same way as scalar UDFs - once per row - so there's very little gain.

遵循 Itzik Ben-Gan 的这篇文章中的示例(讨论内联 TVF 的好处)，设置以下测试:

Following the example in this article by Itzik Ben-Gan (which discusses the benefits of in-line TVFs), set up the following test:

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

Create a numbers table with 1 million rows:

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:

Run a million executions of your TVF using the following code:

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 秒.

On my system, this showed an average of 83 seconds elapsed time for three executions, running DBCC dropcleanbuffers between each execution.

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

If you perform a similar test for your scalar valued function, you should have a clearer idea of comparative performance.

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

The test also revealed what appears to be a bug in your function. If the AcademicYear.StartDate is set to '2010-09-01', the Academic Year returned for an input of '1900-01-01' is 1789, where it seems like 1899 would be expected.

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

To get the best performance, you'd need to convert the TVF to be in-line - I came up with the following, which I believe corrects the bug:

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 秒 - 快了近十倍.

This had an average elapsed time of 8.9 seconds over three runs - almost ten times faster.

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

The other thing to consider is that the performance benefit from using TVF will be negligible unless you are applying it to multiple rows, as in this test. If you're using it on one value at a time, you won't see a lot of benfit unless you have thousands of instances of the function executing in parallel.

这篇关于在 sql server 中测试标量函数与表值函数的性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！