Здравствуйте, 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>, и проигрываем за счет того же (отсутствия оптимизации).
Сервер, время от времени, пересчитывает статистику. При построении всех планов запросов оптимизатор пользуется результатами этого пересчета. Если статистика для какой-то таблицы поменялась, то из кеша автоматически выкидываются все планы связанные с этой таблицей...
По этой причине и проигрыша никакого нет, даже если все было бы так как ты описываешь.
Запрос из процедуры полюбому перестроится, так как не найдет подходящего плана.