即席查询 vs 存储过程 vs 动态 SQL [英] Ad hoc queries vs stored procedures vs Dynamic SQL

问题描述

即席查询与存储过程与动态 SQL.谁能说说优缺点?

Ad hoc queries vs stored procedures vs Dynamic SQL. Can anyone say pros and cons?

推荐答案

存储过程

• 优点:适用于简短的简单查询(又名 OLTP——即添加、更新、删除、查看记录)
• 优点:将数据库逻辑与业务逻辑分开
• 优点:易于排除故障
• 优点:易于维护
• 优点:通过网络传输的比特更少(即只有过程名称和参数)
• Pro:在数据库中编译
• 优点:更好的安全性(用户不需要直接访问表)
• 优点:出色的查询计划缓存(适用于 OLTP 查询--计划重用的好处)
• 缺点:出色的查询计划缓存(不利于 OLAP 查询——独特计划的好处)
• 缺点:让您与该 SQL 供应商联系在一起

• Pro: Good for short, simple queries (aka OLTP--i.e. add, update, delete, view records)
• Pro: Keeps database logic separate from business logic
• Pro: Easy to troubleshoot
• Pro: Easy to maintain
• Pro: Less bits transferred over network (i.e. only the proc name and params)
• Pro: Compiled in database
• Pro: Better security (users don't need direct table access)
• Pro: Excellent query plan caching (good for OLTP queries--benefits from plan reuse)
• Con: Excellent query plan caching (bad for OLAP queries--benefits from unique plans)
• Con: Makes you tied to that SQL vendor

动态 SQL(即在存储过程中使用 exec 命令)

Dynamic SQL (i.e. uses exec command within a stored procedure)

• 优点:适用于简短的简单查询(又名 OLTP)
• 优点:将数据库逻辑与业务逻辑分开
• 优点:通过网络传输的比特更少(即只有过程名称和参数)
• Pro:允许引用任何表、数据库或列
• Pro:允许根据参数添加/删除谓词(在 WHERE 子句中)
• 优点:良好的查询计划缓存(对于 OLTP 和 OLAP 查询来说都一般)
• 缺点:只能编译 proc 的静态元素
• 缺点:让您与该 SQL 供应商联系在一起
• 缺点:更难排除故障
• 缺点:更容易受到 SQL 注入攻击

Ad Hoc SQL(即在您的业务代码中创建)

Ad Hoc SQL (i.e. created in your business code)

• 优点:适用于长时间、复杂的静默(又名 OLAP——即报告或分析)
• 优点:灵活的数据访问
• Pro:可以使用 ORM；可以在代码中编译/测试(即 Linq-to-Sql 或 SqlAlchemy)
• 优点:查询计划缓存不佳(有利于 OLAP 查询--独特计划的好处)
• 缺点:查询计划缓存不佳(不利于 OLTP 查询——计划重用的好处)
• 缺点:通过网络传输的比特数更多(即整个查询和参数)
• 缺点:如果不使用 ORM，则更难维护
• 缺点:如果不使用 ORM，则更难排除故障
• 缺点:更容易受到 SQL 注入攻击

• Pro: Good for long, complex quieres (aka OLAP--i.e. reporting or analysis)
• Pro: Flexible data access
• Pro: ORM usage is possible; can be compiled/tested in code (i.e. Linq-to-Sql or SqlAlchemy)
• Pro: Poor query plan caching (good for OLAP queries--benefits from unique plans)
• Con: Poor query plan caching (bad for OLTP queries--benefits from plan reuse)
• Con: More bits transferred over network (i.e. the whole query and params)
• Con: More difficult to maintain, if you don't use an ORM
• Con: More difficult to troubleshoot, if you don't use an ORM
• Con: More vulnerable to SQL injection attacks

注意:始终参数化您的即席 SQL.

Note: Always parameterize your ad hoc SQL.

对于 OLAP ad hoc SQL:仅参数化字符串数据.这满足两个条件.它可以防止 SQL 注入攻击.它使查询看起来对数据库更独特.是的，你会得到一个糟糕的查询计划缓存命中率.但这对于 OLAP 查询是可取的.他们受益于独特的计划生成，因为他们的数据集和最有效的计划在给定参数之间差异很大.

For OLAP ad hoc SQL: only parameterize string data. This satisfies two conditions. It prevents SQL injection attack. And it makes the queries look more unique to the database. Yes, you'll get a poor query plan cache hit ratio. But that's desirable for OLAP queries. They benefit from unique plan generation, since their datasets and most efficient plans vary greatly among given parameters.

这篇关于即席查询 vs 存储过程 vs 动态 SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！