Вызов хранимой процедуры в запросе

← →
victor_ch (2003-07-15 09:29) [0]

Добрый день всем
переношу прлиложение с оракла на MSSql сервер
и возникла проблема с синтаксисом
на оракле у меня был запрос с вызовом функции внутри него
например:
select
lcity(id) cityfrom,lcity(id) cityto
from segments

запрос возвращает список городов функция "lcity" по коду города ищет его название

как такой же запрос будет выглядеть в MSSQL сервере

← →
stone (2003-07-15 09:42) [1]

у функции необходимо указывать owner, например

select dbo.lcity(id) as cityfrom, dbo.lcity(id) as cityto
from segments

← →
victor_ch (2003-07-15 09:48) [2]

не прошло, выскакивает реплика
incorrect syntax near "id"

← →
stone (2003-07-15 10:14) [3]

1. Существует ли функция dbo.lcity(Параметр)?
2. Существует ли поле id в таблице segments?
3. В той ли базе, где содержится segments, выполняется запрос?

← →
victor_ch (2003-07-15 10:23) [4]

> 1. Существует ли функция dbo.lcity(Параметр)?

отдельно если я запускаю функцию и передаю ей параметр
exec lcity @id_c=1, то выполняется и выдает результат
> 2. Существует ли поле id в таблице segments?
да существует

> 3. В той ли базе, где содержится segments, выполняется запрос?
все выполняю в одной и той же базе

← →
stone (2003-07-15 10:37) [5]

> отдельно если я запускаю функцию и передаю ей параметр
> exec lcity @id_c=1, то выполняется и выдает результат

это уже не функция, а хранимая процедура

← →
victor_ch (2003-07-15 10:49) [6]

а разве в MSSQL это не одно и то же?

если нет как написать функцию
я что то не вижу через SQL server enterprise manager где функции

← →
stone (2003-07-15 10:57) [7]

User Defined Functions

← →
Smashich (2003-07-15 11:11) [8]

victor_ch (15.07.03 10:49)

ну используй тада SQL Query Analyzer
процедура и функция в MS SQL не отдно и тоже

← →
victor_ch (2003-07-15 11:26) [9]

и где это искать
может у меня SQL сервер старый, у меня 7-я версия
я нашел только
user defined data types

← →
stone (2003-07-15 11:28) [10]

> я нашел только
> user defined data types

а прямо под ней должно быть
user defined functions

← →
victor_ch (2003-07-15 11:39) [11]

странно у меня нету :(
может какой прибамбас доустановить надо к SQL серверу

← →
stone (2003-07-15 11:42) [12]

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

← →
Fiend (2003-07-15 12:40) [13]

Это не дистрибутив кривой а голова не круглая.

ЮДФ поддерживаются только начиная с 8 версии сервера. Т.е. надо поставить MS SQL Server 2000

← →
АлексейК (2003-07-15 13:58) [14]

User defined functions, в отличии от встроенных, в выборкаx лучше не использовать для обработки массива данных, так как сильно тормозят (у MSSQL интерпретатор языка TransactSQL) при обработке даже небольших объемов данных, и система будет работать со скоротью улитки, так же повышается вероятность мертвых блокировок, достаточно посмотреть трассировщик, при выполнении запроса.
Даннное затруднение можно решить подзапросом.
select
(select .. from a where a.id=segments.id) as cityfrom,lcity(id) as cityto
from segments

← →
SergSuper (2003-07-15 15:59) [15]

> АлексейК

Вы правы с точностью до наоборот :)
Я вот решил проверить и такой чувство что только функции и компилируются (хотя конечно никакой интерпритации в T-SQL нет!)

Скрипт без процедур выполнялся 22с, процедурой - 23, функцией - 2 с!
declare @i1 int, @i2 int, @c int select @i1=1, @c=1 while @i1<1000 begin select @i2=1, @i1=@i1+1 while @i2<1000 select @i2=@i2+1, @c=(@c+@i2)& @i1 end go create proc #pp as declare @i1 int, @i2 int, @c int select @i1=1, @c=1 while @i1<1000 begin select @i2=1, @i1=@i1+1 while @i2<1000 select @i2=@i2+1, @c=(@c+@i2)& @i1 end go #pp go CREATE FUNCTION ss() RETURNS @s TABLE (i int ) as begin declare @i1 int, @i2 int, @c int select @i1=1, @c=1 while @i1<1000 begin select @i2=1, @i1=@i1+1 while @i2<1000 select @i2=@i2+1, @c=(@c+@i2)& @i1 end insert @s select @c return end go select * from ss()

← →
АлексейК (2003-07-16 07:50) [16]

>SergSuper
Это не показатель.
Во первых, я писал что пользовательские функции не следует использовать для обработки массива данных а не для выборки оного. Например, хотим мы преобразовать какой-нибудь код
CREATE FUNCTION F_LS (@ls varchar(20), @separ char(1) = " ") RETURNS varchar(20) AS BEGIN DECLARE @o_ls varchar(20) IF (@ls is not null) SET @o_ls=SUBSTRING(@ls, 1, 2)+@separ+SUBSTRING(@ls, 3, 6)+@separ+ SUBSTRING(@ls, 9, 1)+@separ+SUBSTRING(@ls, 10, 2) ELSE SET @o_ls=null return(@o_Ls) END
Запускаем оба запроса с трассировкой.
SELECT dbo.f_ls([любое текстовоеполе], "-") FROM [любая таблица с текстовым полем] ....
Теперь сморим трассировщик запроса, видно, что функция dbo.f_ls() выполняется для каждой строки результирующего набора, тормоза страшные.

Далее пишем запрос
SELECT SUBSTRING([любое текстовоеполе], 1, 2)+"-"+SUBSTRING([любое текстовоеполе], 3, 6)+"-"+ SUBSTRING([любое текстовоеполе], 9, 1)+"-"+SUBSTRING([любое текстовоеполе], 10, 2) FROM [таблица с текстовым полем]
Смотрим результаты трассировки.
Сравниваем (SUBSTRING встроенная функция и вызывается один раз) делаем выводы.
Даже если в запросе будет всего около 200 записей разница будет заметная. Например у меня при обработки 440 записей разница по времени в 100 раз. В больших объемах данных это критично. То же самое бало бы и с select dbo.lcity(id) as cityfrom, dbo.lcity(id) as cityto
from segments. Кроме того при болших объемах данных часто функция блокирует саму себя, получается deadlock.

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

← →
SergSuper (2003-07-16 10:52) [17]

> АлексейК

Это же Вы написали:
User defined functions, в отличии от встроенных, в выборкаx лучше не использовать для обработки массива данных, так как сильно тормозят ( у MSSQL интерпретатор языка TransactSQL) при обработке даже небольших объемов данных
Я по-моему наглядно продемонстрировал что во всяком случае интерпритации не происходит.
Если под обработкой массива данных вы имели в виду можификацию таблиц-переменных, то думаю вряд ли будет существенная разница при их обработке в функции или в процедуре. К тому же к интерпритации это не имеет отношения.

Далее
SUBSTRING встроенная функция и вызывается один раз
Ну как же она может вызываться один раз, если у нас несколько записей? Естественно она вызывается для каждой записи, да еще и по 4 раза. Поскольку SUBSTRING функция втроенная, она конечно вызывается быстрее, о ней знает оптимизатор и понятно что добавление пользовательской функции внесёт замедление (кстати не только в SQL, в любом языке). Но про замедление в 100 раз, да к тому же заметное на 440 записях - перебор явный.

Я вот взял Вашу же функцию и проверил на 86000 записей(на 300 выполняется мгновенно). Результат - с функцией 1800 мс, без функции - 390. Разница меньше чем в пять раз, ну никак не в 100.

Про мёртвые блокировки - тут проверить трудно, но если учесть что функции не могут модифицировать данные, не имеют транзакций и очевидно блокируют записи только на чтение, то трудно представить почему они могут вызывать deadlockи.

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

А вот как я проверял:
declare @d datetime select @d=getdate() select o1.name,o2.name from sysobjects o1, sysobjects o2 where dbo.F_LS(o1.name+o2.name,"-") like "%---%" select datediff(ms,@d, getdate()) go declare @separ char(1)set @separ = " " declare @w varchar(11) set @w="%---%" declare @d datetime select @d=getdate() select o1.name,o2.name from sysobjects o1, sysobjects o2 where SUBSTRING(o1.name+o2.name, 1, 2)+"-"+SUBSTRING(o1.name+o2.name, 3, 6)+@separ+ SUBSTRING(o1.name+o2.name, 9, 1)+@separ+SUBSTRING(o1.name+o2.name, 10, 2) like @w select datediff(ms,@d, getdate())

В таблице sysobjects у меня было 286 записей

← →
АлексейК (2003-07-16 12:57) [18]

>SergSuper

Я по-моему наглядно продемонстрировал что во всяком случае интерпритации не происходит.
В чем наглядность?
К сожалению, MS SQL Server содержит не компилятор, а интерпретатор сохраненных процедур и триггеров, что делает возможным создание процедур, содержащих семантические ошибки, например вызовы несуществующих процедур, вызовы существующих процедур с неправильным набором параметров и др. Не думаю что для UDF сделано исключение.
Напрример
create procedure Test as exec klvkefljgkejПроцедуру он создаст.
Полезная статья о MS SQL, где есть и про интерпретацию.
http://www.interface.ru/fset.asp?Url=/microsoft/mssqlbug.htm

Если под обработкой массива данных вы имели в виду можификацию таблиц-переменных, то думаю вряд ли будет существенная разница при их обработке в функции или в процедуре.
Я имел ввиду подстановку функций в запрос.

Согласен что SUNSTRING встроенная функция, которую обрабатывает оптимизатор, так в том и вопрос что UDF не оптимизуруются. Замедление в 100 раз, это просто мой конкретный пример, то есть 0 мс и 100 мс, в принципе может и такое быть. Прямой зависимости в скорости работы нет. Однако даже в пять раз как показал ваш тест (не самый худший вариант), это много.

А deadlock"i всеже возникают.

← →
АлексейК (2003-07-16 12:59) [19]

Проблема с производительностью UDF возникла не только у меня
http://www.sql.ru/forum/actualthread.aspx?bid=1&tid=9542

← →
АлексейК (2003-07-16 15:05) [20]

Чего - то я запутался.
Хотя на статью
http://www.interface.ru/fset.asp?Url=/microsoft/mssqlbug.htm
есть ответ Microsof, где критично комментируются утверждения в данной статье.
Но если создать ХП
create procedure test
as
select * from table_x
exec kljfksjdfksjdfkl //несуществующая процедура
select * from table_1
при запуске (по документации при первом запуске происходит компиляция ХП), то хоть и будет выдана ошибка на коммнаду exec,
но оба селекта выполнятся. Как же он тогда производит компиляцию (не сохранения скрипта в базу как создании, именно компиляцию)? Получается что MS SQL выполняет ХП по коммандно.

← →
SergSuper (2003-07-16 15:30) [21]

> АлексейК

Блин, ну Вы увидели статью какого-то дилетанта, которому надо втюхать свой продукт, где написано что процедуры интерпретируются - и поверили. А документации от MS не верите.
Пример процедуры, которую Вы приводите НЕ СОДЕРЖИТ семантические ошибки, поэтому и компилируется. Кстати в версиях 4, 6.0 и 6.5 было бы выдано предупреждение что процедура klvkefljgkej еще не создана. В 7.0 ввели позднюю компиляцию (что мне честно говоря не понравилось) и такие предупреждения потеряли смысл.
Вызов процедур всегда осуществлялся не по бинарной ссылке, как в процедурных языках, а по имени. Еще в 4-й версии, где не было динамических запросов, можно было запустить процедуру так:
declare @c varchar(100) set @c="MyProc" exec @c "param1","param2"
Т.е. при компиляции не известно что за процедура будет выполняться и соответсвенно какие у неё будут параметры. Можно спорить удачное ли это решение или нет, но это никак не говорит о том, что процедуры интерпретируются.
Кстати подобный вызов осуществляется для процедур DLL, но Вы же не скажите, что все программы, которые используют DLL - интерпретаторы. Аналогия не полная, но Вы спокойно можете создать процедуру, которая будет вызывать функцию DLL, которой еще нет. И никаких предупреждений во время компиляции не будет.

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

← →
SergSuper (2003-07-16 15:45) [22]

Чуть добавлю: в компилированном коде стоит запуск процедуры по имени. Т.е. процедура test компилируется, в каком-то бинарном виде вставляются выполнения запросов, в бинарном же виде вставляется команда вызова процедуры, но сама процедура обозначена по её именем, т.е. строкой. При запуске этого компилированного кода выполнить процедуру не получается, а всё остальное работает.

← →
АлексейК (2003-07-17 08:59) [23]

Если бы написать
exec @str ...., где @str varchar ...
то вопросов не возникает, так как компилятор понимает @str это не объект метаданных, это текстовая переменная и по имени нужно будет найти процедуру и выполнить, но когда я пишу
exec kfghfjdhg я обращають к конкретному объекту, который несуществует, следовательно компиляция не должна происходить (код то в базу он сохраняет это номально но не компилировать же его), то есть процедура не должна выполнятся вообще. То есть если вы в Delphi обращаетесть к несуществующей переменной, процедуре, функции, модулю компилятор выдает ошибку и исполняемый код не создает.

При этом в документации по архетиктуре MS SQL пишется что план выполнения перекомпилируетя в случаях, изменений медаданных на которые он ссылается, большого колличетсва добавлений или удалений из таблиц с которыми он работает, или при явном вызове перекомпиляции (если конечно он остался еще в памяти). Следовательно скомпилировав код процедура должна отслеживать изменение процедуры "kfghfjdhg", либо поставить на нее блокировку стабильности схемы. Но этой процедуры не существует.
Если рассматривать команду exec kfghfjdhg как ссылку, где при выполнении основной процедуры происходит поиск по имени процедуры kfghfjdhg выполнение ее кода и выход из нее тогда в приципе понятно. Но все же если на момент компиляции есть возможность проверить наличие объекта, на который ссылается исходный код, и не найдя оного компилировать возможную ошибку, какой смысл? Конечно, я могу создать объект динамически, во время выполнения самой процедуры, но не функции же
create function dbo.test(@value int)
returns int
as
begin
declare @i int
if @value=2
return (2)
set @i=(select top 1 id_sta from jghjfhdgjfhdjk) //селект из несуществующего объекта.
return (@i)
end
В данном случае MS SQL проглатывает код и даже не предупреждает, что jghjfhdgjfhdjk не существует, при этом в функции я не могу создать объект динамически. Функция выполнятеся и возвращает число 2, но ведь она должна компилироваться перед выполением.
set @i=(select top 1 id_sta from jghjfhdgjfhdjk)
Какой код он создат из строки? Если предположить,что он откидывает эту чать кода, дак ведь нет, при введении числа <>2 он пытается выполнить данный код, конечно происходит ошибка.

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

Где с год назад на SQL.ru искал информацию по оптимизации базы данных на MS SQL, жаль сейчас не могу найти, так вот там тоже приводились доводы трасляции T-SQL тогда они показались мне убедительными, нда, а вот сейчас снова встал вопрос.
А на эту статью наткнулся случайно (когда пытался найти информацию с SQL.ru), конечно статья разбавлена вымыслом (но в меру), и явно рекламная, но вообщем описанные там особенности MS SQL имеют место.

← →
Е-Моё имя (2003-07-18 09:34) [24]

> Замедление в 100 раз, это просто мой конкретный пример,
> то есть 0 мс и 100 мс,

а почему в 100 раз?
чтобы получить разы, нужно одно разделить на другое
можно узнать, как разделить 100 на 0 и получить 100?

← →
АлексейК (2003-07-18 09:47) [25]

← →
Е-Моё имя (2003-07-18 10:02) [26]

2 АлексейК (18.07.03 09:47)
гениально! а на каком основании?

← →
АлексейК (2003-07-18 10:25) [27]

← →
Е-Моё имя (2003-07-18 10:58) [28]

2 АлексейК (18.07.03 10:25)

единственный вариант - элементарная безграмотность

Вызов хранимой процедуры в запросе Найти похожие ветки