sql - 一般来说，存储过程是否比现代 RDBMS 上的内联语句更有效？

Question

这个问题在这里已经有了答案:





Which is better: Ad hoc queries or stored procedures? [closed]

(22 个回答)


8年前关闭。




传统观点认为存储过程总是更快。所以，因为它们总是更快，所以使用它们 一直 .

我很确定这是基于某些历史背景，曾经是这种情况。现在，我不主张不需要存储过程，但我想知道在什么情况下存储过程在现代数据库(例如 MySQL、SQL Server、Oracle 或 )中是必需的。通过存储过程进行所有访问是否过大？

Answer 1

NOTE that this is a general look at stored procedures not regulated to a specific DBMS. Some DBMS (and even, different versions of the same DBMS!) may operate contrary to this, so you'll want to double-check with your target DBMS before assuming all of this still holds.

I've been a Sybase ASE, MySQL, and SQL Server DBA on-and off since for almost a decade (along with application development in C, PHP, PL/SQL, C#.NET, and Ruby). So, I have no particular axe to grind in this (sometimes) holy war.

存储过程的历史性能优势通常来自以下方面(无特定顺序):

预解析的 SQL

预先生成的查询执行计划

减少网络延迟

潜在的缓存优势

预解析的 SQL -- 除了在非常微观的层面上，编译代码与解释代码的好处相似。

还是优势？
在现代 CPU 上根本不是很明显，但是如果您正在发送一个每秒 1100 亿次非常大的 SQL 语句，则解析开销可能会增加。

预先生成的查询执行计划 .
如果你有很多 JOIN，排列会变得非常难以管理(现代优化器出于性能原因有限制和截止)。非常复杂的 SQL 具有独特的、可测量的(在我们调整 DBMS 之前，我已经看到一个复杂的查询需要 10 多秒才能生成计划)的延迟并不是未知的，因为优化器试图找出“接近最佳” ” 执行计划。存储过程通常会将其存储在内存中，因此您可以避免这种开销。

还是优势？
大多数 DBMS(最新版本)会缓存 INDIVIDUAL SQL 语句的查询计划，大大减少存储过程和即席 SQL 之间的性能差异。有一些警告和情况并非如此，因此您需要在目标 DBMS 上进行测试。

此外，越来越多的 DBMS 允许您提供优化器路径计划(抽象查询计划)以显着减少优化时间(对于即席和存储过程 SQL!!)。

WARNING Cached query plans are not a performance panacea. Occasionally the query plan that is generated is sub-optimal. For example, if you send SELECT *
FROM table WHERE id BETWEEN 1 AND
99999999, the DBMS may select a full-table scan instead of an index scan because you're grabbing every row in the table (so sayeth the statistics). If this is the cached version, then you can get poor performance when you later send SELECT * FROM table WHERE id BETWEEN
1 AND 2. The reasoning behind this is outside the scope of this posting, but for further reading see: http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx and http://msdn.microsoft.com/en-us/library/ms181055.aspx and http://www.simple-talk.com/sql/performance/execution-plan-basics/

"In summary, they determined that supplying anything other than the common values when a compile or recompile was performed resulted in the optimizer compiling and caching the query plan for that particular value. Yet, when that query plan was reused for subsequent executions of the same query for the common values (‘M’, ‘R’, or ‘T’), it resulted in sub-optimal performance. This sub-optimal performance problem existed until the query was recompiled. At that point, based on the @P1 parameter value supplied, the query might or might not have a performance problem."

减少网络延迟
A) 如果您一遍又一遍地运行相同的 SQL——并且 SQL 加起来有很多 KB 的代码——用简单的“exec foobar”替换它真的可以加起来。
B) 存储过程可用于将过程代码移动到 DBMS。这可以避免将大量数据混洗到客户端，只是为了让它返回一些信息(或根本不发送!)。类似于在 DBMS 中与在您的代码中执行 JOIN(每个人都喜欢的 WTF!)

还是优势？
A) 现代 1Gb(以及 10Gb 及更高版本!)以太网确实使这一点可以忽略不计。
B) 取决于您的网络有多饱和——为什么无缘无故地来回推送几兆字节的数据？

潜在的缓存优势
如果您在 DBMS 上有足够的内存并且您需要的数据在服务器的内存中，则执行服务器端数据转换可能会更快。

还是优势？
除非您的应用程序具有对 DBMS 数据的共享内存访问权限，否则优势将始终是存储过程。

当然，如果不讨论参数化 SQL 和即席 SQL，就不会完整地讨论存储过程优化。

参数化/准备好的 SQL
存储过程和即席 SQL 之间的一种交叉，它们是使用“参数”作为查询值的宿主语言中的嵌入式 SQL 语句，例如:

SELECT .. FROM yourtable WHERE foo = ? AND bar = ?

这些提供了一个更通用的查询版本，现代优化器可以使用它来缓存(和重用)查询执行计划，从而获得存储过程的大部分性能优势。

临时 SQL
只需打开 DBMS 的控制台窗口并输入 SQL 语句即可。在过去，这些是“最差”的执行者(平均而言)，因为 DBMS 无法像参数化/存储过程方法那样预先优化查询。

还是劣势？
不一定。大多数 DBMS 能够将即席 SQL“抽象”为参数化版本——从而或多或少地消除了两者之间的差异。有些是隐式执行此操作或必须使用命令设置启用(SQL 服务器: http://msdn.microsoft.com/en-us/library/ms175037.aspx ，Oracle: http://www.praetoriate.com/oracle_tips_cursor_sharing.htm )。

吸取的教训？
摩尔定律继续向前发展，DBMS 优化器随着每个版本的发布变得更加复杂。当然，您可以将每一个愚蠢的小 SQL 语句放在存储过程中，但要知道从事优化器工作的程序员非常聪明，并且不断寻找提高性能的方法。最终(如果它还没有)，即席 SQL 性能将变得与存储过程性能无法区分(平均而言!)，因此任何类型的大规模存储过程都仅出于“性能原因”** 使用 ** 对我来说肯定听起来像过早的优化.

无论如何，我认为如果您避免边缘情况并拥有相当普通的 SQL，您将不会注意到临时和存储过程之间的区别。