Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
 Функция создается каждый раз при обращении  [new]
bravo1
Member

Откуда:
Сообщений: 16
Всем привет.

На одном из проектов используем MSSQL Server 2012 Enterprise.

В БД создали функцию, которую потом используем в запросах:

CREATE FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
DECLARE @intAlpha INT
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
BEGIN
WHILE @intAlpha > 0
BEGIN
SET @strAlphaNumeric = STUFF(@strAlphaNumeric, 1, @intAlpha, '' )
SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
END
END

IF @strAlphaNumeric = ''
RETURN NULL

RETURN @strAlphaNumeric
END
GO

Люди, занимающиеся отслеживанием нагрузки на БД, прислали список самых горячих запросов и в нем под первым номером как раз запрос на создание этой процедуры.

Разумеется никто из кода приложения процедуру не создает.
Создали один раз и потом используем в запросах.

Это такая особенность MSSQL?

Поиском ничего не нашел, но если тема уже обсуждалась, ткните, плиз.
2 июн 15, 17:23    [17721620]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
Glory
Member

Откуда:
Сообщений: 104751
bravo1
Это такая особенность MSSQL?

Это такая "особенность" ваших людей, отслеживающих нагрузки.
2 июн 15, 17:26    [17721635]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
bravo1
Member

Откуда:
Сообщений: 16
Glory
bravo1
Это такая особенность MSSQL?

Это такая "особенность" ваших людей, отслеживающих нагрузки.


Э.... "особенность" наших людей в том, что таких запросов быть не может или в том, что такие запросы настолько быстрые, что не должны попадать в TOP?
2 июн 15, 17:40    [17721724]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
Glory
Member

Откуда:
Сообщений: 104751
bravo1
Э.... "особенность" наших людей в том, что таких запросов быть не может или в том, что такие запросы настолько быстрые, что не должны попадать в TOP?

Запросы такие могут быть. Если конечно вы под запросами понимаете любые команды TSQL.
И даже в TOP они попасть могут. Если никаких других запросов нет.
2 июн 15, 17:43    [17721743]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
bravo1
Member

Откуда:
Сообщений: 16
Glory
Запросы такие могут быть. Если конечно вы под запросами понимаете любые команды TSQL.
И даже в TOP они попасть могут. Если никаких других запросов нет.


Да, под запросом понимаю команду TSQL.

Я правильно понял, что даже если я никогда не выполняю команду CREATE FUNCTION (потому что такая функция уже создана), то MSSQL каждый раз создает ее для меня?

А можно про это где-нибудь почитать?

В BOL что-то не увидел. Ткните, плиз.
2 июн 15, 17:47    [17721761]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
Glory
Member

Откуда:
Сообщений: 104751
bravo1
Да, под запросом понимаю команду TSQL.

А что под запросом понимают "Люди, занимающиеся отслеживанием нагрузки на БД" ?
Что, чем и как они отслеживали ?

bravo1
Я правильно понял, что даже если я никогда не выполняю команду CREATE FUNCTION (потому что такая функция уже создана), то MSSQL каждый раз создает ее для меня?

Вот интересно, а откуда вы это поняли ?

bravo1
А можно про это где-нибудь почитать?

В BOL что-то не увидел. Ткните, плиз.

В BOL ерунду и глупости не пишут.
2 июн 15, 17:49    [17721772]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
Владислав Колосов
Member

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

Скорее всего, они нашли в кэше текст функции и решили, что этот текст выполняется.
Вы создали скалярную функцию, её использовании в запросах приведет, скорее всего, к тому, что функция будет выполнена для каждой строки таблицы, используемой при обращении к функции в запросе. Для оптимального применения функции в запросе перепишите её как inline табличную функцию.
2 июн 15, 17:49    [17721773]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
bravo1
Member

Откуда:
Сообщений: 16
Владислав Колосов
bravo1,

Скорее всего, они нашли в кэше текст функции и решили, что этот текст выполняется.
Вы создали скалярную функцию, её использовании в запросах приведет, скорее всего, к тому, что функция будет выполнена для каждой строки таблицы, используемой при обращении к функции в запросе. Для оптимального применения функции в запросе перепишите её как inline табличную функцию.


Спасибо, попробую.
2 июн 15, 17:53    [17721785]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
bravo1
Владислав Колосов
bravo1,

Скорее всего, они нашли в кэше текст функции и решили, что этот текст выполняется.
Вы создали скалярную функцию, её использовании в запросах приведет, скорее всего, к тому, что функция будет выполнена для каждой строки таблицы, используемой при обращении к функции в запросе. Для оптимального применения функции в запросе перепишите её как inline табличную функцию.


Спасибо, попробую.



Если эта функция часто задействована в запросах, то вы можете увидеть её в огромной количестве. У меня как функция была показана 180 000 раз в секунду. Это значит, что она откомпилирована и выполняется такое количество раз. Ничего страшного в этом нет.

Идёт выполнение запроса и эта функция будет насчитана на каждую строку, где она была вызвана. Это не означает, что она все время перекомпилируется или что-то такое.
2 июн 15, 19:41    [17722118]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
a_voronin
bravo1
пропущено...


Спасибо, попробую.



Если эта функция часто задействована в запросах, то вы можете увидеть её в огромной количестве. У меня как функция была показана 180 000 раз в секунду. Это значит, что она откомпилирована и выполняется такое количество раз. Ничего страшного в этом нет.

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


Автору вообще-то неплохо было бы привести конкретный запрос из профайлера или из того места, где увидели постоянное
создание этой функции "люди, занимающиеся отслеживанием"
2 июн 15, 21:01    [17722391]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
o-o
Guest
да понятно примерно, что они делают.
сравните второй и третий столбец вот в таком:
select qs.execution_count, qt.text,
       substring(qt.text, qs.statement_start_offset/2,         
       (case when qs.statement_end_offset = -1 
             then len(convert(nvarchar(max), qt.text)) * 2 
             else qs.statement_end_offset 
        end 
        - qs.statement_start_offset)/2)  as query_text
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
order by qs.execution_count desc

они третий столбец считать не умеют, а второй их удивил несказанно
2 июн 15, 21:33    [17722525]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
o-o
да понятно примерно, что они делают.
сравните второй и третий столбец вот в таком:
select qs.execution_count, qt.text,
       substring(qt.text, qs.statement_start_offset/2,         
       (case when qs.statement_end_offset = -1 
             then len(convert(nvarchar(max), qt.text)) * 2 
             else qs.statement_end_offset 
        end 
        - qs.statement_start_offset)/2)  as query_text
from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) as qt
order by qs.execution_count desc

они третий столбец считать не умеют, а второй их удивил несказанно


в чем-то их можно понять, если уже второй столбец удивил, то смысл смотреть дальше?
2 июн 15, 21:45    [17722586]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
leov
Member

Откуда: С-Петербург
Сообщений: 616
у меня однажды была похожая ситуация
на основе данных одной строки таблицы с помощью довольно громоздкого логического выражения
вычислялся некоторый признак, который использовался кажется в where, точно уже не помню
и применялось это больше чем в одном запросе
ну подумалось оформить это в виде функции, чтобы хотя-бы логику того выражения в одном месте править
не больно шустро это работало
полез смотреть трейс и ужаснулся что она вызывается многие тысячи раз, очень это не понравилось
плюнул и переписал воткнув выражение в каждый запрос
мне показалось что стало быстрее
2 июн 15, 22:12    [17722689]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
o-o
Guest
leov,

ТС решил, что ф-ция столько раз "создается", сколько и вызывается,
что неверно, а вы увидели то, что и есть на самом деле:
вызов ф-ции вещь небесплатная,
а скалярка вызовется для каждой строки.
ну т.е. расходы на это надо помножить на число строк,
ужаснуться и отказаться в случае внушительной таблицы
(переделать в инлайн, как тут уже и посоветовали)

You should be aware that invoking scalar UDFs in queries has a high cost when you provide
the function with attributes from the outer table as inputs. Even when the function only has
a RETURN clause with a scalar expression, it is not considered inline. The overhead of the
function call per row involves a high cost.
2 июн 15, 23:05    [17722847]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
o-o
leov,

ТС решил, что ф-ция столько раз "создается", сколько и вызывается,
что неверно, а вы увидели то, что и есть на самом деле:
вызов ф-ции вещь небесплатная,
а скалярка вызовется для каждой строки.
ну т.е. расходы на это надо помножить на число строк,
ужаснуться и отказаться в случае внушительной таблицы
(переделать в инлайн, как тут уже и посоветовали)

You should be aware that invoking scalar UDFs in queries has a high cost when you provide
the function with attributes from the outer table as inputs. Even when the function only has
a RETURN clause with a scalar expression, it is not considered inline. The overhead of the
function call per row involves a high cost.


хых, 100%, я с какого-то момента стал считать, что функции в T-SQL - это зло
2 июн 15, 23:39    [17722928]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
a_voronin
Если эта функция часто задействована в запросах, то вы можете увидеть её в огромной количестве. У меня как функция была показана 180 000 раз в секунду. Это значит, что она откомпилирована и выполняется такое количество раз. Ничего страшного в этом нет.
Страшно то, что производительность сильно упадёт, запрос буджет выполняться медленно, сервер будет загружен всякой ерундой.

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

Мы тут это не раз обсуждали, вывод такой - скалярную функцию можно использовать, если есть гарантия, что она будет вызываться только для строк выходного рекордсета.
3 июн 15, 01:45    [17723176]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
bravo1
Member

Откуда:
Сообщений: 16
Спасибо всем откликнувшимся.

Функция действительно вызывается в запросе, обрабатывающем многие тысячи строк (сотни тысяч).

Вот запрос, которым смотрели тяжелые запросы:

SELECT cast(sqltext.TEXT as varchar(8000)) as my_text ,      
      req.session_id, req.status, req.command,               
      req.cpu_time,req.total_elapsed_time,                   
      (select host_name from  sys.dm_exec_sessions s         
          where s.session_id = req.session_id) as host_name   
   FROM sys.dm_exec_requests req                             
   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS sqltext   
      where command = 'SELECT' and total_elapsed_time > 1000 
3 июн 15, 09:38    [17723551]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
bravo1
Member

Откуда:
Сообщений: 16
o-o
leov,

You should be aware that invoking scalar UDFs in queries has a high cost when you provide
the function with attributes from the outer table as inputs. Even when the function only has
a RETURN clause with a scalar expression, it is not considered inline. The overhead of the
function call per row involves a high cost.


Спасибо, а откуда цитата, поиском что-то не нашел?
3 июн 15, 09:38    [17723554]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
o-o
Guest
bravo1
а откуда цитата, поиском что-то не нашел?

вообще-то поиском тоже находится:
кусочек
это именно из этой книги, книга стОящая:
Inside Microsoft SQL Server 2008 T-SQL Programming
By Itzik Ben-Gan, Dejan Sarka, Roger Wolter, Greg Low, Ed Katibah, Isaac Kunen
3 июн 15, 10:19    [17723784]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
alexeyvg
a_voronin
Если эта функция часто задействована в запросах, то вы можете увидеть её в огромной количестве. У меня как функция была показана 180 000 раз в секунду. Это значит, что она откомпилирована и выполняется такое количество раз. Ничего страшного в этом нет.
Страшно то, что производительность сильно упадёт, запрос буджет выполняться медленно, сервер будет загружен всякой ерундой.

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

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



В моём случае замена функции на константу никак на скорость не повлияло. Время исполнения осталось таким же. А это было на десятках лямов строк.
3 июн 15, 10:56    [17724035]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
zasandator
Member [скрыт] [заблокирован]

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

оптемезируй фунгцыю чо...

нопремер таг... ну или исчо побыстрее чево найдьош.

ALTER FUNCTION dbo.udf_GetNumeric
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
    DECLARE @intAlpha INT
    SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric)
    BEGIN
	   WHILE @intAlpha > 0
	   BEGIN
		  SET @strAlphaNumeric = STUFF(@strAlphaNumeric, 1, @intAlpha, '' )
		  SET @intAlpha = PATINDEX('%[^0-9]%', @strAlphaNumeric )
	   END
    END

    IF @strAlphaNumeric = ''
	   RETURN NULL

    RETURN @strAlphaNumeric
END
GO

ALTER FUNCTION dbo.udf_GetNumeric2
(@strAlphaNumeric VARCHAR(256))
RETURNS VARCHAR(256)
AS
BEGIN
    return reverse(left(reverse(@strAlphaNumeric),patindex('%[^0-9]%',reverse('x'+@strAlphaNumeric))-1))
END
GO

declare
    @d datetime = getdate(),
    @i int = 10000

while @i > 0
begin
    set @i -= 1
    print dbo.udf_GetNumeric('125503')
end

select datediff(millisecond, @d, getdate()) as orig_func_time

select
    @d = getdate(),
    @i = 10000

while @i > 0
begin
    set @i -= 1
    print dbo.udf_GetNumeric2('125503')
end

select datediff(millisecond, @d, getdate()) as new_func_time


Твоя фунгцыя 564 выдала на моем локальном компе, новая... 116 миллисекунд...
3 июн 15, 11:00    [17724068]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
o-o
Guest
a_voronin
В моём случае замена функции на константу никак на скорость не повлияло. Время исполнения осталось таким же. А это было на десятках лямов строк.

да у других просто фантазии не хватило такую ф-цию написать, куда простым смертным до вас
create function dbo.udf_kto_na_svete_vsex_umnee_vsex_krasivej_i_belee()
returns varchar(256)
as
begin
   return 'A_VORONIN';
end;
3 июн 15, 11:07    [17724106]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4893
o-o
a_voronin
В моём случае замена функции на константу никак на скорость не повлияло. Время исполнения осталось таким же. А это было на десятках лямов строк.

да у других просто фантазии не хватило такую ф-цию написать, куда простым смертным до вас
create function dbo.udf_kto_na_svete_vsex_umnee_vsex_krasivej_i_belee()
returns varchar(256)
as
begin
   return 'A_VORONIN';
end;


А вот тут пишут, что из перьев o-o делают церемониальные халаты для гавайских вождей.

o-o
[oh-oh]
Spell Syllables
Word Origin
noun, plural o-os.
1.
any of several species of Hawaiian honey eaters of the genus Moho, especially the extinct M. nobilis, of the island of Hawaii, that had black plumage and two tufts of yellow plumes used to make ceremonial robes for the Hawaiian kings.
3 июн 15, 11:41    [17724335]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
invm
Member

Откуда: Москва
Сообщений: 9825
a_voronin
В моём случае замена функции на константу никак на скорость не повлияло. Время исполнения осталось таким же. А это было на десятках лямов строк.
Если функция была скалярная и не schemabinding, то вы либо в очередной раз выдаете желаемое за действительное, либо ваш запрос настолько "тормозной", что накладные расходы на вызов функции не оказывают заметного влияния на время выполнения.
3 июн 15, 11:48    [17724391]     Ответить | Цитировать Сообщить модератору
 Re: Функция создается каждый раз при обращении  [new]
o-o
Guest
a_voronin
из перьев o-o делают церемониальные халаты для гавайских вождей.


занятно и познавательно, спасибо

а вообще, сказочку не слыхали, почему летучая мышь голая?
тоже перья чужие собирала, да все это закончилось тем самым, так теперь и летает голышом

кстати, даже оптимизатор 2014-ого сервера не в состоянии развернуть скалярку, тупо возвращающую константу,
в просто константу.
им точно такая супер-функция и в голову не могло прийти,
так что поддерживаю версию invm
3 июн 15, 12:04    [17724517]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить