Обратите внимание на то, что локальные переменные являются именно таковыми - локальными по отношению к каждой хранимой процед}е. Например, в процессе работы может быть создано пять различных копий переменной ©MyDate, по одной для каждого из пяти различных вызовов хранимой процедуры, и все эти Экземпляры переменной будут независимыми друг от друга.

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

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

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

Предоставление пользователям возможности непосредственно подключаться к производственной базе данных с помощью такой программы, как Access или Excel, позволяет, с одной стороны, раскрыть для них в системе невероятно привлекательные возможности, а с другой - создать предпосылки исключительно серьезных ошибок. Чем больше прав приобретают пользователи, тем больше увеличивается объем потребляемых ресурсов, а также продолжительность выполняемых запросов. (Вполне естественно, что при этом пользователей мало волнует то, какие при этом возшжают отрицательные последствия, связанные с работой самой системы.)

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

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

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

Рассмотрим вопрос о том, от каких факторов зависит при этом производительность. Для этого в качестве иллюстрации воспользуемся показанной на рис. 11.1 схемой действий, происходящих при создании хранимой процедуры.

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

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

Время создания

Добавлено к таблицам:

sysobjects

sysdepends

syscomments

Во время выполнения

Первое выполнение или перекомпиляция

Не первое выполнение, но не находится в кэше

Оптимизирована и компилирована

Не первое выполнение и находится в кэше

Добавлена к кэшу процедур в памяти

Рис. 11.1. Действия, осущестеляемые при создании хранимой процедуры

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

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

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