Когда я должен использовать хранимые процедуры вместо того, чтобы просто писать логику непосредственно в моем приложении? Я хотел бы воспользоваться преимуществами хранимых процедур, но также хотел бы, чтобы моя прикладная логика не распространялась на базу данных и приложение.
Есть ли какие-либо эмпирические правила, о которых вы можете думать в этом отношении?
Вау... Я собираюсь прямо поплавать против нынешнего здесь и сказать "почти всегда". Есть список причин из соображений, некоторые из которых я уверен, что другие будут спорить. Но я разработал приложения как с использованием, так и без использования хранимых procs в качестве уровня доступа к данным, и мой опыт заключался в том, что хорошо написанные хранимые процедуры упрощают запись вашего приложения. Тогда есть хорошо документированные характеристики производительности и безопасности.
Это полностью зависит от вашей среды. Ответ на вопрос действительно не проблема кодирования, или даже проблема анализа, а бизнес-решение.
Если ваша база данных поддерживает только одно приложение и достаточно жестко интегрирована с ней, то из соображений гибкости лучше поместить свою логику в свою прикладную программу. В этих обстоятельствах обращение с базой данных просто как простой репозиторий данных с использованием общей функциональности теряет мало и получает гибкость - с поставщиками, внедрением, развертыванием и многое другое - и многие из аргументов пуриста, которые толпу "базы данных для данных" делают демонстративно правда.
С другой стороны, если вы работаете с корпоративной базой данных, которая обычно может быть идентифицирована с помощью нескольких путей доступа к ней, то настоятельно рекомендуется завинтить систему безопасности, насколько это возможно. По крайней мере, все соответствующие ограничения должны быть включены, и, если возможно, доступ к данным должен осуществляться только через представления и процедуры. Программисты Whining следует игнорировать в этих случаях, поскольку...
Кодеры, которые погружаются в религиозные войны за использование или нет SP, как правило, работали только в одной среде или другой, поэтому они экстраполируют свой ограниченный опыт на чугунное положение, которое действительно будет вполне оправданным и правильным в контексте из которого они приходят, но пропускает большую картину. Как всегда, вы должны принять решение о потребностях бизнеса/клиентов/пользователей, а не о том, какой тип методологии кодирования вы предпочитаете.
Я сказал это в комментарии, но я собираюсь сказать это снова здесь.
Безопасность, безопасность, безопасность.
Когда код sql встроен в ваше приложение, вам необходимо предоставить доступ к базовым таблицам для прямого доступа. Сначала это может звучать нормально. Пока вам не удастся добавить какую-нибудь SQL-инъекцию, которая скремблирует все поля varchar в вашей базе данных.
Некоторые люди могут сказать, что они обошли это, используя магические кавычки или какой-либо другой способ надлежащего выхода из их встроенного sql. Проблема, тем не менее, в том, что один запрос не удалось избежать. Или, разработчик, который забыл не разрешать загрузку кода. Или веб-сервер, который был взломан, что позволило злоумышленнику загрузить код. Или... ты понимаешь. Трудно охватить все ваши базы.
Я хочу сказать, что все современные базы данных имеют встроенную защиту. Вы можете просто запретить прямой доступ к таблице (выбирать, вставлять, обновлять и удалять) и заставлять все проходить через ваши s'procs. Делая это, общие атаки больше не будут работать. Вместо этого злоумышленник должен будет потратить время, чтобы изучить интимные детали вашей системы. Это увеличивает их "стоимость" с точки зрения времени, затраченного и останавливающего прохождение и червявые атаки.
Я знаю, что мы не можем обезопасить себя от всего, но если вы потратите время на то, чтобы архитектовать свои приложения, чтобы затраты на его взломы намного перевешивали преимущества, тогда вы серьезно уменьшите свой потенциал потери данных. Это означает использование всех доступных инструментов безопасности.
Наконец, что касается идеи не использовать s'procs, потому что вам, возможно, придется переносить на другой rdbms: во-первых, большинство приложений не меняют серверы баз данных. Во-вторых, в случае, если это реальная возможность, вы должны в любом случае использовать код ANSI sql; которые вы можете сделать в своих процессах. В-третьих, вам придется переоценить весь свой код sql, несмотря ни на что, и это намного проще, если этот код находится в одном месте. В-четвертых, все современные базы данных теперь поддерживают s'procs. В-пятых, при использовании s'proc вы можете настраивать свой sql для базы данных, в которой он работает, чтобы воспользоваться преимуществами этих SQL-расширений базы данных.
Я стараюсь избегать хранимых процедур. Инструменты отладки, как правило, более примитивны. Отчеты об ошибках могут быть более сложными (по сравнению с файлом журнала сервера), и, по крайней мере, мне кажется, что это просто добавляет другой язык без реального выигрыша.
Бывают случаи, когда это может быть полезно, особенно при обработке больших объемов данных на сервере и, конечно же, для триггеров базы данных, которые вы не можете сделать в коде.
Кроме этого, я, как правило, делаю все в коде и рассматриваю базу данных как большой дамп данных, а не то, что я запускаю код.
Рассмотрим Кому нужны хранимые процедуры, Anyways?:
Для современных баз данных и реального мира сценарии использования, я считаю, что сохраненный Архитектура процедуры имеет серьезные недостатки и мало практические выгода. Хранимые процедуры должны быть рассматриваемый язык сборки базы данных: для использования только в самой производительности критические ситуации.
и Почему я не использую хранимые процедуры:
Абсолютное худшее, что вы можете сделать, и это ужасающе распространено в Мир разработки Microsoft - это разделять связанные функции между sproc и код среднего уровня. Grrrrrrrr. Вы просто делаете код хрупкий, и вы увеличиваете интеллектуальные накладные расходы на понимание системы.
В основном, когда вам нужно выполнять операции с данными, которые не нужны для выхода из базы данных. Например, вы хотите обновить одну таблицу данными от другой, нет смысла извлекать данные и обратно, если вы можете сделать все это одним выстрелом в db.
Другая ситуация, когда может быть приемлемой для использования хранимых процедур, - это когда вы на 100% уверены, что никогда не будете развертывать свое приложение у другого поставщика базы данных. Если вы являетесь магазином Oracle, и у вас много приложений, работающих с одной и той же базой данных, может иметь смысл хранить хранимые процедуры, чтобы убедиться, что все они говорят с db согласованным образом.
Сложные запросы к базе данных для меня, как правило, заканчиваются как хранимые процедуры. Другая мысль, которую следует учитывать, заключается в том, что ваша база данных может быть полностью отдельной и отличной от приложения. Допустим, вы запустили Oracle DB, и вы, по сути, создаете API для других разработчиков приложений в вашей организации. Вы можете скрыть сложный материал от них и предоставить сохраненную копию на своем месте.
Очень простой пример:
registerUser(username, password)
может закончиться выполнение нескольких разных запросов (проверьте, существует ли это, создавать записи в таблице предпочтений и т.д.), и вы можете их инкапсулировать.
Конечно, разные люди будут иметь разные перспективы (DBA против программиста).
Я использовал хранимые procs в 1 из 3 сценариев:
Speed Когда скорость имеет первостепенное значение, хранимые процедуры обеспечивают отличный метод.
Сложность Когда я обновляю несколько таблиц, и логика кода может измениться по дороге, я могу обновить сохраненный процесс и избежать перекомпиляции. Хранимые процедуры - отличный метод черного ящика для обновления большого количества данных за один такт.
Сделки Когда я работаю с вставкой, удаляю или обновляю, это охватывает несколько таблиц. Я переношу все это в транзакцию. Если есть ошибка, очень легко отменить транзакцию и выбросить ошибку, чтобы избежать повреждения данных.
Дно 2 очень полезно в коде. Тем не менее, хранимые процедуры предоставляют метод "черного ящика" для работы, когда важны сложные операции и операции уровня транзакции. В противном случае, придерживайтесь операций базы данных уровня кода.
Безопасность была одной из причин. Однако, с LINQ и другими ORM там, операции DAL уровня кода намного более безопасны, чем они были в прошлом. Сохраненные procs являются безопасными, но ORM как LINQ.
Он также может быть очень полезен как вопрос инкапсуляции, так и в философии DRY. Например, я использую хранимые функции для вычислений внутри таблицы, которые мне нужны для нескольких запросов внутри кода. Таким образом, я использую лучшую производительность, а также гарантирую, что расчет всегда выполняется одинаково.
Я бы не использовал его для более высокой функциональности или логики, который должен быть на уровне бизнес-логики архитектуры, но сосредоточен на уровне модели, где функциональность четко ориентирована на дизайн базы данных и возможную гибкость изменения дизайна базы данных без нарушения API для других слоев.
Мы используем хранимые процедуры для всех наших потребностей в отчетах. Они могут обычно извлекать данные быстрее и таким образом, чтобы отчет мог просто выплюнуть прямо, вместо того, чтобы делать какие-либо вычисления или подобные.
Мы также будем использовать хранимые процедуры для сложных или сложных запросов, которые нам нужно сделать, что было бы трудно прочитать, если бы они были внутри нашей базы кода.
Я обычно использую хранимые процедуры. Лично я считаю, что это упрощает обслуживание. Тогда есть соображения безопасности и производительности.
Просто убедитесь, что вы пишете чистые, хорошо выложенные и хорошо документированные хранимые процедуры.
Когда весь код находится в сохраненном proc, гораздо проще реорганизовать базу данных по мере необходимости. Изменения в логике намного легче продвигать. Кроме того, намного легче настроить производительность, и рано или поздно настройка производительности становится необходимой для большинства приложений баз данных.
В дополнение к соображениям скорости и безопасности я стараюсь придерживаться как можно большего количества хранимых процедур для удобства обслуживания и изменений. Если вы поместите логику в свое приложение и обнаружите, что в этой логике sql есть ошибка или она должна работать по-разному, вам придется перекомпилировать и повторно развернуть целое приложение во многих случаях (особенно если это приложение на стороне клиента, такое как WPF, Win-формы и т.д.). Если вы сохраняете логику в сохраненной процедуре, все, что вам нужно сделать, это обновить proc, и вам никогда не придется прикасаться к приложению.
Хранимые процедуры - это метод сбора операций , которые должны выполняться вместе на стороне базы данных, но сохраняя их на стороне базы данных.
Это включает в себя:
и др.
Основная проблема с хранимыми процедурами заключается в том, что их трудно поддерживать.
Таким образом, вы должны сделать хранимые процедуры такими же простыми в обслуживании, как и весь ваш другой код.
У меня есть статья об этом в моем блоге:
У меня был очень плохой опыт с этим.
Я не против использования хранимых процедур вместо них, но бесплатное использование хранимых процедур может быть очень дорогостоящим.
Сначала хранимые процедуры выполняются на сервере базы данных. Это означает, что если у вас есть многосерверная среда с 50 веб-серверами и одним сервером базы данных, вместо того, чтобы распространять рабочую нагрузку на более 50 дешевых машин, вы загружаете один дорогой (поскольку сервер базы данных обычно создается как супертяжелый сервер). И вы рискуете создать одноточечный отказ.
Во-вторых, нелегко написать приложение исключительно в хранимых процедурах, хотя я столкнулся с тем, что делало сверхчеловеческие усилия, чтобы попытаться. Таким образом, вы получаете что-то дорогое в обслуживании: оно реализовано на двух разных языках программирования, а исходный код часто не все в одном месте, поскольку хранимые процедуры окончательно хранятся в СУБД, а не в исходном архиве. Предполагая, что кто-то когда-либо управлял/беспокоился, вытащил их из сервера базы данных и вообще архивировал их.
Таким образом, помимо довольно грязной архитектуры приложений, вы также ограничите набор квалифицированных шимпанзе, которые могут его поддерживать, поскольку требуется несколько навыков.
С другой стороны, хранимые процедуры чрезвычайно полезны, IF:
Вам необходимо поддерживать некоторую целостность данных в разных системах. То есть хранимая логика не принадлежит ни одному отдельному приложению, но вам необходимо последовательное поведение всех участвующих приложений. Определенное количество этого почти неизбежно в современных приложениях в виде внешних ключей и триггеров, но изредка может быть гарантировано значительное редактирование и валидация.
Вам нужна производительность, которая может быть достигнута только за счет запуска логики на сервере базы данных, а не в качестве клиента. Но, как я уже сказал, когда вы это делаете, вы едите в общие системные ресурсы сервера СУБД. Поэтому вам следует убедиться, что если есть значимые биты операции о нарушении, которые могут быть выгружены на клиентов, вы можете их разделить и оставить наиболее важные вещи для сервера СУБД.
Конкретный сценарий, который вы, вероятно, выиграете, включает ситуацию вокруг проблемы масштабируемости (n + 1). Любой вид многомерной/иерархической ситуации, вероятно, будет включать этот сценарий.
Другой сценарий будет включать в себя варианты использования, когда он обрабатывает некоторые протоколы при обработке таблиц (подсказка: определенные шаги, в которых могут быть задействованы транзакции), это может извлечь выгоду из локальности ссылки: находясь на сервере, запросы могут пригодиться. OTOH, вы можете предоставить пакет отчетов непосредственно на сервер. Особенно, когда вы находитесь в среде XA, и вам приходится обращаться к федеративным базам данных.
Если вы говорите бизнес-логику, а не просто "Должен ли я использовать sprocs вообще", я бы сказал, что вы должны поместить бизнес-логику в sprocs, когда выполняете большие операции на основе набора или в любое другое время, выполняющее логику, потребует большой количество вызовов в db из приложения.
Это также зависит от вашей аудитории. Является ли удобство установки и переносимости между СУБД важными для вас?
Если ваша программа должна быть простой в установке и простой в использовании в разных системах баз данных, вы должны держаться подальше от хранимых процедур, а также следить за непереносимостью SQL в вашем коде.