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

Ответ 1

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

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

• Для любой сложной работы с данными. Если вы собираетесь делать что-то действительно требующее таблиц курсора или temp, это, как правило, самый быстрый способ сделать это в SQL Server.
• Если вам нужно заблокировать доступ к данным. Если вы не предоставляете доступ к таблице пользователям (или роли или чему-либо еще), вы можете быть уверены, что единственный способ взаимодействия с данными - через создаваемый вами СП.

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

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

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

Ответ 2

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

Ответ 3

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

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

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

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

Ответ 4

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

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

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

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

Ответ 5

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

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

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

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

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

Ответ 6

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

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

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

Ответ 7

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

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

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

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

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

Легче переносить на другой БД - нет procs для порта

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

Ответ 8

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

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

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

Ответ 9

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

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

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

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

Ответ 10

Некоторые вещи, о которых нужно подумать: Кому нужны хранимые процедуры, Anyways?

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

Ответ 11

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

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

Ответ 12

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

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

Ответ 13

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

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

Я по-прежнему предпочитаю параметризованный SQL, хотя...

Ответ 14

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

Ответ 15

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

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

Ответ 16

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

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

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

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

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

Ответ 17

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

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

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

Ответ 18

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

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

Ответ 19

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

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

Ответ 20

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

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

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

Ответ 21

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

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

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

Ответ 22

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

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

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

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

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