Специальные запросы против хранимых процедур и динамического SQL
Специальные запросы против хранимых процедур против динамического SQL. Кто-нибудь может сказать плюсы и минусы?
6 ответов
Хранимые процедуры
- Pro: Подходит для коротких, простых запросов (или OLTP- т.е. добавлять, обновлять, удалять, просматривать записи)
- Pro: держит логику базы данных отдельно от бизнес-логики
- Pro: легко устранять неисправности
- Pro: простота в обслуживании
- Pro: Меньше битов передается по сети (т.е. только имя процесса и параметры)
- Pro: скомпилировано в базе данных
- Pro: лучшая безопасность (пользователям не нужен прямой доступ к таблицам)
- Pro: Отличное кэширование плана запросов (хорошо для OLTP- запросов - преимущества повторного использования плана)
- Против: Отличное кэширование планов запросов (плохо для запросов OLAP - преимущества уникальных планов)
- Con: Связывает вас с этим поставщиком SQL
Динамический SQL (т.е. использует команду exec в хранимой процедуре)
- Pro: Хорошо для коротких, простых запросов (он же OLTP)
- Pro: держит логику базы данных отдельно от бизнес-логики
- Pro: Меньше битов передается по сети (т.е. только имя процесса и параметры)
- Pro: позволяет ссылаться на любую таблицу, базу данных или столбец
- Pro: позволяет добавлять / удалять предикаты (в предложении WHERE) на основе параметров
- Pro: Хорошее кэширование плана запросов (от среднего до хорошего для запросов OLTP и OLAP)
- Con: Только статические элементы proc могут быть скомпилированы
- Con: Связывает вас с этим поставщиком SQL
- Против: Сложнее устранить неполадки
- Против: более уязвимы для атак SQL-инъекций
Специальный SQL (т. Е. Созданный в вашем бизнес-коде)
- Pro: Подходит для долгих, сложных задач (иначе как OLAP - то есть отчетность или анализ)
- Pro: гибкий доступ к данным
- Pro: возможно использование ORM; может быть скомпилирован / протестирован в коде (например, Linq-to-Sql или SqlAlchemy)
- Pro: плохое кэширование планов запросов (хорошо для запросов OLAP - преимущества от уникальных планов)
- Недостаток: плохое кэширование плана запросов (плохо для OLTP- запросов - преимущества повторного использования плана)
- Против: больше битов передается по сети (т. Е. Весь запрос и параметры)
- Против: Сложнее в обслуживании, если вы не используете ORM
- Против: Сложнее устранить неполадки, если вы не используете ORM
- Против: более уязвимы для атак SQL-инъекций
Примечание. Всегда настраивайте свой специальный SQL.
Для OLAP специального SQL: только параметризовать строковые данные. Это удовлетворяет двум условиям. Это предотвращает SQL инъекцию атаки. И это делает запросы более уникальными для базы данных. Да, вы получите плохой коэффициент попадания в кэш плана запросов. Но это желательно для запросов OLAP. Они получают выгоду от генерации уникальных планов, поскольку их наборы данных и наиболее эффективные планы сильно различаются по заданным параметрам.
Хранимые процедуры PRO:
- Составитель. Это означает, что он быстрее запускается и оказывает положительное влияние на ЦП сервера базы данных благодаря обходу этапа оптимизации / компиляции для всех, кроме первого выполнения.
- Разрешить чистый контроль разрешений для сложных запросов на чтение и запись.
- Обеспечить многоразовый API, обеспечивающий одну ХОРОШУЮ эффективную реализацию, вместо множества Yahoos на различных платформах из множества приложений, повторно реализующих запросы samke и рискующих получить неэффективные реализации
- Как и любой API, обеспечьте уровень абстракции. Вы можете изменить базовую реализацию (схему) без изменения кода, вызывающего SP. Это чрезвычайно большой плюс, когда есть сотни приложений на всех платформах, которые используют запрос.
Хранимые процедуры
- Трудно кодировать гибкую логику по сравнению с динамическим SQL
- Предварительно скомпилированная версия может привести к менее эффективному выполнению, так как ваши данные перемещаются и выбор оптимизатора меняется. Это легко улучшить, перекомпилируя время от времени.
RDBMS? Этот ответ является специфическим для более старого оракула
В более ранней версии Oracle < 11 динамический sql не использует существующие планы sqltext SGA, он создает новую запись для каждого плана выполнения, который необходим анализатору. При большом количестве динамических вызовов sql область sqltext сбрасывается достаточно быстро, поэтому повторное использование запросов значительно снижается, а производительность снижается.
Еще одним преимуществом является более простое "Нет обновлений простоя" (для крупных обновлений вы все равно можете понести некоторое время простоя).
если весь доступ к данным осуществляется через хранимые процедуры, вы можете легко разместить v1 и v2 хранимых процедур рядом друг с другом.
теперь вы можете иметь двоичные файлы / логику приложения из v1 и v2, работающие рядом, каждый из которых вызывает свою собственную версию хранимых процедур.
время простоя не достигается через 1, блокировку приложения v1 в режиме только для чтения (если применимо), 2, развертывание изменений в БД. 3, включив нормальный доступ к приложению v1, 4, развернув приложение v2 рядом, попросите новых пользователей использовать новые двоичные файлы. 6. закрывать старые двоичные файлы, когда старые пользователи больше не используют пользователей.
Хранимые процедуры
- Pro: Разрешение действий без необходимости предоставления более фундаментальных прав на уровне таблиц.
- Pro: дискретный и версионный
- Pro: Позволяет вам изолировать вашу схему от кода доступа к данным.
- Против: Может быть утомительно кодировать процедуры CRUD
- Con: Необходимо поддерживать в соответствии с базовой схемой
Специально и динамично - см. Ответы и комментарии Билла Паецке.
Также не забывайте о таких шаблонах, как массовая вставка для SQL, которых нет в вашем списке, но которые все же следует учитывать.
ИМХО хранимых процедур следует избегать как чумы. Вот десять веских причин, почему вы никогда не должны их использовать (относится ко всем базам данных):
- Язык PL/SQL ориентирован на обработку данных в таблицах, строках и столбцах. Это плохой выбор для выражения бизнес-логики. Вы можете кодировать что угодно на любом языке - это не значит, что вы должны
- Большинству баз данных не хватает приличной IDE, чтобы помочь с синтаксисом и ссылками на другие существующие процедуры (например, как в Eclipse для java)
- Труднее найти человеческие ресурсы для написания и поддержки хранимых процедур - они просто намного реже и поэтому дороже
- Хранимые процедуры не переносимы между базами данных, потому что: а) отраслевого стандарта для PL/SQL не существует; б) даже если бы он существовал, вы обычно используете специфичные для базы данных функции /sql внутри хранимых процедур. Если вам когда-нибудь понадобится переместить базу данных, вы смотрите на переписывание вашей бизнес-логики
- Большинство баз данных не предлагают никакой поддержки для отладки хранимых процедур - вам остается вставить строки в таблицу журналов или что-то подобное для достижения ведения журнала для отладки - очень уродливо
- Для проверки хранимой процедуры вам нужен реальный экземпляр базы данных. Это затрудняет модульное тестирование хранимых процедур (для их запуска необходимо развернуть их в dev db)
- Для развертывания хранимых процедур необходимо обновить базу данных (удалить, а затем создать хранимую процедуру). Если обнаружена ошибка, вы не можете просто откатиться к предыдущему бинарному выпуску, как вы можете с помощью кода приложения. Вместо этого вы должны найти старый код, удалить новый сохраненный процесс и (заново) создать старый. Это изменение поверх изменения, а не откат
- Вы повышаете требования к обработке сервера баз данных, а не распространяете бизнес-логику на другие серверы (приложений). Поскольку база данных обычно одноэлементная, это очень плохо, потому что единственный способ увеличить емкость - это купить лучшее оборудование (не покупать больше оборудования или использовать облако)
- Они не намного быстрее, чем хорошо написанные запросы, использующие подготовленные операторы, потому что есть компромисс между возрастающим спросом на обработку на сервере базы данных и эффективностью их использования. Кроме того, скорость - это еще не все (если это приемлемо): поддержка, отладка, пригодность PL/SQL и т. Д. Так же важны, если не более
- Хранимые языки процессов имеют ограниченные (если таковые имеются) библиотеки, на которые можно опираться, так что в итоге вы пишете много кода с низким значением. Это не похоже на языки приложений, которые имеют множество библиотек, которые вы можете использовать для всего, что нужно бизнес-логике
Есть только одно место, где я бы санкционировал их использование: для очень специфической функциональности базы данных - возможно, проверка ключа или преобразование типа данных или что-то подобное, возможно, в триггере, что настолько важно, что оправдывает его существование и что, вероятно, никогда не будет изменить один раз написано.
В общем, вы должны запускать крики из хранимых процедур!