Тут на днях собеседовался в одной конторе.
У них веб серверное приложение, До-диез, без шаропойнта, сервер баз данных MS SQL.
Я, как умная маша, спрашиваю про то да сё; спрашиваю, используют ли они хранимые процедуры.
Ихний спец отвечает, что мол у них высоко-надёжный сервис, и поэтому они не могут использовать хранимые процедуры. Вдруг, мол, соединение прервётся, или ещё какая напасть.
Я в SQL-ях далеко не гуру. Однако про себя удивился.
В нашем текущем проекте хранимые процедуры не используются по причине погони за 100% переносимостью.
А в остальных случаях, по-моему, с ними куда надёжнее и эффективнее, чем без них.
Или я не прав?
F>В нашем текущем проекте хранимые процедуры не используются по причине погони за 100% переносимостью. F>А в остальных случаях, по-моему, с ними куда надёжнее и эффективнее, чем без них.
Надежность от использования хранимых процедур никак не должна зависеть.
А эффективность, как обычно, зависит не просто от факта использования чего-либо, но и от того, КАК ты это используешь.
Мнение ихнего спеца, конечно, очень странное, если ты правильно его понял и пересказал.
Здравствуйте, filkov, Вы писали:
F>сервер баз данных MS SQL. F>с ними куда надёжнее и эффективнее, чем без них.
На сколько я помню, в mssql (во всяком случае, в 2000-м) план выполнения создается одновременно с созданием самой хранимой процедуры, и никогда больше не перестраивается. То есть, при выполнении процедуры актуальная статистика не учитывается. Так что в эффективности хранимые процедуры проигрывают.
Других минусов — не знаю.
Здравствуйте, mloginov, Вы писали:
F>>с ними куда надёжнее и эффективнее, чем без них. M>На сколько я помню, в mssql (во всяком случае, в 2000-м) план выполнения создается одновременно с созданием самой хранимой процедуры, и никогда больше не перестраивается. То есть, при выполнении процедуры актуальная статистика не учитывается. Так что в эффективности хранимые процедуры проигрывают.
У хранимых процедур нет плана. Он есть у statement-ов. А уж откуда они пришли (из хранимки или от клиента) — не важно.
Здравствуйте, mloginov, Вы писали:
M>На сколько я помню, в mssql (во всяком случае, в 2000-м) план выполнения создается одновременно с созданием самой хранимой процедуры, и никогда больше не перестраивается. То есть, при выполнении процедуры актуальная статистика не учитывается.
Не при создании, а при выполнении, не процедуры, а конкретного запроса, при этом планы при обновлении статистики таки перестраиваются, ну и наконец, для параноиков есть опция WITH RECOMPILE.
M> Так что в эффективности хранимые процедуры проигрывают.
Можно конечно придумать сценарий, чтобы они проигрывали, но обычно хранимки эффективнее.
Здравствуйте, IB, Вы писали:
IB>Здравствуйте, mloginov, Вы писали:
IB>Не при создании, а при выполнении, не процедуры, а конкретного запроса, при этом планы при обновлении статистики таки перестраиваются, ну и наконец, для параноиков есть опция WITH RECOMPILE.
Да, я ошибся. Конечно не при создании, а при первом выполнении процедуры.
IB>Можно конечно придумать сценарий, чтобы они проигрывали, но обычно хранимки эффективнее.
Но за счет чего эффективней? Обычно объясняют так:
When you execute a stored procedure for the first time, the SQL Server query optimizer builds an execution plan for the stored procedure, so that it can run quickly without needing to repeat the parsing, optimizing and compiling steps each time it is executed. Reusing the execution plan is one of the main advantages of using the stored procedures.
Процитировал что первое под руку попалось (выделение мое). Но везде пишут примерно одно и то же. Получается, что мы выигрываем за счет отсутствия повторной компиляции (и оптимизации, замечу), и проигрываем за счет того же (отсутствия оптимизации).
Здравствуйте, mloginov, Вы писали:
M>Но за счет чего эффективней?
за счет того, что весь батч уже находится на сервере, с клиента достаточно передать команду на выполнение.
M>Обычно объясняют так:
Гонят.
M>
M>When you execute a stored procedure for the first time, the SQL Server query optimizer builds an execution plan for the stored procedure, so that it can run quickly without needing to repeat the parsing, optimizing and compiling steps each time it is executed. Reusing the execution plan is one of the main advantages of using the stored procedures.
Нет такого понятия как план для процедуры в целом. Сиквел не умеет компилировать процедуру целиком и никогда не умел. План всегда строится икешируется для каждого запроса в отдельности, не важно из хранимки пришел запрос или нет.
Все T-SQL выражения, которые не имеют отношения к запросам (IF, ect) вообще интерпретируются при каждом выполнении.
Все запросы пришедшие из клиента напрямую, точно также парсятся и компилируются ровно один раз, после чего складываются в кеш в виде плана. Второй запрос сравнивается с первым на точное совпадение и если оно прошло успешно, то просто берется готовый план из кеша. Так было начиная с версии 7.0 точно, а скорее всего и раньше.
В следствии этого возможны ряд забавных эффектов, но это уже выйдет за рамки текущего разговра.
M> Но везде пишут примерно одно и то же.
Не везде, есть пара статей в MSDN-е, где написано как все происходит на самом деле.
Ссылок не дам, лень искать.
Данное же заблуждение проистекает, подозреваю, из-за давней небрежности в BOL, там вообще предпочитают в тонкости не вдаваться и пишут в общих чертах, что заставляет строить догадки не соответствующие действительности
M> Получается, что мы выигрываем за счет отсутствия повторной компиляции (и оптимизации, замечу)
Повторное построение плана отсутствует в любом случае, если второй запрос совпадает с первым, так что тут никакого выигрыша нет.
M>, и проигрываем за счет того же (отсутствия оптимизации).
Сервер, время от времени, пересчитывает статистику. При построении всех планов запросов оптимизатор пользуется результатами этого пересчета. Если статистика для какой-то таблицы поменялась, то из кеша автоматически выкидываются все планы связанные с этой таблицей...
По этой причине и проигрыша никакого нет, даже если все было бы так как ты описываешь.
Запрос из процедуры полюбому перестроится, так как не найдет подходящего плана.
Здравствуйте, IB, Вы писали:
IB>Не везде, есть пара статей в MSDN-е, где написано как все происходит на самом деле. IB>Ссылок не дам, лень искать.
Поискал сам, похоже Вы правы. То, о чем я говорил относилось еще к mssql 6.5. На всякий случай, что бы поставить точку, процитирую MSDN (KB243586):
[msdn]In SQL Server 7.0, a new behavior is introduced that may cause a stored procedure to recompile during execution. This new behavior ensures that the optimizer always has the best possible plan for each specific statement within a procedure. The following events may cause a run-time recompilation of a stored procedure:
A sufficient percentage of data changes in a table that is referenced by the stored procedure.
The procedure interleaves Data Definition Language (DDL) and Data Manipulation Language (DML) operations.
The procedure performs certain operations on temporary tables.[/msdn]
filkov пишет:
> А в остальных случаях, по-моему, с ними куда надёжнее и эффективнее, чем > без них. > Или я не прав?
Ты прав. Точнее — надежность никак не зависит от наличия или отсутствия
процедур, эффективность — в каких -то случаях с использованием SP лучше.
Но кстати тут от СУБД многое зависит. У MSSQL — да, SP могут сильно
увеличить производительность.
mloginov пишет:
> На сколько я помню, в mssql (во всяком случае, в 2000-м) план выполнения > создается одновременно с созданием самой хранимой процедуры, и никогда > больше не перестраивается
IB пишет:
> Нет такого понятия как план для процедуры в целом. Сиквел не умеет > компилировать процедуру целиком и никогда не умел. План всегда строится > икешируется для каждого запроса в отдельности, не важно из хранимки > пришел запрос или нет.
Уж не знаю как там в MS сейчас, но про то, что он "и никогда не умел" —
это уж вы загнули.
> Все запросы пришедшие из клиента напрямую, точно также парсятся и > компилируются ровно один раз, после чего складываются в кеш в виде > плана.
Кэш кончается. Запрос приходит еще раз. Компилируется еще раз.
Здравствуйте, MasterZiv, Вы писали: MZ>Ты прав. Точнее — надежность никак не зависит от наличия или отсутствия MZ>процедур,..
Вот реально интересно, что подразумевалось под надежностью? Упоминалось про возможность разрыва соединения с сервером БД, только, ИМХО, в случае каких-либо проблем скорее "ляжет" веб-сервер, чем сервер БД.
Здравствуйте, MasterZiv, Вы писали:
MZ>Уж не знаю как там в MS сейчас, но про то, что он "и никогда не умел" - MZ>это уж вы загнули.
Никто ничего не загибал.
MZ>Кэш кончается. Запрос приходит еще раз. Компилируется еще раз.
Это же верно и для хранимок, так к чему ты это?
Здравствуйте, mloginov, Вы писали:
M>Здравствуйте, filkov, Вы писали:
M>На сколько я помню, в mssql (во всяком случае, в 2000-м) план выполнения создается одновременно с созданием самой хранимой процедуры, и никогда больше не перестраивается. То есть, при выполнении процедуры актуальная статистика не учитывается. Так что в эффективности хранимые процедуры проигрывают. M>Других минусов — не знаю.
Вот поставить тебе минус — тогда узнаешь. А по существу — уже все сказали.
Здравствуйте, MasterZiv, Вы писали:
MZ>_d_m_ пишет: >> M>Других минусов — не знаю. >> >> Вот поставить тебе минус — тогда узнаешь. А по существу — уже все сказали.
MZ>А мне пофигу на эти ваши минусы. Я за правду!
Я тож здесь не ради оценок. Да и тебе не собирался ставить минус — не заслужил пока. Просто человек, я думаю, отнесется с осторожностью к советам с отрицательной оценкой.
Здравствуйте, filkov, Вы писали:
F>Уважаемые коллеги.
F>Тут на днях собеседовался в одной конторе. F>У них веб серверное приложение, До-диез, без шаропойнта, сервер баз данных MS SQL. F>Я, как умная маша, спрашиваю про то да сё; спрашиваю, используют ли они хранимые процедуры. F>Ихний спец отвечает, что мол у них высоко-надёжный сервис, и поэтому они не могут использовать хранимые процедуры. Вдруг, мол, соединение прервётся, или ещё какая напасть.
А вам не кажется что спец имел ввиду Extended Stored Procedures?
Здравствуйте, mloginov, Вы писали:
M>Здравствуйте, IB, Вы писали:
IB>>Не везде, есть пара статей в MSDN-е, где написано как все происходит на самом деле. IB>>Ссылок не дам, лень искать. M>Поискал сам, похоже Вы правы.
Присоединяюсь! Сам незнал -- буквально глаза открылись, после прочтения MSDN'овских статей по этому поводу.
Но:
1. Эксперименты показывают, что использование ADODB.Command с параметром adCommandStoredProc даёт некторый прирост производительности, против тупого формирования строки вызова процедуры или "сырого" запроса.
2. Хранимки можно (ну с некоторыми магическими пассами) в Source Safe или аналогичные менеджеры исходного кода засунуть.
3. Как никак, а подсветка синтаксиса в банальном QA радует глаз и мозг при переработке больше чем куча разрозненных "серых" кусков SQL кода внутри исходника программы.
4. Расширяемость, переносимость программы улучшается при использовании хранимок. Однажды мне удалось переделать свою прогу с Access на MSSQL без перекомпиляции, а просто переписав хранимки на MSSQL и поменяв DNS.
PS: Всю ветку читать некогда, если сплагиатил, приношу извинения.
Наука изощряет ум; ученье вострит память.
(c) Козьма Прутков
Мы уже победили, просто это еще не так заметно...
