Специальные запросы по сравнению с хранимыми процедурами по сравнению с Динамическим [закрытым] SQL

СУБД? Этот ответ специфичен для более старого оракула

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

Хранимые процедуры

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

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

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

Ad Hoc SQL (т.е. созданный в вашем бизнес-коде)

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

Примечание: всегда параметризуйте ваш ad hoc SQL.

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

Хранимые процедуры PROs:

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

Минусы хранимых процедур:

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

Хранимые процедуры

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

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

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