По умолчанию каждый раз после выполнения хранимой процедуры сервер посылает клиентскому приложению сообщение о количестве строк, обработанных процедурой. Это информация очень редко нужна для клиента. Если отключить эту серверную функцию, то можно уменьшить сетевой трафик между сервером и клиентом, увеличить производительность запроса на сервере.
Есть два основных способа отключить функцию. Чтобы сделать это на уровне хранимых процедур необходимо добавить в начало кода процедуры команду SET NOCOUNT ON. Чтобы отключить функцию на уровне сервера необходимо выполнить следующий код:
SP_CONFIGURE 'user options', 512
RECONFIGURE
Это отключение затронет все транзакции на вашем сервере. Однако есть программы, которым нужна информация о количестве обработанных строк, иначе работать они не смогут. В этом случае лучше использовать SET NOCOUNT ON в ваших процедурах, а не отключать функцию посылки сообщения на уровне всего сервера.
Хранимые процедуры могут принимать множество параметров. Но если некоторые из них необязательны, то количество используемых параметров меняется при каждом запуске процедуры. Есть два способа разрешить эту проблему.
- Если вы хотите разработать ваш запрос как можно быстрее, и производительность приложения вас не волнует, то тогда можете писать подобные хранимые процедуры как обычно, не обращая внимания на количество передаваемых параметров. Проблема в том, что возможно вам придется выполнять запросы, объединять таблицы которые на основе переданных параметров объединять не надо.
- Второй способ займет больше времени на разработку, но сможет поднять производительность ваших процедур. Надо включить проверки типа IF...ELSE в ваш код процедуры, создать раздельные запросы для всех возможных комбинаций передаваемых параметров. Тогда вы можете быть уверены в эффективности выполнения процедуры в любом случае.
Если вы создаете хранимую процедуру, которая запускается не из базы Master, то не используйте в ее имени префикс "sp_". Это специальный префикс, зарезервированный для системных процедур. Хотя применение этого префикса не запрещено и ваша процедура с таким именем будет работать, это может сказаться на скорости вызова процедуры. Дело в том, что SQL сервер любую процедуру с префиксом "sp_" пытается найти в базе данных Master. А так как она не там, то происходит задержка вызова. Когда сервер не находит процедуру в базе Master, он пытается найти ее в текущей, предполагая что владелец объекта "dbo". Чтобы таких задержек не возникало - не используйте префикс "sp_" для ваших процедур.
Для выполнения строки запроса Transact-SQL используйте sp_executesql вместо команды EXECUTE. Sp_executesql имеет ряд преимуществ. Во-первых, поддерживает подстановку параметров, что дает возможность гибко работать с кодом. Во-вторых, создает планы исполнения, которые можно использовать повторно. Sp_executesql исполняет строку запроса в своем собственном пакете - сервер компилирует код в отдельный план исполнения.