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

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

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

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

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

дата заказа.

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

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

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

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

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

EXEC spEmployee WITH RECOMPILE

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

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

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

Расширенные хранимые процедуры

После того как СУБД SQL Server была встроена в инфраструктуру .NET, область применения расширенных хранимых процедур существенно изменилась. В свое время без расширенных хранимых процедур невозможно было обойтись при создании сценариев, наиболее тесно связанных с внутренним функционированием базы данных и применяемых в таких ситуациях, когда простые конструкции T-SQL и другие обычные средства СУБД SQL Server не позволяют решить поставленную задачу.

Но с тех пор как СУБД SQL Server вошла в состав инфраструктуры .NET, позволяющей легко решать задачи обеспечения доступа к файлам операционной системы, поддерживать взаимодействие с другими внешними системами или реализовывать сложные алгоритмы, необходимость в использовании расширенных хранимых процедур в значительность степени снизилась. Безусловно, расширенные хранимые процедуры еще сохранили свою крошечную нишу, поскольку иногда предъявляются столь жесткие требования к производительности, что ее может обеспечить только код, работающий непосредственно под управлением СУБД SQL Server, но в эпоху повсеместного распространения инфраструктуры .NET даже этот способ повышения производительности кажется слишком радикальным.

Краткие сведения об использовании рекурсии

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

Краткое определение понятия рекурсии состоит в том, что это - метод вызова кода из самого этого кода. Анализ данного определения показывает, какая опасность возникает при использовании рекурсивного кода, т.е. кода, который вызывает сам себя. Ведь если такой вызов произошел один раз, то что может предотвратить осуществление этого вызова снова и снова, до бесконечности? Ответ заключается в том, что все зависит от программиста. Иными словами, необходимо позаботиться о том, чтобы в рекурсивном вызываемом коде была предусмотрена проверка условия рекурсии для обеспечения выхода из цепочки рекурсивных вызовов после того, как в этом возникнет необходимость.

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

В этом классическом примере используются факториалы. Факториалом целого числа называется произведение натуральных чисел от единицы до какого-либо натурального числа. Например, факториал числа 5, который обозначается как 5i, равен 120, т.е. 5*4*3*2*1.

Рассмотрим следуюп1ую реализацию алгоритма вычисления факториала в виде рекурсивной хранимой процедуры:

CREATE PROC spFactorial ©ValueIn int, ©ValueOut int OUTPUT AS

DECLARE ©InWorking int DECLARE ©Outworking int IF ©Valuein != 1 BEGIN

SELECT ©InWorking = ©Valuein - 1

EXEC spFactorial ©InWorking, ©Outworking OUTPUT

SELECT ©ValueOut = ©Valuein * ©Outworking

ELSE

BEGIN

SELECT ©ValueOut = 1

RETURN GO

Итак, рассматриваемая хранимая процедура должна принимать в качестве параметра одно целочисленное значение (число, факториал которого должен быть вычислен) и возвращать другое целочисленное значение (значение вычисленного факториала). Изучение этой процедуры вначале вызывает удивление, поскольку в ней, на первый взгляд, нет ничего, что позволило бы вычислить значение факториала за один шаг. Вместо этого в ней просто берется один сомножитель произведения, из которого составляется значение факториала, к нему применяется одна операция, после чего хранимая процедура вызывает сама себя. В следующем вызове снова обрабатывается только одно числовое значение и хранимая процедура снова вызывает сама себя. Такая цепочка вызовов может продолжаться вплоть до достижения глубины рекурсии, равной 32 уровням рекурсии. Но после того как СУБД SQL Server достигает глубины в 32 уровня, активизируется ошибка и обработка прекращается.

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