Каковы плюсы и минусы хранения SQL в хранимых процедурах по сравнению с кодом

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

Дополнительные преимущества хранимых процедур:

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

Хранимые процедуры.

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

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

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

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

В настоящее время это обсуждается в нескольких других темах. Я последовательный сторонник хранимых процедур, хотя приводятся несколько хороших аргументов в пользу Linq to Sql.

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

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

[Edit] Вот еще один текущее обсуждение

Advantages for in Code:

• Easier to maintain - don't need to run a SQL script to update queries
• Easier to port to another DB - no procs to port

На самом деле, я думаю, у вас все наоборот. IMHO, SQL в коде сложно поддерживать, потому что:

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

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

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

Одно из предложений сеансов Microsoft TechEd по безопасности, на которых я присутствовал, - выполнять все вызовы через сохраненные процедуры и запрещать доступ непосредственно к таблицам. Этот подход был заявлен как обеспечивающий дополнительную безопасность. Я не уверен, стоит ли это просто из соображений безопасности, но если вы уже используете сохраненные процедуры, это не повредит.

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

Подумайте об этом так

У вас есть 4 веб-сервера и несколько приложений для Windows, которые используют один и тот же код SQL. Теперь вы поняли, что есть небольшая проблема с кодом SQl. так ты скорее ...... поменять процедуру в 1 месте или же отправьте код на все веб-серверы, переустановите все настольные приложения (щелчок может помочь) во всех окнах

Я предпочитаю хранимые процессы

Также проще провести тестирование производительности с помощью процедуры, поместив ее в анализатор запросов. установить статистику io / время на установите showplan_text и вуаля

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

только мои 2 цента

Вы перечисляете 2 про-очка для спрока:

Производительность - не совсем. В Sql 2000 или выше оптимизация плана запроса довольно хороша и кэшируется. Я уверен, что Oracle и т.д. делают аналогичные вещи. Я не думаю, что sprocs больше подходят для повышения производительности.

Безопасность? Почему бы sprocs быть более безопасными? Если в любом случае у вас нет довольно незащищенной базы данных, весь доступ будет осуществляться от ваших администраторов баз данных или через ваше приложение. Всегда параметризуйте все запросы - никогда не вставляйте что-либо из пользовательского ввода, и все будет в порядке.

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

Linq определенно является тем способом, которым я бы сейчас занялся новым проектом. См. Этот похожий пост.

Я падаю на сторону код. Мы создаем уровень доступа к данным, который используется всеми приложениями (как веб-, так и клиентскими), так что с этой точки зрения он СУХОЙ. Это упрощает развертывание базы данных, потому что нам просто нужно убедиться, что схема таблицы верна. Это упрощает обслуживание кода, поскольку нам не нужно смотреть на исходный код и базу данных.

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

Преимущества хранимых процедур:

Более простой анализ кода.

Менее связаны, поэтому их легче тестировать.

Настраивается более легко.

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

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

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

Недостатки:

Разработчикам сложнее управлять: контроль версий скриптов: у каждого есть своя база данных, интегрирована ли система контроля версий с базой данных и IDE?

Хранимые процедуры поддерживаются БОЛЕЕ, потому что:

• Вам не нужно перекомпилировать приложение C# всякий раз, когда вы хотите изменить какой-либо SQL
• Вы в конечном итоге повторно используете код SQL.

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

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

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

Easier to port to another DB - no procs to port

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

@Keith

Security? Why would sprocs be more secure?

Хранимые процедуры обеспечивают внутреннюю защиту от атак SQL-инъекция.

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

@Keith

Security? Why would sprocs be more secure?

Как предлагает Komradekatz, вы можете запретить доступ к таблицам (для комбинации имени пользователя и пароля, которая подключается к базе данных) и разрешить доступ только к SP. Таким образом, если кто-то получит имя пользователя и пароль к вашей базе данных, он сможет выполнять SP, но не сможет получить доступ к таблицам или любой другой части БД.

(Конечно, выполнение sprocs может предоставить им все необходимые данные, но это будет зависеть от доступных sprocs. Предоставление им доступа к таблицам дает им доступ ко всему.)

@Terrapin - sprocs так же уязвимы для инъекционных атак. Как я сказал:

Always parametrise all queries - never inline something from user input and you'll be fine.

Это касается sprocs и динамического Sql.

Я не уверен, что отсутствие перекомпиляции вашего приложения является преимуществом. Я имею в виду, что вы выполнили свои модульные тесты для этого кода (как приложения, так и БД), прежде чем снова выйти в эфир.

@Guy - да, вы правы, sprocs позволяет вам контролировать пользователей приложения, чтобы они могли выполнять только sproc, а не основное действие.

Мой вопрос: если весь доступ к нему осуществляется через ваше приложение, используя подключения и пользователей с ограниченными правами на обновление / вставку и т. д., Добавляет ли этот дополнительный уровень безопасность или дополнительное администрирование?

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

Инъекции SQL по-прежнему могут выполняться для этих sprocs, если они динамически встроены в код, поэтому золотое правило по-прежнему применяется: весь пользовательский ввод всегда должен быть параметризован.

Я не фанат хранимых процедур

Stored Procedures are MORE maintainable because: * You don't have to recompile your C# app whenever you want to change some SQL

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

• You end up reusing SQL code.

В языках программирования, включая C#, есть такая удивительная вещь, которая называется функцией. Это означает, что вы можете вызывать один и тот же блок кода из разных мест! Удивительный! Затем вы можете поместить повторно используемый код SQL внутрь одного из них или, если вы хотите получить действительно высокие технологии, вы можете использовать библиотеку, которая сделает это за вас. Я считаю, что они называются объектно-реляционными картографами и в наши дни довольно распространены.

Code repetition is the worst thing you can do when you're trying to build a maintainable application!

Согласен, поэтому хранимые процессы - это плохо. Намного проще провести рефакторинг и разложить (разбить на более мелкие части) код на функции, чем SQL на ... блоки SQL?

You have 4 webservers and a bunch of windows apps which use the same SQL code Now you realized there is a small problem with the SQl code so do you rather...... change the proc in 1 place or push the code to all the webservers, reinstall all the desktop apps(clickonce might help) on all the windows boxes

Почему ваши приложения для Windows подключаются напрямую к центральной базе данных? Это похоже на ОГРОМНУЮ дыру в безопасности и узкое место, поскольку оно исключает кеширование на стороне сервера. Разве они не должны подключаться через веб-службу или аналогично вашим веб-серверам?

Итак, подтолкнуть 1 новый sproc или 4 новых веб-сервера?

В этом случае является легче протолкнуть один новый sproc, но, по моему опыту, 95% «проталкиваемых изменений» влияют на код, а не на базу данных. Если в этом месяце вы отправляете 20 элементов на веб-серверы и 1 в базу данных, вы вряд ли много потеряете, если вместо этого отправите 21 элемент на веб-серверы и ноль в базу данных.

More easily code reviewed.

Вы можете объяснить как? Я этого не понимаю. В частности, учитывая, что sprocs, вероятно, не находятся в системе управления версиями, и поэтому к ним нельзя получить доступ через веб-браузеры SCM и так далее.

Еще минусы:

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

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

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

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

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

Атаки с использованием SQL-инъекций набирают обороты. Кто-то может легко найти этот код и запустить инъекционные атаки на ваш сайт. Вы всегда должны параметризовать свои запросы всегда. Лучше никогда не запускать exec (@x) для динамических запросов SQL. Это просто не лучшая идея когда-либо использовать встроенный SQL, IMO.

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

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

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

Большой поклонник Transact SQL, настройка больших запросов оказалась для меня очень полезной. Я не писал ни одного встроенного SQL около 6 лет!

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

Можно было бы возразить, что это просто переносит некоторую обработку с SQL на веб-сервер, но в целом это было бы хорошо.

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

То, о чем я пока не упоминал: люди, лучше всего разбирающиеся в базе данных, не всегда пишут код приложения. Хранимые процедуры дают специалистам по базам данных возможность взаимодействовать с программистами, которые на самом деле не хотят так много разбираться в SQL. Большие - и особенно устаревшие - базы данных - не самая простая вещь для полного понимания, поэтому программисты могут просто предпочесть простой интерфейс, который дает им то, что им нужно: пусть администраторы баз данных выяснят, как объединить 17 таблиц, чтобы это произошло.

При этом языки, используемые для написания хранимых процедур (печально известный пример PL / SQL), довольно жестокие. Обычно они не предлагают никаких тонкостей, которые можно было бы увидеть в популярных сегодня императивных, ООП или функциональных языках. Подумайте о КОБОЛЕ.

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

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

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

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

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

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

ПРОТИВ

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

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

ЗА

1. Производительность того, чего она может стоить (позволяет избежать синтаксического анализа запросов драйвером БД / планом воссоздания и т. д.)
2. Обработка данных не встроена в код C / C++ / C#, что означает, что мне нужно просматривать меньше кода низкого уровня. SQL менее подробен и легче просматривается, если он указан отдельно.
3. Благодаря разделению люди могут намного проще находить и повторно использовать код SQL.
4. При изменении схемы проще что-то изменить - вам просто нужно дать тот же вывод коду, и он будет работать нормально.
5. Легче перенести в другую базу данных.
6. Я могу перечислить отдельные разрешения для моих хранимых процедур и управлять доступом на этом уровне.
7. Я могу профилировать свой код запроса / сохранения данных отдельно от кода преобразования данных.
8. Я могу реализовать изменяемые условия в своей хранимой процедуре, и ее будет легко настроить на сайте клиента.
9. Становится проще использовать некоторые автоматизированные инструменты для преобразования моей схемы и операторов вместе, чем когда они встроены в мой код, где мне придется их выслеживать.
10. Обеспечение передовых методов доступа к данным проще, если у вас есть весь код доступа к данным в одном файле - я могу проверить запросы, которые обращаются к неэффективной таблице или те, которые используют более высокий уровень сериализации, или выбрать * в коде и т. д.
11. Когда все это перечислено в одном файле, становится легче найти изменения схемы / изменения логики манипулирования данными.
12. Становится проще выполнять поиск и заменять правки в SQL, когда они находятся в одном и том же месте, например. изменить / добавить операторы изоляции транзакции для всех хранимых процессов.
13. Я и администратор баз данных находим, что иметь отдельный файл SQL проще / удобнее, когда администратору базы данных необходимо просмотреть мои данные по SQL.
14. Наконец, вам не нужно беспокоиться об атаках с использованием SQL-инъекций, потому что какой-то ленивый член вашей команды не использовал параметризованные запросы при использовании встроенных sqls.

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

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

Аргумент безопасности ложный. Он просто переносит проблему безопасности из приложения в базу данных. Код есть код. Я видел хранимые процедуры, которые принимают SQL от приложений и используют его для построения запросов, которые подвергались атакам с использованием SQL-инъекций.

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

Использование богатых библиотек генерации SQL, таких как LINQ, Rails ActiveRecord или Hibernate / NHibernate, ускоряет разработку. Добавление хранимых процедур в микс замедляет его работу.

Меньшие журналы

Еще одно незначительное преимущество хранимых процедур, о котором не упоминалось: когда дело доходит до трафика SQL, доступ к данным на основе sp генерирует на много меньше трафика. Это становится важным, когда вы отслеживаете трафик для анализа и профилирования - журналы будут намного меньше и удобочитаемы.

Я твердо на стороне хранимых процедур, предполагая, что вы не обманываете и не используете динамический SQL в хранимых процессах. Во-первых, использование хранимых процедур позволяет dba устанавливать разрешения на уровне хранимых процедур, а не на уровне таблиц. Это важно не только для борьбы с атаками SQL-инъекций, но и для предотвращения прямого доступа к базе данных внутренними лицами и внесения изменений. Это способ предотвратить мошенничество. Никакая база данных, которая содержит личную информацию (SSN, номера кредитных карт и т. д.) Или которая каким-либо образом создает финансовые транзакции, никогда не должна быть доступна, кроме как через строгие процедуры. Если вы используете какой-либо другой метод, вы оставляете свою базу данных широко открытой для лиц в компании для создания поддельных финансовых транзакций или кражи данных, которые могут быть использованы для кражи личных данных.

Сохраненные процессы также намного проще поддерживать и настраивать производительность, чем SQL, отправляемый из приложения. Они также позволяют БД увидеть, какое влияние структурное изменение базы данных окажет на способ доступа к данным. Я никогда не встречал хорошего dba, который разрешал бы динамический доступ к базе данных.

Я предпочитаю использовать O / R Mapper, например LLBLGen Pro.

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

На самом деле, возможность работать со строго типизированными объектами - достаточная причина для использования O / R Mapper.

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

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

Это сохраняет наш код и наши вызовы sql в одном элементе управления версиями, не «забывая» запускать некоторые внешние приложения для обновления.

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

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

На мой взгляд, по этому вопросу нельзя голосовать «да» или «нет». Это полностью зависит от дизайна вашего приложения.

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

(Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

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

Если у вас есть толстый клиент с графическим интерфейсом, который напрямую подключается к вашей БД или, может быть, даже к большему количеству приложений, это другая история. В этой ситуации SP могут служить своего рода псевдосредним уровнем, который отделяет ваше приложение от модели данных и предлагает контролируемый доступ.

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

В более ранних ответах много говорилось о преимуществах безопасности хранимых процедур. Они делятся на две большие категории:

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

2) SQL-инъекция / параметризованные запросы. Это возражение - отвлекающий маневр. Встроенный SQL - даже динамически сгенерированный встроенный SQL - может быть так же полностью параметризован, как и любой хранимый процесс, и это можно сделать так же легко на любом современном языке, достойном его соли. В любом случае здесь нет преимущества. («Ленивые разработчики могут не беспокоиться об использовании параметров» - недопустимое возражение. Если в вашей команде есть разработчики, которые предпочитают просто объединять пользовательские данные в свой SQL вместо использования параметров, вы сначала пытаетесь их обучить, а затем увольняете если это не сработает, как если бы вы поступили с разработчиками, у которых есть другие плохие, явно пагубные привычки.)

Мой голос за хранимые процедуры; как уровень абстракции, близкий к данным, эффективный с наборами, многократно используемый многими «клиентами» (клиентские языки). Язык T-SQL немного примитивен (и я думаю, это то, чему подвергалось большинство разработчиков C# здесь, в SO), но PL / SQL Oracle не уступает любому современному языку программирования.

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

Мы используем хранимые процедуры с базами данных Oracle, где я работаю сейчас. Мы также используем Subversion. Все хранимые процедуры создаются как файлы .pkb и .pks и сохраняются в Subversion. Раньше я выполнял встроенный SQL, и это больно! Я предпочитаю, как мы это делаем здесь. Создавать и тестировать новые хранимые процедуры намного проще, чем делать это в вашем коде.

Это

Плюсы хранимых процедур 1). Повышенная безопасность, поскольку SQL в хранимой процедуре статичен по своей природе (в основном). Это защитит от SQL-инъекции. 2). Возможность повторного использования. Если необходимо вернуть одни и те же данные для нескольких приложений / компонентов, это может быть лучшим выбором вместо повторения операторов SQL. 3). Уменьшает количество вызовов между клиентом и сервером базы данных.

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

Твердо придерживайтесь принципа «Хранимые процессы плохо подходят для использования CRUD / бизнес-логики». Я понимаю необходимость отчетности, импорта данных и т. д.

Напишите здесь ...

Для Microsoft SQL Server вы должны использовать хранимые процедуры везде, где это возможно, чтобы помочь с кэшированием и повторным использованием плана выполнения. Почему вы хотите оптимизировать повторное использование плана? Потому что создание планов выполнения обходится довольно дорого.

Хотя кэширование и повторное использование планов выполнения для специальных запросов значительно улучшилось в более поздних выпусках SQL-сервера (особенно 2005 и 2008 гг.), По-прежнему существует гораздо меньше проблем с повторным использованием планов при работе с хранимыми процедурами, чем для специальных запросов. . Например, SQL-сервер будет повторно использовать план выполнения только в том случае, если текст плана соответствует точно - вплоть до комментариев и пробелов, например, если каждая из следующих строк SQL должна выполняться независимо, ни одна из них не будет использовать тот же план выполнения:

SELECT MyColumn FROM MyTable WHERE id = @id
select MyColumn from MyTable WHERE id = @id
SELECT MyColumn  FROM MyTable WHERE id = @id
SELECT MyColumn FROM MyTable WHERE id = @id -- "some comment"
SELECT MyColumn FROM MyTable WHERE id = @id -- "some other comment"

Вдобавок ко всему, если вы явно не укажете типы своих параметров, тогда есть большая вероятность, что SQL Server может ошибиться, например, если вы выполнили вышеуказанный запрос с вводом 4, тогда SQL Server параметризует запрос с @id в качестве SMALLINT (или, возможно, TINYINT), и поэтому, если вы затем выполните тот же запрос с @id, скажем, 4000, SQL Server параметризует его как INT и не будет повторно использовать тот же кеш.

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

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

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

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

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

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

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

Выполнять специальные запросы с хранимыми процедурами неудобно - это проще сделать с динамическим sql (или ORM), что может быть самым большим недостатком использования хранимых процедур для меня.

С другой стороны, хранимые процедуры удобны в ситуациях, когда вы вносите изменения, но не требует повторного развертывания кода приложения. Это также позволяет вам формировать ваши данные перед их отправкой по сети, где в коде sql может потребоваться несколько вызовов для извлечения, а не для их формирования (хотя теперь есть способы запускать несколько операторов sql и возвращать несколько наборов результатов в одном "вызове"). ", как в MARS в ADO.NET), что, вероятно, приведет к тому, что через вашу сеть будет передаваться больше данных.

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

Я не большой поклонник хранимых процедур, но использую их в одном условии:

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

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

Но, черт возьми, ими так ужасно управлять. Я избегаю их, насколько могу.

Вероятно, ваш язык программирования и структура приложения:

• высокий уровень, особенно по сравнению с SQL
• легко редактировать и развертывать с помощью автоматизированных процессов, особенно по сравнению с SQL

Если этих двух условий два, пропустите хранимые процедуры.

Сохраненная процедура SQL не увеличивает производительность запроса

Один момент, который я не нашел в других ответах, заключается в следующем:

Если в вашей среде база данных и ее схема являются сердцем архитектуры, а приложения играют более вспомогательную роль, тогда может иметь смысл более интенсивно использовать хранимые процедуры, которые могут помочь обеспечить базу уровня для всех приложений, которым необходим доступ. БД и, таким образом, вызовет меньшее повторение кода (например, уверены ли вы, что все ваши приложения для доступа к БД всегда будут написаны на C# или других языках .NET?).

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

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

Очевидно, что использование хранимых процедур имеет несколько преимуществ по сравнению с построением SQL в коде.

1. Реализация вашего кода и SQL становятся независимыми друг от друга.
2. Код легче читать.
3. Пишите один раз используйте много раз.
4. Изменить один раз
5. Нет необходимости сообщать программисту внутреннюю информацию о базе данных. и т. д. и т. д.

Самым большим преимуществом sproc в том месте, где я работаю, является то, что у нас будет намного меньше кода для переноса на VB.NET (из VB6), когда придет время. И это НАМНОГО меньше кода, потому что мы используем sprocs для всех наших запросов.

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

Никто не упомянул модульное тестирование!

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