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

It also depends on your audience. Is ease of installation and portability across DBMSs important to you?

If your program should be easy to install and easy to run on different database systems then you should stay away from stored procedures and also look out for non-portable SQL in your code.

Если вы говорите о бизнес-логике, а не просто "Стоит ли вообще использовать sprocs"

Конкретный сценарий, который вы, вероятно, выиграете, включает ситуацию вокруг проблемы масштабируемости "(n + 1)". Любой вид многомерной / иерархической ситуации, вероятно, будет включать этот сценарий.

Другой сценарий может включать случаи использования, когда он использует какой-либо протокол при обработке таблиц (подсказка: определенные шаги, которые могут быть задействованы транзакциями), это может выиграть от местонахождение ссылки: находясь на сервере, запросы могут быть полезны. OTOH, вы можете передать пакет операторов прямо на сервер. Особенно, когда вы работаете в среде XA и вам нужен доступ к федеративным базам данных.

Я всегда использую хранимые процедуры. Лично я считаю, что это упрощает обслуживание. Затем следует обратить внимание на безопасность и производительность.

Просто убедитесь, что вы пишете чистые, хорошо продуманные и хорошо документированные хранимые процедуры.

Помимо соображений скорости и безопасности, я стараюсь как можно больше придерживаться хранимых процедур для простоты обслуживания и внесения изменений. Если вы поместите логику в свое приложение и позже обнаружите, что логика sql имеет ошибку или должна работать по-другому, во многих случаях вам придется перекомпилировать и повторно развернуть все приложение (особенно если это клиентское приложение, такое как WPF , Win-Forms и т. Д.). Если вы сохраните логику в сохраненной процедуре, все, что вам нужно сделать, это обновить процедуру, и вам никогда не придется прикасаться к приложению.

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

] Это включает:

• Заполнение нескольких таблиц из одного источника строк
• Проверка нескольких таблиц на соответствие различным бизнес-правилам
• Выполнение операций, которые невозможно эффективно выполнить с использованием подхода на основе наборов

и т. Д.

Основная проблема с Хранимые процедуры - это то, что их трудно поддерживать.

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

У меня есть статья об этом в моем блоге:

• Мусор схемы

У меня был очень плохой опыт с этим.

Я не против использования хранимых процедур на их месте, но безвозмездное использование хранимых процедур может быть очень дорогостоящим.

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

Во-вторых, не очень просто написать приложение исключительно в хранимых процедурах, хотя я столкнулся с такой, которая приложила нечеловеческие усилия, чтобы попытаться это сделать. Таким образом, вы получаете то, что дорого поддерживать: это реализовано на 2 разных языках программирования, и исходный код также часто не находится в одном месте, поскольку хранимые процедуры окончательно хранятся в СУБД, а не в исходном архиве. Предполагая, что кто-то когда-либо управлял / беспокоился о том, чтобы вытащить их из сервера базы данных и вообще заархивировать исходный код.

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

С другой стороны, хранимые процедуры чрезвычайно полезны, ЕСЛИ:

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

2. Вам нужна производительность, которая может быть достигнута только путем выполнения логики на самом сервере базы данных, а не в качестве клиента. Но, как я уже сказал, когда вы это делаете, вы съедаете все системные ресурсы сервера СУБД. Поэтому вам следует убедиться, что при наличии значительных битов ошибочной операции, которые МОЖНО передать клиентам, вы можете отделить их и оставить самые важные элементы для сервера СУБД.

When all the code is in a stored proc, it is far easier to refactor the database when needed. Changes to logic are far easier to push as well. It is also far far easier to performance tune and sooner or later performance tuning becomes necessary for most database applications.

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

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

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

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

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

Рассмотрим Кому в любом случае нужны хранимые процедуры? :

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

и Почему я не использую хранимые процедуры : ​​

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

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

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

Я использовал сохраненные процедуры в 1 из 3 сценариев:

Скорость Когда скорость имеет первостепенное значение, хранимые процедуры представляют собой отличный метод

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

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

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

Раньше одной из причин была безопасность. Однако с LINQ и другими ORM операции DAL на уровне кода стали намного безопаснее, чем они были в прошлом. Сохраненные процедуры ЯВЛЯЮТСЯ безопасными, но такие ORM, как LINQ, тоже.

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

Очень простой пример:

registerUser(username, password)

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

Конечно, разные люди будут иметь разные точки зрения (администратор баз данных или программист).

Я сказал это в комментарии, но я собираюсь повторить это здесь.

Безопасность, Безопасность, БЕЗОПАСНОСТЬ .

Когда sql-код встроен в ваш приложения, вы должны предоставить прямой доступ к базовым таблицам. Это может сначала звучать нормально. Пока вы не столкнетесь с некоторой SQL-инъекцией, которая скремблирует все поля varchar в вашей базе данных.

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

Я хочу сказать, все современные базы данных имеют встроенную систему безопасности. Вы можете просто запретить прямой доступ к таблице (выбор, вставка, обновление и удаление) и заставить все пройти через ваши s'procs. При этом общие атаки больше не будут работать. Вместо этого злоумышленнику придется потратить время, чтобы изучить интимные детали вашей системы. Это увеличивает их "стоимость" с точки зрения затраченного времени и предотвращает атаки "проезжающих мимо" и "червей".

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

Наконец, что касается идеи не использовать s'procs, потому что вам, возможно, придется переносить на другой rdbms: во-первых, большинство приложений не используют t изменить серверы баз данных. Во-вторых, если это реально, вам все равно придется кодировать с использованием ANSI sql; что вы можете сделать в своих процессах. В-третьих, вам придется переоценить весь свой sql-код, несмотря ни на что, и это намного проще, когда этот код находится в одном месте. В-четвертых, все современные базы данных теперь поддерживают s'procs. В-пятых, при использовании s'proc вы можете настроить свой sql для базы данных, в которой он работает, чтобы воспользоваться преимуществами sql-расширений этой конкретной базы данных.

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

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

Это полностью зависит от вашей среды. Ответ на этот вопрос на самом деле не проблема кодирования или даже не проблема анализа, а бизнес-решение.

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

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

1. В корпоративной базе данных актив является ценным, а неверные данные или действия могут иметь опасные для бизнеса последствия. Ваша основная забота - это защита бизнеса, а не то, насколько удобен доступ для ваших кодировщиков.
2. Такие базы данных по определению доступны более чем одному приложению. Вам необходимо использовать абстракцию, предлагаемую хранимыми процедурами, чтобы база данных могла быть изменена при обновлении приложения A и у вас нет ресурсов для обновления приложения B.
3. Точно так же инкапсуляция бизнес-логики в SP, а не в код приложения, позволяет более легко и надежно реализовать изменения такой логики в рамках всего бизнеса, чем если бы такая логика была встроена в код приложения. Например, если расчет налога изменяется, это меньше работы и становится более надежным, если расчет необходимо изменить в одном SP, чем в нескольких приложениях. Эмпирическое правило здесь заключается в том, что бизнес-правило должно быть реализовано в ближайшей точке к данным, где оно уникально, поэтому, если у вас есть специализированное приложение, логика для этого приложения может быть реализована в этом приложении, но логика более широко применима. для бизнеса должны быть реализованы в ИП.

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