Добрый день.

Есть сторед процедура в MySQL

DROP PROCEDURE IF EXISTS `test`.`b_insert` $$
CREATE PROCEDURE `test`.`b_insert` (IN aName TEXT,
IN aCountry TEXT)
BEGIN
DECLARE country TEXT;
call b_getCountryID(@countryID,aCountry);
set country=@countryID;
insert into testTable(name, idCoutry) values(aName, country);
END $$
DELIMITER ; 

 Когда я ее вызываю процедуру b_insert из какого-либо клиента MySQL(MySQL Command Line Client, MySQL Query Browser), то внутрення процедура b_getCountryID возвращает нужное значение. Если же я вызываю b_insert из сервела то процедура b_getCountryID возвращает NULL. Где собака порылась? Или как можно решить данную проблему?

Заранее спасибо

Видимо, если у вас b_getCountryID возвращает неправильное значение, то надо на неё и смотреть, а не на b_insert.

К тому же, вопрос, видимо в компетенции не этого, а вот этого форума..

Как раз вопроса к MySQL у меня нет процедура b_getCountryID возвращает нужное значение (Поэтому считаю что мой вопрос в компетенции все-таки этого форума). Просто когда вызываю b_insert из java сервлета то b_getCountryID возвращает NULL.


Но всеравно спасибо

Не за что. 

Вот вам система из двух уравнений:

1. процедура b_getCountryID возвращает нужное значение.
2. из java сервлета b_getCountryID возвращает NULL.

Вывод из приведенной совокупности ваших утверждений только один: NULL - это нужное значение. 

Хотя что-то подсказывает мне, что вас понимать следует таким образом, что b_getCountryID не всегда возвращает правильное значение, так? А если это так, то и разбираться нужно с ней, а не с той процедурой, которая её использует, согласитесь. 

) 
Ну вывод не правильный так как процедура b_getCountryID у меня в принципе не может вернуть NULL. Понимать следует именно так как я написал еще впервом сообщении. Т.е. при абсолютно одинакових  входящих параметрах если я пишу в MySQL Command Line Client к примеру call b_insert('Something','Russia'); Все работает отлично. В джава сервлете при вызове процедуры call b_insert('Something','Russia'). в таблицу testTable ничего не записывается по т.к. b_getCountryID возвращает NULL.

Вот код процедуры b_getCountryID


а вот содержание таблицы country 


P.S. Я конечно решил этот вопрос методом топора, т.е. в Java сервлете я изначально вызвал процедуру  b_getCountryID, потом b_insert и в нее я передал то что вернула мне b_getCountryID. Сейчас все работает, но при этом мне всеравно интересно в чем проблема?

Я бы вам посоветовал рассмотреть вопрос о переходе с вызовов хранимых процедур на запросы вида:

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

Что до NULL - он (а) всегда вставляется в testTable при вызове b_insert, (б) только при вызове b_insert из сервлета, но всегда, либо (в) произвольно?

Если Я бы на вашем месте для начала попытался избавиться от @-переменной.

А как может вызов запроса через JDBC быть более эффективным чем вызов вложенной процедуры, которая уже откомпилирована на сервере и следственно выполняется быстрее обычного запроса?

Кстати, а такая процедура не работает?



А такая вроде бы вообще должна возвращать значение подобно функции:


Но лучше для этой конкретной цели использовать все-таки SQL-выражение..

За MySQL не стану ручаться, но по аналогии с Oracle - запрос компилируется так же однократно, потом сидит в кеше. При этом, для данного типа запроса у него с большой вероятностью уже и данные в кеше готовые лежат, к справочнику-то часто обращаются, а вот процедуру всё равно придётся выполнить - оптимизатор не знает какова логика процедуры.
Плюс небольшие затратах на вызов процедуры в т.ч. инициализацию пакета, проверку привелегий и т.д., она ведь исполняется с правами своего владельца. 
Но главное, при использовании обращения к справочнику по странам в качестве подзапроса, оптимизатор запросов, работая с запросом ( в д.с. INSERT, но сложный SELECT был бы лучшим примером), будет его как нетрудно догадаться, оптимизировать - исходя из статистики таблиц к которым обращается запрос, наличия индексов, способа связывания и т.п. Он будет выбирать по каким индексам каких участвующих таблиц осуществлять внешние циклы, а по каким внутренние, да и по индексам ли вообще это делать. Заменяя подзапросы вызовами хранимых процедур вы лишите оптимизатор возможности выбора стратегии. 

Не совсем. Это было бы справедливо, если бы мы использовали PreparedStatement, и биндили его переменные. Если же мы будем втупую "склеивать" запрос, то он будет заново парситься, составляться план и т.д.

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

И еще одно хотелось бы заметить - борьба за переносимость SQL запросов между серверами баз данных, это вообще крайне порочное занятие изначально. 

п.с. простите искреен за офтоп. зацепило почему-то 

Не так. SQL-движок Oracle ничего не знает про JDBCшный PreparedStatement. Он получает select чего-то там через соединение, берет от него хеш и смотрит во внутренних структурах - а не завалялся ли там уже такой запрос и если нашел - берет адрес уже готовой структуры. Ну и с планом, вроде такая же фигня - во вью v$sql бывает можно встретить одинаковые запросы, с идентичными хешами, что меня заставляет думать, что они отличаются контекстом построения запроса (optimizer_env), но специально не интересовался, может и не по этому.

Биндинг переменных - естественно, по умолчанию. Это и безопаснее всегда, и в случае с Oracle, запросы должны быть идентичны, что бы их хеши сошлись.

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



Как должна отрабатывать база подобный запрос? Пойти перебирать людей сверяясь каждый раз с их страной и по ней с континентом, вызывая нужные функции?

Но ведь у нас 7 миллиардов людей сосредоточено в 200 странах и худо-бедно индексы существуют, и их селективность известна оптимизатору. По этому он найдет единственную страну на континенте Австралия и вытащит ветку индекса таблицы персон, содержащую указатели на персоны там живущие, и будет выбирать с диска блоки данных, содержащие только эти персоны, а не лопатить миллиарды записей персон.

Ситуация может быть и иная - может не быть некоторых индексов, данные могут лежать очень разреженно, у вас в базе может быть не 7 миллиардов людей, а 300 сотрудников из этих 200 стран - в каждом случае оптимизатор выберет наиболее выигрышную стратегию - как с наименьшими затратами выбрать для вас интересующий набор данных. 

И MySQL не может быть исключением. Он же тоже из каких-то соображений строит план запроса, использует те или иные индексы? Но как только вы начинаете пользоваться функциями - всё, оптимизация идёт лесом. 



Но нет смысла городить сложности на пустом месте. В специальных случаях, когда не работает общее решение - никто не мешает его оговорить особо -  перерекрыть общий случай, работающий везде своими настройками, подогнанными под частный случай. Так и надо разрабатывать софт, ведь дело не только в переносимости между RDBMS, дело в переносимости продукта между разными клиентами, сидящими под одной и той же версией RDBMS. У всех разные "обстоятельства" в базе и продукт должен быть готов быть немного подкручен под конкретный случай. Под одним заказчиком сидеть прикажете? Так он вас же и купит в итоге 



Теперь оправдываться поздно. Посмотри на это небо, эти звёзды..

Ээээ... А в чем же тут противоречие с моими словами?

1. PreparedStatement ни при чем
2. Случай с параметрами, вписанными внутрь запроса просто не рассматривается

А в остальном - всё так, конечно 

Не понял я твою мысль, ну да ладно. Не существенно.

А у меня гораздо больше сомнений вызывает что переданный извне запрос будет выполняться быстрей чем в теле процедуры. Допускаю что в лучшем случае скорость будет одинаковой. У вас есть какие-нибудь материалы доказывающие вашу теорию? Интересно было бы почитать.

А вы не обратили внимания, что в посте, из которого вы процитировали одну фразу, присутствует ещё некая совокупность букв, посвященная раскрытию темы - почему именно я не поверю?   

Это сообщение отредактировал(а) 3x3 - 2.10.2006, 15:10

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

Хм.. Я в свою очередь не понимаю, как вы себе представляете работу оптимизатора в случае, если запрос, приведенный мною в качестве примера, будет подбирать ID стран и континентов посредством вызова функций:


Как вы ему предложите ЭТО оптимизировать? Он просто станет вызывать эти функции для всех семи миллиардов землян и шерстить в них страны и континенты. 

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

Продолжая наш пример:
В мире живёт примерно 7 миллиардов человек (ну, миллиард туда, миллиард сюда - не принципиально).
Население Австралии - что-то около 20 миллионов, что составляет 0.3% от общего количества людей.

В среднем же селективность индекса по странам, которых 200 = 0.5%, очень хорошая избирательность, проиндексируем персон по стране.

Соответственно оптимизатор знает какие существуют индексы (в жизни он ещё оценивает и сколько стоит по ним ходить, ведь данные разреживаются с годами: кто-то мрёт, кто-то наоборот).

И когда мы просим SQL-engine: "дай нам всех мужиков с австралийского континента" она в состоянии решить что правильнее: перебирать семь миллиардов записей персон или 6 континентов, 34 страны (в среднем на континент) и двадцать миллионов персон.

Но стоит вам сказать SQL-engine: "дай нам всех мужиков, у которых ID страны, совпадёт с ID, которой вернет фукция вызванная для этой записи" - и всё. БД пойдёт перебирать миллиарды землян и для каждого вызывать функцию и проверять - совпадает ли возвращенный ID с тем, что забито в записи этого пользователя. 

ЗЫ
Не без исключений, конечно. В частности при выборке по Азии будет скорее всего выгоднее ходить по персонам: одних китайцев - каждый пятый, какой блок с диска ни считаешь - не ошибёшься, в нём уже китаец сидеть будет 

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

На SQL было в предшествующих постах, а это перевод выражения на русский язык 

А про джаву - забудьте. Совершенно непринципиально через какой API вы будете дергать лисэнер БД. Это базу не волнует и на её скорость не влияет.

Ладно, будем абстрагироваться. У нас есть один и тотже SQL запрос со следующими параметрами

В одном случае мы вызываем его в теле хранимой процедуры, во втором извне, через API предоставляемое сервером баз данных, либо через поставляемую вместе с ним утилиту. Теперь можно узнать - почему при запуске SQL запроса из хранимой процедуры оптимизатор запускаться не будет, а во втором случае будет? 

1. Оптимизатор будет вызываться всегда, какой бы вы ему запрос не скормили (оставим за скобками оракловые особенности)
2. Разницы в том, как будет выполняться целый запрос: из хранимой процедуры или из вью или из клиента пришедши - естественно, практически нет.

3x3, ну если разницы нет, почему же вы утверждаете что хранимые процедуры менее эффективны по производительности, чем внешние вызовы? Только потому что требуется проверка прав доступа? Время на инициализацию?

Это вы так интерпретировали моё высказывание: "Никогда в жизни не поверю, что вызов процедуры может ускорить запрос" или можете привести ссылку на высказывание о том, "что хранимые процедуры менее эффективны по производительности, чем внешние вызовы"? 

Запросто

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

Начнём.

С чем из отквоченного вами вы лично не согласны и почему?

boroda, привел текст процедуры в котором был следующий вызов

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

Дальше по моей просьбе вы приводите доказательство своей мысли. Я ставлю его под сомнение, так как мне непонятно почему "Заменяя подзапросы вызовами хранимых процедур вы лишите оптимизатор возможности выбора стратегии.". Дальше мы приходим к выводу что вызов SQL запроса происходит практически одинаково как из хранимой процедуры, так и из внешнего приложения. На основе этого я делаю утверждение, что вы признали, что внешние SQL запросы не могут быть лучшими по производительности, чем хранимые процедуры   

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




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



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

PS
Кстати, с точки зрения сопровождения БД 24х7 будет любопытно взглянуть как вы в работающей базе будете при необходимости апдейтить две функции, из которых одна вызывает другую - ну мало ли что там поменялось ;)


PPS
А у вас аватар не просто так выбран? По SunOS можно обратиться с вопросиком?
 




Это сообщение отредактировал(а) 3x3 - 2.10.2006, 22:40

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

База:


Вызывающий код:


Как видим, четыре разных запроса по-разному вставляют строку:
0: insert into xtest select ?, c_id from xcountry where c_name=? 
  - простой запрос, который я рекомендовал
1: insert into xtest values(?, xget_country_id(?))
  - запрос извлекающий ИД страны вызовом функции
2: call xinsert_test(?,?)
  - процедура, идущая по пути автора треда и вызывающая функцию для получения ИДа
3: call xinsert_test2(?,?)
  - Такой же запрос, как и в случае 0, только зачем-то упакованный в процедуру

Результат теста:

При включенном автокоммите:

Test started for 1000 rows per pass
  Pass 0 query 0 passed for 26438 ms
  Pass 0 query 1 passed for 29344 ms
  Pass 0 query 2 passed for 30265 ms
  Pass 0 query 3 passed for 28360 ms
  Pass 1 query 0 passed for 27109 ms
  Pass 1 query 1 passed for 30047 ms
  Pass 1 query 2 passed for 30172 ms
  Pass 1 query 3 passed for 28015 ms
  Pass 2 query 0 passed for 28875 ms
  Pass 2 query 1 passed for 30125 ms
  Pass 2 query 2 passed for 29797 ms
  Pass 2 query 3 passed for 27172 ms
  Pass 3 query 0 passed for 30344 ms
  Pass 3 query 1 passed for 30141 ms
  Pass 3 query 2 passed for 29218 ms
  Pass 3 query 3 passed for 27860 ms
Test finished


Выключаю автокоммит

Test started for 1000 rows per pass
  Pass 0 query 0 passed for 281 ms
  Pass 0 query 1 passed for 187 ms
  Pass 0 query 2 passed for 188 ms
  Pass 0 query 3 passed for 172 ms
  Pass 1 query 0 passed for 140 ms
  Pass 1 query 1 passed for 172 ms
  Pass 1 query 2 passed for 188 ms
  Pass 1 query 3 passed for 156 ms
  Pass 2 query 0 passed for 156 ms
  Pass 2 query 1 passed for 157 ms
  Pass 2 query 2 passed for 203 ms
  Pass 2 query 3 passed for 156 ms
  Pass 3 query 0 passed for 156 ms
  Pass 3 query 1 passed for 406 ms
  Pass 3 query 2 passed for 235 ms
  Pass 3 query 3 passed for 156 ms
Test finished

Быстровато, увеличил количество инсертов

Test started for 100000 rows per pass
  Pass 0 query 0 passed for 12046 ms
  Pass 0 query 1 passed for 12563 ms
  Pass 0 query 2 passed for 13812 ms
  Pass 0 query 3 passed for 12703 ms
  Pass 1 query 0 passed for 11953 ms
  Pass 1 query 1 passed for 12907 ms
  Pass 1 query 2 passed for 16171 ms
  Pass 1 query 3 passed for 12891 ms
  Pass 2 query 0 passed for 12125 ms
  Pass 2 query 1 passed for 12891 ms
  Pass 2 query 2 passed for 16093 ms
  Pass 2 query 3 passed for 13047 ms
  Pass 3 query 0 passed for 11657 ms
  Pass 3 query 1 passed for 13203 ms
  Pass 3 query 2 passed for 15968 ms
  Pass 3 query 3 passed for 13032 ms
Test finished

Добавил DETERMINISTIC в определение функции

Test started for 100000 rows per pass
  Pass 0 query 0 passed for 11875 ms
  Pass 0 query 1 passed for 12594 ms
  Pass 0 query 2 passed for 13812 ms
  Pass 0 query 3 passed for 12922 ms
  Pass 1 query 0 passed for 11766 ms
  Pass 1 query 1 passed for 12906 ms
  Pass 1 query 2 passed for 14406 ms
  Pass 1 query 3 passed for 12781 ms
  Pass 2 query 0 passed for 11828 ms
  Pass 2 query 1 passed for 13032 ms
  Pass 2 query 2 passed for 16297 ms
  Pass 2 query 3 passed for 12421 ms
  Pass 3 query 0 passed for 12141 ms
  Pass 3 query 1 passed for 13156 ms
  Pass 3 query 2 passed for 14266 ms
  Pass 3 query 3 passed for 12844 ms
Test finished


ЧТД

PS

На всякий случай, статус базы:


SSL:                    Not in use
Using delimiter:        ;
Server version:         5.0.22-community-max-nt
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    utf8
Db     characterset:    utf8
Client characterset:    utf8
Conn.  characterset:    utf8
TCP port:               3306
Uptime:                 23 hours 36 min 42 sec


Это сообщение отредактировал(а) 3x3 - 3.10.2006, 03:34

Можно, правда я с ней не работал    Работал только с FreeBSD и Linux. Аватар выбран не случайно - он соотвествует моему нику в этом форуме.

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

А, ну ладно тогда, я в соответствующем разделе спросил..



Поздравляю. Утро вечера мудренее.



Не люблю этот синтаксис, и не знаю что он такого даёт в повседневном использовании. Мне больше нравится когда таблицы в запросе рядом через запятую перечислены  

Такой запрос отрабатывает более быстро чем если вы укажите через запятую или как вложенный запрос.

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

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

Добавлено @ 12:04 

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





Ну теперь мне уже и самому интересно стало

Нельзя слишком доверять оптимизаторам. Как показывает практика, запросы с перечисленными через запятую таблицами отрабатывают медленней и уж тем более вложенные запросы, чем объединенные через JOIN. Особенно на больших объёмах.