Что лучше: специальные запросы или хранимые процедуры?
Предполагая, что вы не можете использовать LINQ по какой-либо причине, лучше ли размещать ваши запросы в хранимых процедурах, или это так же хорошо, как выполнять специальные запросы к базе данных (скажем, SQL Server для аргумента)?
22 ответа
Из моего опыта написания в основном приложений WinForms Client/Server я пришел к простым выводам:
Используйте хранимые процедуры:
- Для любой сложной работы данных. Если вы собираетесь делать что-то действительно требующее курсора или временных таблиц, обычно это быстрее всего сделать в SQL Server.
- Когда вам нужно заблокировать доступ к данным. Если вы не предоставляете пользователям доступ к таблицам (или роли, или что-то еще), вы можете быть уверены, что единственный способ взаимодействия с данными - это созданные вами SP.
Используйте специальные запросы:
- Для CRUD, когда вам не нужно ограничивать доступ к данным (или делаете это по-другому).
- Для простых поисков. Создание SP для набора критериев поиска - это боль и трудно поддерживать. Если вы можете генерировать достаточно быстрый поисковый запрос, используйте это.
В большинстве своих приложений я использовал как SP, так и ad-hoc sql, хотя я обнаружил, что использую SP все реже и реже, так как они в конечном итоге становятся кодом, подобным C#, только сложнее для контроля версий, тестирования и сопровождения. Я бы порекомендовал использовать ad-hoc sql, если вы не можете найти конкретную причину не делать этого.
Я не могу говорить ни с чем, кроме SQL Server, но аргумент производительности там недействителен, если только вы не используете 6.5 или более раннюю версию. SQL Server кэширует специальные планы выполнения уже около десяти лет.
Я думаю, что это основной конфликт между людьми, которые должны поддерживать базу данных, и людьми, которые разрабатывают пользовательские интерфейсы.
Как человек, работающий с данными, я бы не стал работать с базой данных, доступ к которой осуществляется с помощью специальных запросов, поскольку их сложно эффективно настроить или управлять ими. Как я могу знать, что повлияет на изменение схемы? Кроме того, я не думаю, что пользователям когда-либо следует предоставлять прямой доступ к таблицам базы данных по соображениям безопасности (и я имею в виду не только атаки с использованием SQL-инъекций, но и потому, что это базовый внутренний контроль, который не допускает прямых прав и требует от всех пользователей используйте только процедуры, предназначенные для приложения. Это сделано для предотвращения возможного мошенничества. Любая финансовая система, которая позволяет напрямую вставлять, обновлять или удалять права на таблицы, несет в себе огромный риск мошенничества. Это плохо.).
Базы данных не являются объектно-ориентированными, и код, который кажется хорошим с объектно-ориентированной точки зрения, может быть чрезвычайно плохим с точки зрения базы данных.
Наши разработчики говорят нам, что они рады, что весь наш доступ к базе данных осуществляется через procs, потому что это позволяет намного быстрее исправить ошибку, связанную с данными, а затем просто запустить proc в производственной среде, а не создавать новую ветвь кода и перекомпилировать и перезагрузить в производство. Мы требуем, чтобы все наши процессы были в подрывной деятельности, поэтому контроль над исходным кодом вообще не является проблемой. Если его нет в Subversion, он будет периодически отбрасываться дбассом, поэтому нет сопротивления использованию Source Control.
Хранимые процедуры представляют собой программный контракт, который включает в себя действия, предпринятые с базой данных. Код в процедурах и даже схема самой базы данных могут быть изменены, не затрагивая скомпилированный, развернутый код, так что входные и выходные данные процедуры остаются неизменными.
Встраивая запросы в свое приложение, вы тесно привязываетесь к своей модели данных.
По той же причине нецелесообразно просто создавать хранимые процедуры, которые являются просто запросами CRUD для каждой таблицы в вашей базе данных, поскольку это все еще тесно связано. Вместо этого процедуры должны быть громоздкими, крупнозернистыми операциями.
С точки зрения безопасности рекомендуется запрещать db_datareader и db_datawriter из вашего приложения и разрешать доступ только к хранимым процедурам.
Хранимые процедуры - определенно лучший способ... они скомпилированы, заранее имеют план выполнения, и вы можете управлять ими.
Я не понимаю всю эту проблему контроля над исходным кодом хранимой процедуры. Вы определенно можете контролировать их, если только вы немного дисциплинированы.
Всегда начинайте с файла.sql, который является источником вашей хранимой процедуры. Включите его в систему контроля версий после написания кода. В следующий раз, когда вы захотите отредактировать хранимую процедуру, получите ее из системы контроля версий, а не из базы данных. Если вы будете следовать этому, у вас будет такой же хороший контроль исходного кода, как и у вашего кода.
Я хотел бы процитировать Тома Кайта из Oracle здесь... Вот его правило о том, где писать код... хотя и немного не связанное, но полезно знать, наверное.
- Начните с хранимых процедур в PL/SQL...
- Если вы думаете, что что-то нельзя сделать с помощью хранимой процедуры в PL/SQL, используйте хранимую процедуру Java.
- Если вы думаете, что что-то не может быть сделано с помощью хранимой процедуры Java, рассмотрите Pro * c.
- Если вы думаете, что не можете достичь чего-то с помощью Pro*C, вы можете переосмыслить то, что вам нужно сделать.
Мой ответ из другого поста: Хранимые процедуры ПОДРОБНЕЕ, потому что:
- Вам не нужно перекомпилировать ваше приложение на C# всякий раз, когда вы хотите изменить SQL
- В итоге вы снова используете код SQL.
Повторение кода - худшее, что вы можете сделать, когда пытаетесь создать поддерживаемое приложение!
Что происходит, когда вы обнаружите логическую ошибку, которую необходимо исправить в нескольких местах? Вы более склонны забыть изменить последнее место, куда вы копировали и вставляли свой код.
На мой взгляд, повышение производительности и безопасности является дополнительным плюсом. Вы по-прежнему можете писать небезопасные / неэффективные хранимые процедуры SQL.
Проще портировать на другую БД - без процов на порт
Нетрудно написать все хранимые процедуры для создания в другой БД. На самом деле - это проще, чем экспортировать таблицы, потому что нет первичных / внешних ключей, о которых нужно беспокоиться.
В нашем приложении есть слой кода, который обеспечивает содержание запроса (и иногда это вызов хранимой процедуры). Это позволяет нам:
- легко иметь все запросы под контролем версий
- сделать какие-либо изменения, необходимые для каждого запроса для разных серверов баз данных
- исключает повторение одного и того же кода запроса через наш код
Контроль доступа реализован на среднем уровне, а не в базе данных, поэтому нам не нужны хранимые процедуры там. В некотором смысле это промежуточный путь между специальными запросами и хранимыми процессами.
Для обоих есть убедительные аргументы - все хранимые процедуры расположены в центральном репозитории, но (потенциально) их сложно перенести, а специальные запросы легче отлаживать, как и в вашем коде, но их также сложнее найти в код.
Аргумент, что хранимые процедуры более эффективны, больше не выдерживает критики. текст ссылки
Использование Google для хранимых процедур и динамических запросов покажет достойные аргументы в любом случае, и, вероятно, вам лучше принять собственное решение...
Процедуры хранилища должны использоваться как можно чаще, если вы пишете SQL в код, вы уже настраиваетесь на головные боли в будущем. Написание SPROC занимает примерно столько же времени, сколько и на запись в коде.
Рассмотрим запрос, который отлично работает при средней нагрузке, но как только он переходит в полную занятость, ваш плохо оптимизированный запрос забивает систему и приводит к ее ползанию. На большинстве SQL-серверов вы не единственное приложение / служба, которое его использует. Ваше приложение принесло кучу злых людей к вашей двери.
Если у вас есть запросы в SPROC, вы также позволяете своему дружественному администратору баз данных управлять и оптимизировать без перекомпиляции или взлома вашего приложения. Помните, что администраторы баз данных являются экспертами в этой области, они знают, что делать, а что нет. Имеет смысл использовать их большие знания!
РЕДАКТИРОВАТЬ: кто-то сказал, что перекомпилировать это ленивый оправдание! да, давайте посмотрим, насколько лениво вы себя чувствуете, когда вам нужно перекомпилировать и развернуть свое приложение на тысячах рабочих столов, и все это потому, что администратор БД сказал вам, что ваш специальный запрос потребляет слишком много времени на сервере!
кто-то сказал, что перекомпилировать это ленивый оправдание! да, давайте посмотрим, насколько лениво вы себя чувствуете, когда вам нужно перекомпилировать и развернуть свое приложение на тысячах рабочих столов, и все это потому, что администратор БД сказал вам, что ваш специальный запрос потребляет слишком много времени на сервере!
Это хорошая системная архитектура, если вы позволите подключить 1000 рабочих столов непосредственно к базе данных?
Здесь есть о чем подумать: кому нужны хранимые процедуры?
Очевидно, что это вопрос ваших собственных потребностей и предпочтений, но при использовании специальных запросов в общедоступной среде важно учитывать одну очень важную вещь. Всегда параметризируйте их и следите за типичными уязвимостями, такими как атаки с использованием SQL-инъекций.
Хранимые процедуры хороши тем, что их можно изменить без перекомпиляции. Я бы постарался использовать их как можно чаще.
Я использую ad-hoc только для запросов, которые динамически генерируются на основе пользовательского ввода.
Параметризованный SQL или SPROC... не имеет значения с точки зрения производительности... вы можете запросить оптимизацию любого из них.
Для меня последнее оставшееся преимущество SPROC состоит в том, что я могу устранить много управления правами SQL, только предоставив свои права входа в систему для выполнения sprocs... если вы используете Parametized SQL, вход в систему со строкой подключения имеет гораздо больше прав (написание ЛЮБОГО например, оператор выбора для одной из таблиц, к которым у них есть доступ).
Я все еще предпочитаю Параметизированный SQL хотя...
Аргумент производительности sproc является спорным - 3 лучших RDBM используют кэширование плана запросов и работают уже некоторое время. Это было задокументировано... Или 1995 год все еще?
Тем не менее, встраивание SQL в ваше приложение также является ужасным дизайном - для многих поддержка кода кажется отсутствующей концепцией.
Если приложение может запускаться с нуля с помощью ORM (новых приложений очень мало!), То это отличный выбор, поскольку ваша модель класса управляет моделью БД - и экономит МНОГО времени.
Если среда ORM недоступна, мы использовали гибридный подход к созданию XML-файла ресурса SQL для поиска строк SQL, когда они нам нужны (они затем кэшируются средой ресурсов). Если SQL требует каких-либо незначительных манипуляций, это делается в коде - если требуются серьезные манипуляции со строками SQL, мы переосмысливаем подход
Этот гибридный подход облегчает управление разработчиками (возможно, мы меньшинство, поскольку моя команда достаточно умна, чтобы читать план запросов), а развертывание - это простая проверка из SVN. Кроме того, это облегчает переключение RDBM - просто замените файл ресурсов SQL (конечно, не так просто, как инструмент ORM, но это работает с устаревшими системами или неподдерживаемыми базами данных)
Процедуры по причинам, упомянутым другими, а также проще настроить процесс с помощью профилировщика или частей процесса. Таким образом, вам не нужно указывать кому-либо запускать его приложение, чтобы узнать, что отправляется на SQL-сервер.
Если вы используете специальные запросы, убедитесь, что они параметризованы
Я не нашел убедительных аргументов в пользу использования специальных запросов. Особенно те, которые перепутаны с вашим C#/Java/PHP кодом.
В эти дни я почти никогда не использую хранимые процедуры. Я использую их только для сложных запросов SQL, которые не могут быть легко сделаны в коде.
Одна из основных причин заключается в том, что хранимые процедуры не работают так же хорошо с операциями сопоставления OR.
Сегодня я думаю, что вам нужна очень веская причина для написания бизнес-приложения / информационной системы, в которой не используется какой-либо OR ORMAP.
Я предпочитаю хранить всю логику доступа к данным в программном коде, в котором уровень доступа к данным выполняет прямые SQL-запросы. С другой стороны, логику управления данными я помещаю в базу данных в виде триггеров, хранимых процедур, пользовательских функций и прочего. Примером, который я считаю достойным изучения базы данных, является генерация данных - предположим, что у нашего клиента есть FirstName и LastName. Теперь пользовательскому интерфейсу нужно DisplayName, которое получено из некоторой нетривиальной логики. Для этого поколения я создаю хранимую процедуру, которая затем выполняется триггером при каждом обновлении строки (или других исходных данных).
Похоже, существует несколько распространенное заблуждение, что уровень доступа к данным является базой данных, и все, что касается данных и доступа к данным, идет туда "просто потому что". Это просто неправильно, но я вижу много проектов, которые вытекают из этой идеи. Возможно, это местный феномен, хотя.
Я могу просто отключить идею SP, увидев так много плохо спроектированных. Например, один проект, в котором я участвовал, использовал набор хранимых процедур CRUD для каждой таблицы и каждого возможного запроса, с которым они столкнулись. При этом они просто добавили еще один совершенно бессмысленный слой. Больно даже думать о таких вещах.
Хранимая процедура работает как блок кода, поэтому вместо adhoc-запроса она работает быстро. Еще одна вещь - это хранимая процедура, дающая возможность перекомпиляции, которая лучше всего используется в хранимых процедурах, ничего подобного в adhoc-запросе.
Некоторые результаты в запросе и хранимой процедуре отличаются, это мой личный опыт. Используйте приведение и скрытую функцию для проверки этого.
Необходимо использовать хранимую процедуру для больших проектов, чтобы улучшить производительность.
У меня было 420 процедур в моем проекте, и он отлично работает для меня. Я работаю последние 3 года над этим проектом.
Поэтому используйте только процедуры для любой транзакции.
Зависит от того, какова ваша цель. Если вы хотите получить список элементов, и это происходит, например, один раз во время всего запуска вашего приложения, вероятно, не стоит использовать хранимую процедуру. С другой стороны, запрос, который выполняется многократно и требует (относительно) длительного времени, является отличным кандидатом на хранение в базе данных, поскольку производительность будет выше.
Если ваше приложение почти полностью живет в базе данных, хранимые процедуры не представляют сложности. Если вы пишете приложение для настольного компьютера, для которого база данных важна только в тангенциальном отношении, то лучшим вариантом могут быть специальные запросы, так как весь ваш код хранится в одном месте.
@Terrapin: Я думаю, ваше утверждение о том, что тот факт, что вам не нужно перекомпилировать приложение для внесения изменений, делает хранимые процедуры лучшим вариантом, не является началом. Могут быть причины для выбора хранимых процедур, а не для специальных запросов, но в отсутствие чего-либо убедительного проблема компиляции выглядит скорее как лень, чем как реальная причина.
Мой опыт показывает, что 90% запросов и / или хранимых процедур не должны быть написаны вообще (по крайней мере, вручную).
Доступ к данным должен генерироваться как-то автоматически. Вы можете решить, хотите ли вы статически генерировать процедуры во время компиляции или динамически во время выполнения, но когда вы хотите добавить столбец в таблицу (свойство объекта), вам следует изменить только один файл.
Это хорошая системная архитектура, если вы позволите подключить 1000 рабочих столов непосредственно к базе данных?
Нет, это, очевидно, нет, возможно, это плохой пример, но я думаю, что смысл, который я пытался прояснить, ясен: ваш администратор баз данных заботится о вашей инфраструктуре баз данных, в этом их экспертиза, а вставка SQL в код закрывает им дверь и их опыт.