Производительность запросов с использованием UDF

alexxa

Пример (MS SQL Server 2000 SP4)есть таблица:CREATE TABLE dbo.Table1(a int NULL, b int NULL, c int NULL) ON PRIMARYи скалярная пользовательская функция:CREATE FUNCTION dbo.UDF1(@a int, @b int)RETURNS int ASBEGIN RETURN @a * @b ENDЕсли в таблице Table1 количество записей ~1 млн., то запрос вида:SELECT a, b, с, a * b * c FROM Table1выполняется в несколько раз быстрее запроса вида:SELECT a, b, c, dbo.UDF1(dbo.UDF1(a, b), c) FROM ********* этом падение производительности почти не зависит от сложности самой скалярной функции и ее использования в запросе.Вопрос: где можно "крутить", что бы производительность запросов с использованием скалярных пользовательских функций в MS SQL 2000 не падала так сильно?
24 ответа

alexxa

Функция выполняется для каждой строки. Т.е. сколько у Вас строк запрос возвращает, столько раз "дергается" функция.Со всеми вытекающими...


alexxa

То что для каждой строки, понятно. Вопрос в том, где можно "крутить", если, конечно, можно...


alexxa

Вопрос в том, где можно "крутить", если, конечно, можно...
Нужно не использовать UDF просто так


alexxa

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


alexxa

Сама функция может быть очень сложной, результат ее выполнения определять ход дальнейших вычисленияй.В вашем примереSELECT a, b, c, dbo.UDF1(a, b) FROM TABLE1функция как раз ничего не определяетЗапрос с вычислениями (даже очень сложными) без UDF выполняется почти так же быстро, как и обычный запрос на выборку без вычислений на основе той же таблицы.Ну так оформите запрос в виде представления.


alexxa

Так и делается, все вычисления в одном представлении, без UDF и вложенных подзапросов. Только кроме автора никто там ничего разобрать не может, да и сам автор то же может подзабыть. Не говоря уже о версионности и т.п...


alexxa

Так и делается, все вычисления в одном представлении, без UDF и вложенных подзапросов. Только кроме автора никто там ничего разобрать не может, да и сам автор то же может подзабыть. Не говоря уже о версионности и т.п...
И что изменится, если этот подзапрос из представления переместиться в UDF ?


alexxa

И что изменится, если этот подзапрос из представления переместиться в UDF ?
Внесу свои гнутые 3 копейки:Вроде это называлось раньше "процедурное программирование". Эта UDF потом может быть вызвана из разных view/sp...Код станет более читабельным...


alexxa

падает скорость выполнения, с подзапросом еще сильнее. пример:запрос типаSELECT a, b, c, dbo.UDF1(c, d)FROM (SELECT a, b, c, dbo.UDF1(a, b) AS d FROM Table1) Tработает еще хуже, чем:SELECT a, b, c, dbo.UDF1(dbo.UDF1(a, b), c) FROM ********* этом запрос вида:SELECT a, b, c, c * dFROM (SELECT a, b, c, a * c AS d FROM Table1) T выполняется медленнее (не сильно, считанные проценты), чем запрос вида:SELECT a, b, c, a * b * c AS d FROM Table1И это на простом примере!!! Вот и приходиться все оформлять в одном выражении SELECT, без UDF и вложенных SELECT -ов...


alexxa

падает скорость выполнения, с подзапросом еще сильнее. пример:запрос типаSELECT a, b, c, dbo.UDF1(c, d)FROM(SELECT a, b, c, dbo.UDF1(a, b) AS d FROM Table1) Tработает еще хуже, чем:SELECT a, b, c, dbo.UDF1(dbo.UDF1(a, b), c) FROM TABLE1............
Странно! Если вы два селекта делаете, вместо одного, то они с какой стати быстрее выполнятся?Два гвоздя забить однозначно дольше, чем один.Это если один очень длиииииинный гвоздь, тогда еще может быть.Но в приведенных запросах все гвозди одинаковые.-- Tygra's --


alexxa

Вопрос о том, как сделать так, что бы использование UDF не сильно снижало производительности выполнения запросов. Без использования UDF текст запросов со сложными вычислениями по возвращаемым полям становится непонятным для разработчиков, использование подзапросов для улучшения понимания так же снижает производительность.


alexxa

Стэк вызовов в 2000 сделан, похоже, не в расчете на массовый вызов UDFВ результате применение даже пустой UDF просаживает запросы жуткоЭто факт и бороться тут не с чемВдогонку - нет распознавания "константных" UDF, хотя сгородили механизм "детерменизма", на который можно было бы опереться в этих целыхНо - не работает он на самом деле :)Создание вьюва на getdate() прекрасно демонстрирует сей факт


alexxa

TO Crimean:GETDATE() - как раз недетерминированная функция, но если, возвращаясь к примеру с умножением, то в запросе типа:SELECT MAX(t), MIN(t) FROM (SELECT *, GETDATE() AS t FROM Table1) Tвызывается один(!!!) раз на ~ 1 млн. строк.Попытка вместо GETDATE() в подзапросе подставить dbo.UDF1(dbo.UDF1(a, b), c) приводит сервер в задумчивость, хотя выражение a * b * c вместо GETDATE() понимает легко.И очень жаль, что такой механизм как UDF (особенно в скалярных выражениях) не всегда можно использовать.


alexxa

Сама функция может быть очень сложной, результат ее выполнения определять ход дальнейших вычисленияй. Так что текст запроса может в итоге стать нечитаемым. UDF упрощает чтение, но ухудшает производительность, причем сильно. Запрос с вычислениями (даже очень сложными) без UDF выполняется почти так же быстро, как и обычный запрос на выборку без вычислений на основе той же таблицы. Неужели все так плохо с UDF?
Ну почему - не так уж все и плохо.Например не каждый раз ты выбираеш сразу все записи из таблицы-соответственно количество обращений к функции умеьшается.А если уж действительно нужна сразу вся таблица, то создай ты дополнительное поле в таблице и поддеривай его при помощи тригеров.А UDF - это конечно не быстро, зато очень удобно. А как ты представляеш ее реализацию - чтобы в твоем случае она не тормозила? (Учитывая, что это интерпретатор) Как вариант разве что UDF как в Interbase (берется из DLL)- но тогд из UDF не получиш быстрый доступ к БД .


alexxa

Возможно, лучше использовать UDF, возвращающую набор данных?Правда, тогда она не сильно будет отличаться от view...


alexxa

Вопрос поконкретней:Как заставить оптимизатор запросов MS SQL Server 2000 понимать скалярные детерминированные функции, что бы он их разворачивал и строил план выполнения запроса с учетом выражений внутри функций, а не дергал вызов этих функций для каждой возвращаемой строки?Было высказано мнение, что никак. Другие мнения есть?


alexxa

Как заставить оптимизатор запросов MS SQL Server 2000 понимать скалярные детерминированные функции, что бы он их разворачивал и строил план выполнения запроса с учетом выражений внутри функций, а не дергал вызов этих функций для каждой возвращаемой строки?Никак. Оптимизатор может влючить в план запроса только Inline Table-valued Functions. И еще проанализировать структуру выходной таблицы для Multi-statement Table-valued Functions.Со Scalar Functions работа идет как с "черным ящиком".


alexxa

В MS SQL Serever 2005 поведение скалярных UDF не изменилось. Стоит ли ждать от Microsoft оптимизации их выполнения? И есть ли подобная оптимизация в серверных СУБД других производителей?


alexxa

В MS SQL Serever 2005 поведение скалярных UDF не изменилось. Стоит ли ждать от Microsoft оптимизации их выполнения? И есть ли подобная оптимизация в серверных СУБД других производителей?
Как вы себе представляете оптимизацию функции скажем вида
CREATE FUNCTION dbo.fn_Calls4Service (@cust_number varchar(<b>10</b>), @s23_subscriber varchar(<b>20</b>), @service varchar(<b>20</b>), @for_date datetime) 
RETURNS decimal(<b>12</b>,<b>2</b>) AS 
BEGIN
DECLARE @ret_value	decimal(<b>12</b>,<b>2</b>)
IF @cust_number IS NULL OR @for_date IS NULL OR ISDATE(@for_date) = <b>0</b> OR @s23_subscriber IS NULL
	RETURN <b>0</b>
IF @service = 'BOX'
	SELECT @ret_value = COUNT(*)
			FROM dbo.calls a
			WHERE (a.inv_period = CONVERT(char(<b>7</b>), @for_date, <b>111</b>))
				AND (a.kl = @cust_number) AND (a.subr = @s23_subscriber) AND (a.start >= @for_date)
				AND ((a.dialled LIKE '[02]567%') AND (a.summa <> <b>0</b>) AND (a.categ <> 'FT') ) 
ELSE IF @service = 'DUOLIIN'
	SELECT @ret_value = COUNT(*)
			FROM dbo.calls a
			WHERE (a.inv_period = CONVERT(char(<b>7</b>), @for_date, <b>111</b>))
				AND (a.kl = @cust_number) AND (a.subr = @s23_subscriber) AND (a.start >= @for_date)
				AND (a.dest = 'RHOME') 
			
RETURN @ret_value
END
в рамках какого-то запроса ?


alexxa

TO Glory:С вашим примером - никак...А вот с простыми скалярными функциями типа:MAXDATE(@date1, @date2, @date3, ...) - MINDATE(@date1, @date2, @date3, ...)внутри которых нет обращения к объектам БД очень бы хотелось. А то оформлять все это в виде CASE ... WHEN или других конструкций можно, но очень ненаглядно. А в виде UDF - потеря производительности в общем случае.Но нет, так нет. Компромисы в конкретной задаче достижимы


alexxa

А вот с простыми скалярными функциями типа:Получится, что нужны не просто UDF, а CREATE SIMPLE FUNCTION и CREATE COMPLEX FUNCTION ?Я думаю, что простые нужно просто добиваться расширения TSQL за счет введения в него этих простых скалярных функций.В SQL2005 можно попробовать самому реализовать их на CLR


alexxa

А вот с простыми скалярными функциями типа:Получится, что нужны не просто UDF, а CREATE SIMPLE FUNCTION и CREATE COMPLEX FUNCTION ?Я думаю, что простые нужно просто добиваться расширения TSQL за счет введения в него этих простых скалярных функций.В SQL2005 можно попробовать самому реализовать их на CLR
Имеется в виду, что слишком велико время собственно вызова UDF, без учёта, что там делается внутри. Не думаю, что ф-ция на CLR будет вызываться в 100 раз быстрее.


alexxa

Что то подсказывает мне, что реализация скалярных функций на CLR в MS SQL 2005 еще больше уронит производительность, попробую, отпишусь


alexxa

Вот тут тоже автор вроде сомневалсяhttp://www.sql.ru/forum/actualthread.aspx?tid=234937