Динамический sql по сравнению с хранимыми процедурами - за и против?

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

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

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

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

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

Наконец, если вы пишете код для нескольких баз данных (например, для Oracle и SQL-совместимого кода), я бы полностью избегал хранимых процедур.

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

Основные сценарии, когда у вас ДОЛЖЕН быть SP:

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

2) Когда логика "Only True" для доступа к определенному набору данных ОЧЕНЬ сложна, к ней нужно обращаться из нескольких разных кодовых баз на разных платформах (поэтому написание нескольких API в коде намного дороже).

Любой другой сценарий, это спорный и может быть решен так или иначе.

Я также должен сказать, что аргументы других авторов по поводу управления версиями не так уж и важны в моем опыте - иметь SP в системе управления версиями так же просто, как создать структуру каталогов "sql / db_name" и иметь простой базовый " выпуск базы данных », который выпускает код SP из места управления версиями в базу данных. В каждой компании, в которой я работал, была какая-то подобная установка: центральная, управляемая администраторами баз данных, или ведомственная, управляемая разработчиками.

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

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

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

У хранимых процедур есть две цели, о которых я могу думать.

1. Помощь в упрощении доступа к данным. Хранимая процедура не содержит какой-либо бизнес-логики, она просто знает структуру data и предоставляет интерфейс для изолированного доступа к трем таблицам и представлению только для получения единственной информации.
2. Сопоставление модели домена с моделью данных , хранимые процедуры могут помочь сделать модель данных похожей на данную модель домена.

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

Вот простой ответ:

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

DS более гибкий. Подход SP делает вашу систему более управляемой.

Хранимые процедуры идеально подходят для:

• создания многократно используемых абстракций над сложными запросами;
• принудительного применения определенных типов вставок / обновлений в таблицы (если вы также запрещаете разрешения для таблицы );
• Выполнение привилегированных операций, которые вошедший в систему пользователь обычно не может выполнять;
• Гарантия согласованного плана выполнения;
• Расширение возможностей ORM (пакетные обновления, запросы иерархии и т. Д.) )

Динамический SQL идеально подходит для:

• Переменных аргументов поиска или выходных столбцов:
  • Необязательные условия поиска
  • Сводные таблицы
  • IN предложения со значениями, задаваемыми пользователем
• Реализации ORM (большинство из них могут использовать SP, но не могут быть построены полностью на них );
• DDL и административные скрипты.

Они действительно решают разные проблемы. Используйте то, что больше подходит для поставленной задачи, и не ограничивайте себя одним или другим. Поработав некоторое время над кодом базы данных, вы начнете более интуитивно чувствовать эти вещи; вы обнаружите, что собираете вместе какое-то крысиное гнездо строк для запроса и думаете: «это действительно должно входить в хранимую процедуру».

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