Что лучше: специальные запросы или хранимые процедуры?

В моем опыте написания в основном WinForms клиент / серверных приложений это простые выводы, к которым я пришел:

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

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

Использование специальных запросов:

1. Для CRUD, когда вам не нужно ограничивать доступ к данным (или вы делаете это другим способом).
2. Для простых поисков. Создание SP-х для множества критериев поиска-это боль и трудно поддерживать. Если вы можете создать достаточно быстрый поисковый запрос, используйте его.

В большинстве моих приложений я использовал как SP, так и ad-hoc sql, хотя я обнаружил, что использую SP все меньше и меньше, поскольку они в конечном итоге являются кодом, таким же как C#,, только сложнее контролировать версию, тестировать и поддерживать. Я бы рекомендовал использовать ad-hoc sql, если вы не можете найти конкретную причину этого.

Я не могу говорить ни с чем, кроме сервера SQL, но аргумент производительности не является существенно действительным, если вы не находитесь на 6.5 или ранее. SQL сервер кэширует планы выполнения ad-hoc уже примерно десять лет.

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

Встраивая запросы в приложение, вы тесно связываете себя с моделью данных.

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

С точки зрения безопасности рекомендуется запретить db_datareader и db_datawriter из вашего приложения и разрешить доступ только к хранимым процедурам.

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

Как специалист по данным, я бы не рассматривал работу с базой данных, доступ к которой осуществляется через запросы adhoc, поскольку их трудно эффективно настроить или управлять. Как я могу знать, что повлияет на изменение схемы? Кроме того, я не думаю, что пользователи должны когда-либо получать прямой доступ к таблицам базы данных по соображениям безопасности (и я имею в виду не только SQL инъекционные атаки, но и потому, что это основной внутренний контроль, чтобы не допускать прямых прав и требовать от всех пользователей использовать только procs, предназначенные для приложения. Это делается для предотвращения возможного мошенничества. Любая финансовая система, которая позволяет напрямую вставлять, обновлять или удалять права на таблицы, имеет огромный риск мошенничества. Это очень плохо.).

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

Наши разработчики говорят нам, что они рады, что весь наш доступ к базе данных осуществляется через procs, потому что это позволяет гораздо быстрее исправить ошибку, связанную с данными, а затем просто запустить proc в рабочей среде, а не создавать новую ветвь кода, перекомпилировать и перезагрузить в рабочей среде. Мы требуем, чтобы все наши процессоры были в subversion, поэтому управление версиями вообще не является проблемой. Если он не находится в Subversion, он будет периодически отбрасываться dbas, поэтому нет никакого сопротивления использованию системы управления версиями.

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

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

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

Я хотел бы процитировать Тома Кайта из Oracle here...Here's его правило о том, где писать code...though немного не связано, но хорошо знать, я думаю.

1. Начните с хранимых процедур в PL/SQL...
2. Если вы считаете, что что-то нельзя сделать с помощью хранимой процедуры в PL/SQL,, используйте Java хранимую процедуру.
3. Если вы считаете, что что-то нельзя сделать с помощью Java хранимых процедур, рассмотрите Pro*c.
4. Если вы считаете, что не можете достичь чего-то с помощью Pro*C,, вы можете переосмыслить то, что вам нужно сделать.

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

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

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

Мой ответ из другого поста: Хранимые процедуры являются MORE ремонтопригодными, потому что:

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

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

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

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

Проще портировать на другой DB-нет procs для порта

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

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

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

Выполнение google for Stored Procedure vs Dynamic Query покажет достойные аргументы в любом случае и, вероятно, лучше всего для вас, чтобы принять собственное решение...

Магазинные процедуры следует использовать как можно чаще, если ваше написание SQL в код уже настроило вас на головные боли в будущем. Для написания SPROC требуется примерно столько же времени, сколько и для написания его в коде.

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

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

EDIT: кто-то сказал, что перекомпиляция-это ленивая отговорка! да, давайте посмотрим, насколько ленивым вы себя чувствуете, когда вам нужно перекомпилировать и развернуть свое приложение на 1000 рабочих столов, и все потому, что DBA сказал вам, что ваш специальный запрос съедает слишком много серверного времени!

Здесь есть о чем подумать: кому вообще нужны хранимые процедуры?

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

кто-то сказал, что перекомпиляция-это ленивая отговорка! да, давайте посмотрим, насколько ленивым вы себя чувствуете, когда вам нужно перекомпилировать и развернуть свое приложение на 1000 рабочих столов, и все потому, что DBA сказал вам, что ваш специальный запрос съедает слишком много серверного времени!

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

Аргумент производительности sproc является спорным - 3 top RDBMs используют кэширование плана запроса и были в течение некоторого времени. Это было задокументировано... Или это все еще 1995 год?

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

Если приложение может начать с нуля с ORM (гринфилдские приложения далеко и мало между ними!) это отличный выбор, поскольку ваша модель класса управляет вашей моделью DB - и экономит LOTS времени.

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

Этот гибридный подход позволяет легко управлять разработчиками (возможно, мы в меньшинстве, поскольку моя команда достаточно умна, чтобы прочитать план запроса), а deployment-это простая проверка из SVN. Кроме того, это упрощает переключение RDBMs - просто замените файл ресурсов SQL (конечно, не так просто, как инструмент ORM, но подключение к устаревшим системам или не поддерживаемым базам данных это работает)

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

Я использую ad-hoc только для запросов, которые динамически генерируются на основе пользовательского ввода.

Procs по причинам, упомянутым другими, а также легче настроить proc с помощью профилировщика или частей proc. Таким образом, вам не нужно говорить кому-то, чтобы запустить его приложение, чтобы узнать, что отправляется на сервер SQL

Если вы используете специальные запросы, убедитесь, что они параметризованы

Parametized SQL или SPROC...doesn't вопрос с позиции производительности point...you может оптимизировать запрос один.

Для меня последнее оставшееся преимущество SPROC заключается в том, что я могу устранить много SQL управления правами, только предоставив мои права входа для выполнения sprocs...if вы используете Parametized SQL вход в вашу строку подключения имеет гораздо больше прав (написание ANY вида оператора select на одной из таблиц, к которым у них тоже есть доступ).

Хотя я все еще предпочитаю Параметизированный SQL...

Я не нашел убедительных аргументов для использования специальных запросов. Особенно те, что перепутались с вашим кодом C#/Java/PHP.

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

Некоторые результаты в запросе и хранимой процедуре отличаются, это мой личный опыт. Используйте функцию cast и covert для проверки этого.

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

У меня было 420 процедур в моем проекте, и это прекрасно работает для меня. Я работаю над этим проектом последние 3 года.

Поэтому используйте только процедуры для любой транзакции.

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

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

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

Мой опыт показывает, что 90% запросов и / или хранимых процедур вообще не следует писать (по крайней мере, вручную).

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

Я предпочитаю хранить всю логику доступа к данным в программном коде, в котором уровень доступа к данным выполняет прямые запросы SQL. С другой стороны, логику управления данными я вкладываю в базу данных в виде триггеров, хранимых процедур, пользовательских функций и тому подобного. Пример того, что я считаю достойным базы данных-если это генерация данных - предположим, что у нашего клиента есть FirstName и LastName. Теперь пользовательский интерфейс нуждается в DisplayName, который является производным от некоторой нетривиальной логики. Для этого поколения я создаю хранимую процедуру, которая затем выполняется триггером при каждом обновлении строки (или других исходных данных).

По-видимому, существует это довольно распространенное заблуждение, что уровень доступа к данным IS база данных и все, что касается данных и доступа к данным, входит туда "just because". Это просто неправильно, но я вижу много конструкций, которые вытекают из этой идеи. Хотя, возможно, это местный феноменон.

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

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

Одна из основных причин заключается в том, что хранимые процедуры не работают так же хорошо с OR mappers.

В наши дни я думаю, что вам нужна очень веская причина, чтобы написать бизнес-приложение / информационную систему, которая не использует какой-то OR mapper.

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

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