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

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

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

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

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

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

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

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

Easier to port to another DB - no procs to port

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

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

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

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

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

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

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

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

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

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

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

Я по-прежнему предпочитаю Parametized SQL ...

По моему опыту написания в основном клиент-серверных приложений WinForms, я пришел к простым выводам:

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

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

Используйте специальные запросы:

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

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

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

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

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

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

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

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

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

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

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

Я хотел бы здесь процитировать Тома Кайта из Oracle ... Вот его правило о том, где писать код ... хоть и немного не связанное с этим, но, полагаю, полезно знать.

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

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

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

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

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

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

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

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

someone said that recompile is a lazy excuse! yeah lets see how lazy you feel when you have to recompile and deploy your app to 1000's of desktops, all because the DBA has told you that your ad-hoc Query is eating up too much Server time!

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

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

is it good system architecture if you let connect 1000 desktops directly to database?

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

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

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

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

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

Похоже, существует довольно распространенное заблуждение, что уровень доступа к данным - это база данных, и все, что касается данных и доступа к данным, происходит «просто потому, что». Это просто неправильно, но я вижу множество дизайнов, основанных на этой идее. Хотя, возможно, это местный феномон.

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

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

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

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

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

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

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

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

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

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

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

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

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

Аргумент производительности sproc спорный - 3 самых популярных RDBM используют кеширование плана запроса и уже некоторое время используют его. Это задокументировано ... Или все еще 1995 год?

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

Если приложение может запускаться с нуля с ORM (новых приложений очень мало!), Это отличный выбор, поскольку ваша модель класса управляет вашей моделью БД - и экономит МНОГО времени.

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

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