Являются ли хранимые процедуры более быстрыми, чем автономные запросы?

Пересказ статьи Bert Wagner. Are Stored Procedures Faster Than Stand-Alone Queries?

Если ответить коротко, то оптимизатор запросов будет рассматривать запрос, определенный в хранимой процедуре точно также, как и непосредственно переданный.

Давайте обсудим почему.

Начнем с плана

Хотя для отправки на SQL Server оператора «EXEC < хранимая процедура >» может потребоваться меньше пакетов сетевого трафика, чем отправка нескольких сотен (или тысяч?) строк, составляющих запрос, встроенный в саму процедуру, здесь и заканчивается эффективность хранимой процедуры.

Замечание. Имеются некоторые функциональные особенности SQL Server, например, кэширование временных объектов, скомпилированные в собственном коде хранимые процедуры (natively compiled stored procedures) для оптимизированных таблиц и т.д., которые улучшат производительность хранимой процедуры по сравнению с автономным запросом. Однако мой опыт говорит, что большинство людей не используют эти особенности; поэтому это спорный момент.

После получения запроса оптимизатор запросов SQL Server рассматривает эти два принятых запроса совершенно одинаково. Он проверит наличие кэшированного плана для любого запроса (и если таковой существует, он использует его), в противном случае он проведет каждый запроса через процесс оптимизации, чтобы найти подходящий план выполнения. Если автономный запрос и запрос, определенный в хранимой процедуре в точности одинаковы, и все другие условия на сервере аналогичны на время исполнения, SQL Server будет генерировать одинаковые планы для обоих запросов.

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

CREATE OR ALTER PROCEDURE dbo.USP_GetUpVotes
	@UserId INT
AS
SELECT  
    COUNT(*) AS UpVotes 
FROM 
	dbo.Posts p
    INNER JOIN Votes v
		ON v.PostId = p.Id 
WHERE 
    p.OwnerUserId = @UserId
	AND VoteTypeId = 2
ORDER BY UpVotes DESC
 
EXEC dbo.USP_GetUpVotes 23
DECLARE @UserId INT = 23
SELECT 
    COUNT(*) AS UpVotes 
FROM 
	dbo.Posts p
    INNER JOIN Votes v
		ON v.PostId = p.Id 
WHERE 
    p.OwnerUserId = @UserId
	AND VoteTypeId = 2
ORDER BY UpVotes DESC


Я не включаю сюда скриншот второго плана, поскольку они идентичны.

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

Но я могу поклясться, что моя хранимая процедура отрабатывает быстрее!

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

Как писал немного ранее, SQL Server очень требователен к неизменности каждой мелочи в запросе, чтобы он мог повторно использовать кэшированный план. Это касается таких вещей, как пробелы и регистр символов.

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

Итак, имеет ли особый смысл в использовании хранимых процедур?

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

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

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

Последнее, и наиболее важное. Использование хранимых процедур дает вашему администратору БД лучшее понимание ваших запросов. Сохранение запроса внутри хранимой процедуры означает, что ваш DBA может легко получить доступ и проанализировать её, предложив способы улучшить процедуру в случае плохой производительности. Если же, напротив, все ваши запросы встроены в приложение, это затрудняет DBA доступ к ним, уменьшая вероятность, что он сможет своевременно помочь вам решить проблемы производительности.

Добавить комментарий