Тестирование производительности скалярных и табличных функций на сервере sql

Итак, я прочитал целую кучу статей, предлагающих функции табличного значения и перекрестное применение, обеспечивающие лучшую производительность, чем скалярный udf. Я хотел написать свою функцию обоими способами, а затем проверить, какой из них лучше, но я не могу понять, что я должен использовать/искать, чтобы понять, какой вариант лучше.

Я использую SQL Server 2005. Я пытался запустить предполагаемый план выполнения, фактический план выполнения и проанализировать запрос в советнике по настройке ядра базы данных, и я не знаю, что он пытается мне сказать.

Использование showplan_all on/off похоже, что табличная функция будет использовать больше процессора 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

Спасибо!!


sql sql-server-2005 stored-functions
person Jen    schedule 15.12.2010    source источник
comment
Просто из любопытства - вы действительно находите, что YYYY более читабелен, чем, скажем, Year?   -  person Damien_The_Unbeliever    schedule 15.12.2010
comment
Я как-то не подумал об этом... и взял этот синтаксис из чужой функции :) Но да, год был бы более читабельным.   -  person Jen    schedule 16.12.2010

Ответы (1)


arrow_upward
19
arrow_downward

Возможно, вы не увидите ожидаемого прироста производительности, потому что ваша функция с табличным значением является многофункциональной, а не встроенной. Многофункциональные TVF должны выполняться так же, как и скалярные UDF — один раз на строку — так что выигрыш очень небольшой.

Следуя примеру из этой статьи Ицика Бен-Гана ( в котором обсуждаются преимущества встроенных TVF), настройте следующий тест:

Создайте таблицу чисел с 1 миллионом строк:

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

В моей системе это показало в среднем 83 секунды затраченного времени для трех выполнений, выполняя DBCC dropcleanbuffers между каждым выполнением.

Если вы выполняете аналогичный тест для своей скалярнозначной функции, у вас должно быть более четкое представление о сравнительной производительности.

Тест также выявил ошибку в вашей функции. Если для 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

Это имело среднее затраченное время 8,9 секунды за три прогона - почти в десять раз быстрее.

Еще одна вещь, которую следует учитывать, заключается в том, что выигрыш в производительности от использования TVF будет незначительным, если только вы не применяете его к нескольким строкам, как в этом тесте. Если вы используете его для одного значения за раз, вы не увидите много преимуществ, если у вас нет тысяч экземпляров функции, выполняющихся параллельно.

person Ed Harper    schedule 15.12.2010
comment
Спасибо за действительно подробный ответ и за улучшение моей функции! :) - person Jen; 16.12.2010