Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Вызов UDF сильно тормозит  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Добрый день.
Есть простейшая функция для нахождения минимального значения из трех:

CREATE FUNCTION [dbo].[InlineMin3Money](@val1 money, @val2 money, @val3 money)
RETURNS money
WITH SCHEMABINDING
AS
BEGIN
	
RETURN	Case When @Val1 < @Val2 And @Val1 < @Val3 Then @Val1
            When @Val2 < @Val1 And @Val2 < @Val3 Then @Val2 
            Else @Val3
            End

END


Возьмем тестовый скрипт:
SET NOCOUNT ON 
SET STATISTICS TIME OFF

DECLARE @a TABLE(price1 MONEY, price2 MONEY, price3 MONEY)
DECLARE @i INT = 0

WHILE @i < 5000
BEGIN
	INSERT @a
		VALUES(1,2,3)

	SET  @i = @i + 1
END

SET STATISTICS TIME on

SELECT 
    dbo.InlineMin3Money(price1,price2,price3)
FROM @a


На нашем довольно шустром сервере он выполняется в среднем в район 50мс.

Если же заменяем вызов функции на выражение прямо в строке, то время выполнения снижается до 2-3мс.

SET NOCOUNT ON 
SET STATISTICS TIME OFF

DECLARE @a TABLE(price1 MONEY, price2 MONEY, price3 MONEY)
DECLARE @i INT = 0

WHILE @i < 5000
BEGIN
	INSERT @a
		VALUES(1,2,3)

	SET  @i = @i + 1
END

SET STATISTICS TIME on

SELECT 
    	Case When price1 < price2 And price1 < price3 Then price1
				When price2 < price1 And price2 < price3 Then price2 
				Else price3
				END
FROM @a


То есть всё тоже самое, только без вызова функции.
Подскажите, пожалуйста, как сделать её вызов более быстрым? В реальном запросе выражения более громоздкие, поэтому их пихать напрямую без функции неудобно.
Спасибо.
1 авг 19, 20:02    [21940161]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
invm
Member

Откуда: Москва
Сообщений: 9116
Alexey30
Подскажите, пожалуйста, как сделать её вызов более быстрым?
Сделать функцию инлайновой
CREATE FUNCTION [dbo].[InlineMin3Money](@val1 money, @val2 money, @val3 money)
RETURNS table
AS
return	
 select	Case When @Val1 < @Val2 And @Val1 < @Val3 Then @Val1
            When @Val2 < @Val1 And @Val2 < @Val3 Then @Val2 
            Else @Val3
        End as min_val
go

DECLARE @a TABLE(price1 MONEY, price2 MONEY, price3 MONEY)
DECLARE @i INT = 0

WHILE @i < 5000
BEGIN
	INSERT @a
		VALUES(1,2,3)

	SET  @i = @i + 1
END

SET STATISTICS TIME on

SELECT 
 *
FROM @a a cross apply
dbo.InlineMin3Money(price1, price2, price3) b


Ну или дождаться 2019-го сервера.
1 авг 19, 20:11    [21940164]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Спасибо за ответ.
Cross Apply в примере использовать удобно, а в реальном запросе особо не вариант.
Неужели нет никакого более "красивого" метода?
Microsoft SQL Server 2016 (SP2-GDR)
1 авг 19, 20:16    [21940169]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36691
Установить сервер, в котором есть инлайновые скалярки.
1 авг 19, 20:32    [21940176]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Гавриленко Сергей Алексеевич, печально.
CLR можно не пробовать?
1 авг 19, 20:37    [21940182]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7392
Alexey30,

как раз подходящий случай для CLR функции.
1 авг 19, 20:59    [21940194]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
invm
Member

Откуда: Москва
Сообщений: 9116
Alexey30
Cross Apply в примере использовать удобно, а в реальном запросе особо не вариант.
А почему?
1 авг 19, 21:17    [21940200]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
Alexey30
Member

Откуда:
Сообщений: 163
invm, запрос читается сложнее, на мой взгляд. Странно запихивать во FROM то, что должно быть в SELECT.
Однако действительно работает довольно шустро, изменил только тело функции на такое:
ALTER FUNCTION [dbo].[InlineMin3MoneyInline](@val1 money, @val2 money, @val3 money)
RETURNS table
AS
return	
 select	MIN(val) min_val
 FROM (
	VALUES(@val1), (@val2), (@val3)) a(val)
go
1 авг 19, 22:56    [21940233]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
Alexey30
Member

Откуда:
Сообщений: 163
Alexey30, При этом производительность чуть-чуть хуже, чем в первоначальном варианте функции, но там отсутствовали проверки на NULL.
1 авг 19, 22:58    [21940234]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7392
Alexey30,

разница принципиальная.
В первом случае сервер будет вычислять выражение для каждого значения атрибута, а во втором выполнит "подзапрос", который сможет встроить в план запроса так, как если бы обращение происходило к таблице.

Соответственно, первый вариант не сможет использовать параллельный план выполнения, как минимум.
1 авг 19, 23:20    [21940239]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
vikkiv
Member

Откуда: London
Сообщений: 2620
Alexey30
..Cross Apply в примере использовать удобно, а в реальном запросе особо не вариант.
Неужели нет никакого более "красивого" метода?..
насколько помню cross apply будет для каждой строки, одна за одной, по очереди, впрочем как и функция на строку
т.е. по любому последовательное выполнение без паралельности
единственная паралельность как указали выше - в пределах одной строки по значениям под-таблицы,
т.е. как-бы лучше - но и не сильно

для полной паралельности - можно попробовать табличную CLR,
и там уже внутри выстраивать многопоточный алгоритм
2 авг 19, 00:47    [21940258]     Ответить | Цитировать Сообщить модератору
 Re: Вызов UDF сильно тормозит  [new]
Ролг Хупин
Member

Откуда: Чебаркуль
Сообщений: 3447
Alexey30
Спасибо за ответ.
Cross Apply в примере использовать удобно, а в реальном запросе особо не вариант.
Неужели нет никакого более "красивого" метода?
Microsoft SQL Server 2016 (SP2-GDR)


Функции - зло ;-)

Сделайте запрос с раскрученным запросом в процедуре
2 авг 19, 09:53    [21940399]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить