Использование хранимых процедур для повышения производительности

Tsql теория > Использование хранимых процедур для повышения производительности
08.04.2013 11:22:25



Статья:

Использование хранимых процедур для повышения производительности

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

Создание хранимой процедуры начинается с выполнения оператора CREATE PROC. После вызова этого оператора на выполнение осуществляется синтаксический анализ запроса хранимой процедуры, позволяющий убедиться в том, что код запроса приемлем для выполнения. Единственное возникающее при этом различие по сравнению с непосредственным вызовом сценария на выполнение состоит в том, что в операторе CREATE PROC может использоваться так называемое отложенное преобразование имен. При отложенном преобразовании имен игнорируется тот факт, что некоторые объекты, используемые в хранимой процедуре, еще не существуют. Благодаря этому появляется возможность создавать такие объекты позже.

После создания хранимой процедуры в СУБД не предпринимается больше никаких действий до первого вызова хранимой процедуры на выполнение. А после первого вызова осуществляется оптимизация хранимой процедуры, после чего в системе компилируется и кэшируется план запроса. Этот кэшированный план запроса используется при последующих вызовах хранимой процедуры на выполнение (если с помощью опции WITH RECOMPILE не будет указано, что следует поступить иначе), а не создается каждый раз новый план запроса. Это означает, что при каждом последующем вызове хранимой процедуры удается обойтись без значительной части действий по ее оптимизации и компиляции. Точные показатели достигаемой при этом экономии времени зависят от сложности пакета операторов хранимой процедуры, от размеров таблиц, обрабатываемых в этом пакете, и от количества индексов на каждой таблице. Обычно создается впечатление, что экономия ресурсов системы приводит лишь к небольшому сокращению продолжительности выполнения хранимой процедуры (скажем, в большинстве ситуаций такое сокращение может составлять приблизительно одну секунду), но в процентном отношении к исходному времени выполнения такая разница может оказаться весьма значительной. (Сокращение времени на 1 секунду по сравнению с 2 секундами равносильно ускорению на 100%.) Достигнутое ускорение работы может оказаться еще более значительным, если количество вызовов хранимой процедуры велико или если хранимая процедура применяется в цикле.

Предпосылки снижения производительности хранимых процедур

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

Этот применяемый по умолчанию подход к эксплуатации хранимых процедур, в соответствии с которым оптимизация осуществляется однократно, а вызов на выполнение происходит многократно, способствует повышению производительности приложений, в которых используются хранимые процедуры, но имеет свои недостатки. Если запрос является динамическим (т.е. формируется непосредственно перед вызовом его на выполнение с помощью оператора ЕХЕС), то может оказаться, что хранимая процедура была оптимизирована применительно к тем условиям, которые сложились ко времени первого вызова ее на выполнение, притом что в дальнейшем эти условия больше никогда не обнаруживаются. Иными словами, для прогона хранимой процедуры может использоваться неправильный план запроса!

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

¦    идентификатор заказчика;

¦    идентификатор заказа;

¦    идентификатор товара;

¦    дата заказа.

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

Скорее всего, для решения указанной задачи следует принять такой подход, чтобы было заранее подготовлено несколько запросов и осуществлялся выбор подходящего запроса и вызов его на выполнение в зависимости от того, какие критерии заданы пользователем. Таким образом, после первого вызова хранимой процедуры на выполнение в ней обрабатывается ряд операторов IF. . . ELSE, после чего определяется запрос, подходящий для использования. К сожалению, данный запрос подходит только для данного конкретного вызова хранимой процедуры на выполнение (но неизвестно, насколько часто аналогичная ситуация будет складываться в дальнейшем). После этого первого прогона при каждом вызове хранимой процедуры на выполнение будет вызываться другой запрос, подходящий с точки зрения выбранных на сей раз критериев, но по-прежнему будет использоваться план запроса, сформированный перед первым прогоном хранимой процедуры. Иными словами, производительность запроса действительно будет неоптимальной.

Использование опции with recompile

Эксплуатация хранимых процедур может быть организована таким образом, чтобы применялись их преимущества, связанные с защитой и разделением кода, и вместе с тем исключались недостатки, обусловленные использованием заранее откомпилированного кода. Благодаря этому устраняется та проблема, что при выполнении хранимой процедуры не используется правильный план запроса, поскольку перед каждым очередным прогоном хранимой процедуры создается новый план запроса. Для этого используется опция WITH RECOMPILE, которую можно включить в состав кода двумя способами.

Прежде всего, предусмотрена возможность включить опцию WITH RECOMPILE на этапе прогона. Для этого достаточно указать данную опцию в сценарии вызова хранимой процедуры на выполнение:

ЕХЕС spEmployee

WITH RECOMPILE

В этом операторе СУБД SQL Server передаются указания на то, что необходимо отбросить существующий план выполнения и создать новый; таким образом, при указанном способе применения опции WITH RECOMPILE прогон хранимой процедуры с новым планом выполнения осуществляется только один раз.

Кроме того, требование о неизменной перекомпиляции хранимой процедуры можно задать на постоянной основе, включив опцию WITH RECOMPILE непосредственно в код хранимой процедуры. При подобном подходе опция WITH RECOMPILE задается непосредственно перед ключевым словом AS в операторе CREATE PROC или ALTER PROC.

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