Вопрос по sql-server – Может ли встроенная табличная UDF превзойти эквивалентную скалярную UDF в списке столбцов SELECT?

4

This question grew out of SQLServer: Почему следует избегать пользовательских функций с табличными значениями?, Я начал задавать вопросы в некоторых комментариях, и ответы на мои комментарии переместились за пределы темы.


Так что вам не нужно читать всю дискуссию: я никогда не слышал, чтобы в ней говорилось, что пользовательские функции (UDF) работают медленно или их следует избегать. Некоторые ссылки были опубликованы в вопросе, указанном выше, чтобы показать, что они работают медленно. Я все еще не понял и попросил привести пример. Был опубликован пример, и разница в производительности была огромной.

Я не могу быть единственным человеком, который не понимал, что может быть такая большая разница в производительности. Я чувствовал, что этот факт должен быть разделен на новый вопрос и ответ, чтобы улучшить его шансы быть найденным. Это и есть «вопрос». Пожалуйста, еще не закрывайте, так как я хотел бы дать ответчику время для публикации ответа.

Конечно, другие также должны публиковать ответы или примеры. Я особенно ценю все, что поможет мне понятьwhy разница в производительности такая огромная.

Отметим также, что я не говорю об использовании UDF в предложении WHERE. Мне известно, как это может помешать оптимизатору выполнять свою работу. Меня особенно интересуют различия в производительности, когда исходный UDF был частью списка столбцов SELECT.

Что за история с отрицательными голосами? Скажите, пожалуйста, в чем проблема, или я никогда не узнаю, как ее исправить. John Saunders

Ваш Ответ

2   ответа
5

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, который так же быстр, как просто добавление, и выполните это 1M раз:

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 и встроенного UF очевидна.

@IanC: вы правы, встроенный вызов встроен в запрос, и скалярная функция вызывается один раз для каждой строки. Что вводит в заблуждение? Все, о чем я заботился и говорил, было выступление, и мое утверждение об этом правильно, не так ли?
Этот тест вводит в заблуждение. Взгляд на план выполнения, несомненно, покажет, что встроенный UDF не вызывается 1 M раз, а фактически оптимизирован во внешний запрос. Скалярная функция, напротив, не может быть так оптимизирована, поскольку это черный ящик. Как и все остальное, у каждого есть свое применение, и, если оно используется правильно, сервер цели под рукой.
Поскольку скалярный UDF не осуществляет доступ к данным, вы должны его создать.WITH SCHEMABINDING, Я не проверял, меняет ли это что-либо в этом случае.
все, что я имею в виду, это то, что кто-то, кто не понимает различий между различными UDF, мог бы сделать вывод из вашего заключения, отличного от того, что вы хотите (то есть правильное заключение). Нет ничего технически неточного в том, что вы сказали. ИМО важно помогать другим делать правильные выводы :)
2

-) Я думаю, нам нужны более реалистичные примеры и воздерживаться от игр с гочами. Примеры, которые выглядят слишком надуманными, всегда вызывают у меня подозрение. Так,I rearranged the query just a bit and straight scalar UDF outperformed the query, Dont & APOS; поверьте - попробуйте - это было на SQL 2k8 на dev-сервере под 2k8 Server Std.

Все, что мы узнали до сих пор, это то, что использование вычисляемых столбцов и эквивалентов в предложениях WHERE - это плохо. Этот запрос использовал скалярную функцию в предложении WHERE, делая вид, что он находится в выборе.

SELECT COUNT(*) FROM( 
SELECT n as X,n+1 AS ValuePlusOne 
FROM  dbo.Numbers 
) AS t WHERE X>0 

Таблица «Номера». Сканирование 1, логическое чтение 3521, физическое чтение 0, чтение с опережением 0, логическое чтение 1, физическое чтение 1, чтение с опережением 0.

Время выполнения SQL Server:    Время процессора =234 msпрошедшее время = 228 мс

SELECT COUNT(*) FROM( 
SELECT n as X ,dbo.AddOne(n) AS ValuePlusOne 
FROM  dbo.Numbers 
) AS t WHERE X>0 

Таблица «Номера». Сканирование 1, логическое чтение 3521, физическое чтение 0, чтение с опережением 0, логическое чтение 1, физическое чтение 1, чтение с опережением 0.

Время выполнения SQL Server:    Время процессора =202 msистекшее время = 215 мс

So, now that we resolved that how about some real info and realistic use cases ?

Я предоставлю 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)

[Культура] имеет PK на XLang, Xloc, [Ticker] имеет PK на LCID, Id (Id является искусственным) и IX на StartDare, EndDate, LCID - настолько близкие к чему-то реальному, насколько это возможно в несколько строк.

Похожие вопросы