Специальные запросы и хранимые процедуры vs Dynamic SQL

Ответ 1

Сохраненные процедуры

• Pro: подходит для коротких простых запросов (например, OLTP - добавление, обновление, удаление, просмотр записей)
• Pro: Сохраняет логику базы данных отдельно от бизнес-логики
• Pro: легко устранить неполадки
• Pro: Простота обслуживания
• Pro: Меньше бит передается по сети (т.е. только имя и параметры proc)
• Pro: Скомпилировано в базе данных
• Pro: улучшенная безопасность (пользователям не нужен прямой доступ к таблице)
• Pro: отличное кэширование плана запроса (полезно для запросов OLTP - выгоды от повторного использования плана).
• Con: Отличное кэширование плана запроса (плохо для запросов OLAP - выгоды от уникальных планов)
• Con: привязывает вас к этому поставщику SQL

Динамический SQL (т.е. использует команду exec в хранимой процедуре)

• Pro: Хорошо для коротких, простых запросов (иначе OLTP)
• Pro: Сохраняет логику базы данных отдельно от бизнес-логики
• Pro: Меньше бит передается по сети (т.е. только имя и параметры proc)
• Pro: Позволяет ссылаться на любую таблицу, базу данных или столбец
• Pro: Позволяет добавлять/удалять предикаты (в предложении WHERE) на основе параметров
• Pro: Хорошее кэширование плана запроса (посредственное к хорошему как для OLTP, так и для OLAP-запросов)
• Con: только статические элементы proc могут быть скомпилированы
• Con: привязывает вас к этому поставщику SQL
• Con: сложнее устранить неполадки
• Con: более уязвим для атак SQL-инъекций.

Специальный SQL (т.е. создан в бизнес-коде)

• Pro: Хорошо для длинных сложных запросов (например, OLAP - то есть отчетов или анализа).
• Pro: гибкий доступ к данным
• Pro: возможно использование ORM; могут быть скомпилированы/протестированы в коде (то есть Linq-to-Sql или SqlAlchemy)
• Pro: плохое кэширование плана запросов (полезно для запросов OLAP - выгоды от уникальных планов)
• Con: плохое кэширование плана запросов (плохо для запросов OLTP - выгоды от повторного использования плана)
• Con: больше бит передается по сети (т.е. весь запрос и параметры)
• Con: Сложнее поддерживать, если вы не используете ORM
• Con: сложнее устранить неполадки, если вы не используете ORM
• Con: более уязвим для атак SQL-инъекций.

Примечание. Всегда указывайте параметры вашего специального SQL.

Для OLAP ad hoc SQL: только параметризовать строковые данные. Это удовлетворяет двум условиям. Он предотвращает атаку SQL-инъекций. И это делает запросы более уникальными для базы данных. Да, вы получите плохой коэффициент охвата кеша запроса. Но это желательно для запросов OLAP. Они извлекают выгоду из уникального создания плана, поскольку их данные и наиболее эффективные планы сильно различаются по заданным параметрам.

Ответ 2

Сохраненные процедуры PROs:

• Сост. Это означает, что он быстрее запускается и оказывает положительное влияние на ваш процессор сервера базы данных из-за обхода этапа оптимизации/компиляции для всех, кроме первого выполнения.
• Разрешить чистое разрешение на управление сложными запросами на чтение и запись.
• Предоставьте API многократного использования, позволяющий эффективную реализацию ХОРОШЕГО, а не кучу Yahoos на различных платформах из множества приложений, повторно реализующих запросы samke и рискуя получить неэффективные реализации.
• Как и любой API, укажите уровень абстракции. Вы можете изменить базовую реализацию (схему) без изменения кода, вызывающего SP. Это очень большой плюс, когда на всех платформах, использующих запрос, есть 100 приложений.

Сохраненные процедуры CON:

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

Ответ 3

Сохраненные процедуры

• Pro: разрешение действий без необходимости предоставления более фундаментальных прав на уровне таблицы.
• Pro: дискретный и доступный версии
• Pro: позволяет вам изолировать вашу схему от кода доступа к данным.
• Con: Может быть утомительно кодировать CRUD-процедуры.
• Con: необходимо поддерживать в соответствии с базовой схемой

Ad hoc и dynamic - см. ответы и комментарии Билла Паэтца.

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

Ответ 4

РСУБД? Этот ответ относится к более раннему оракулу

В старой версии oracle < 11, динамический sql не использует повторно существующие SGA-планы sqltext, он создает новую запись для каждого плана выполнения, который нужен парсеру. С большим количеством динамических sql-вызовов область sqltext становится покрасневшей настолько быстро, что повторное использование запроса идет вниз, и после него происходит следование.

Ответ 5

Еще одно преимущество - "Нет простоев" (для крупных обновлений вы все равно можете столкнуться с простоями).

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

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

время простоя не достигается через 1, блокировка приложения v1 в режиме readonly (если применимо), 2, развертывание изменений db. 3, повторное включение обычного доступа к приложению v1, 4, развертывание приложения v2 бок о бок, рассказать новым пользователям использовать новые двоичные файлы. 6. Закройте старые двоичные файлы, когда больше пользователей не используют старые двоичные файлы.

Ответ 6

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

• Язык PL/SQL предназначен для обработки данных в таблицах, строках и столбцах. Это плохой выбор для выражения бизнес-логики. Вы можете кодировать что угодно на любом языке - это не значит, что вы должны
• В большинстве баз данных отсутствует достойная среда IDE, которая помогает с синтаксисом и связыванием с другими существующими процедурами (например, например, Eclipse для java).
• Человеческие ресурсы сложнее найти для написания и хранения хранимых процедур - они просто намного реже и, следовательно, более дорогие.
• Хранимые процедуры не переносятся между базами данных, потому что a) нет промышленного стандарта для PL/SQL b), даже если бы был стандарт, вы обычно используете функциональные возможности /sql для конкретной базы данных в своих хранимых процедурах. Если вам когда-либо понадобится переместить dbs, вы смотрите полную переписку своей бизнес-логики.
• Большинство баз данных не предлагают поддержки для отладки хранимых процедур - вам остается вставлять строки в таблицу журналов или аналогичные для ведения журнала для отладки - очень уродливые
• Для проверки хранимой процедуры вам нужен реальный экземпляр базы данных. Это затрудняет модульное тестирование хранимых процедур (вы должны развернуть их в dev db для их запуска).
• Чтобы развернуть хранимые procs, вам необходимо обновить базу данных (затем выберите затем хранимую процедуру). Если обнаружена ошибка, вы не можете просто вернуться к предыдущему двоичному выпуску, как вы можете с помощью кода приложения. Вместо этого вы должны найти старый код, отбросить новый сохраненный proc и (re) создать старый. Это изменение в верхней части изменения, а не откат
• Вы увеличиваете требования к обработке сервера баз данных, а не распространяете бизнес-логику на другие (приложения) серверы. Поскольку база данных обычно является синглом, это очень плохо, потому что единственный способ увеличить пропускную способность - это купить лучшее оборудование (не покупать больше оборудования или использовать облако).
• Они не намного быстрее, чем хорошо написанные запросы, используя подготовленные операторы, потому что существует компромисс между увеличением потребности в обработке на сервере базы данных и эффективностью их использования. Кроме того, скорость - это не все (до тех пор, пока это приемлемо): Поддержание работоспособности, отладки, пригодность PL/SQL и т.д. Так же важно, если не более того
• Сохраненные языки proc имеют ограниченные (если есть) библиотеки для рисования, поэтому вы в конечном итоге записываете множество низкоценных кодов. Это не похоже на языки приложений, в которых есть множество библиотек, которые вы можете использовать для всего, что требуется бизнес-логике.

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

В общем, вы должны запускать крик из хранимых процедур!