一般而言,存储过程是否比现代RDBMS上的内联语句更有效？

传统观点认为存储过程总是更快.所以,因为他们总是更快,使用它们所有的时间.

我很确定这是基于历史背景,曾经是这种情况.现在,我并不主张不需要存储过程,但我想知道在现代数据库(如MySQL,SQL Server,Oracle或< Insert_your_DB_here >)中存储过程在什么情况下是必需的.通过存储过程进行ALL访问是否过度？

1> Matt Rogish..：

---

请注意,这是对未受特定DBMS管理的存储过程的一般性查看.某些DBMS(甚至是相同DBMS的不同版本!)可能与此相反,因此在假设所有这些仍然成立之前,您需要仔细检查目标DBMS.

近十年来,我一直是Sybase ASE,MySQL和SQL Server DBA的开源和关闭(以及C,PHP,PL/SQL,C#.NET和Ruby中的应用程序开发).所以,我在这场(有时)圣战中没有特别的斧头.

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

预解析的SQL

预生成的查询执行计划

减少网络延迟

潜在的缓存优势

预解析的SQL - 与编译代码和解释代码类似的好处,除非在非常微观的层面上.

还有优势吗？ 在现代CPU上根本不是很明显,但是如果你发送一个非常大的每秒十亿次的SQL语句,那么解析开销就会增加.

预生成的查询执行计划.如果你有很多JOIN,那么排列会变得非常难以管理(现代优化器因性能原因而有限制和截止).对于非常复杂的SQL来说,具有独特的,可测量的(我已经看到一个复杂的查询只需要10秒以上的时间来生成计划,在我们调整DBMS之前)由于优化器试图找出"接近最佳" "执行计划.通常,存储过程会将其存储在内存中,以避免这种开销.

还有优势吗？ 大多数DBMS(最新版本)将缓存INDIVIDUAL SQL语句的查询计划,大大降低了存储过程和即席SQL之间的性能差异.有一些警告和案例并非如此,因此您需要在目标DBMS上进行测试.

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

警告缓存的查询计划不是性能的灵丹妙药.有时,生成的查询计划是次优的.例如,如果您发送SELECT * FROM table WHERE id BETWEEN 1 AND 99999999,DBMS可能会选择全表扫描而不是索引扫描,因为您正在抓取表中的每一行(所以说明统计数据).如果这是缓存版本,那么稍后发送时可能会导致性能下降 SELECT * FROM table WHERE id BETWEEN 1 AND 2.这背后的原因超出了本文的范围,但有待进一步阅读,请参阅:http: //www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx 和 http://msdn.microsoft.com/ en-us/library/ms181055.aspx 和http://www.simple-talk.com/sql/performance/execution-plan-basics/

"总之,他们确定在执行编译或重新编译时提供除公共值之外的任何内容导致优化器编译并缓存该特定值的查询计划.但是,当该查询计划被重用于后续执行时查询公共值('M','R'或'T'),导致次优性能.这个次优性能问题一直存在,直到重新编译查询.此时,基于@ P1提供的参数值,查询可能有也可能没有性能问题."

减少网络延迟 A)如果你一遍又一遍地运行相同的SQL - 并且SQL增加了多KB的代码 - 用一个简单的"exec foobar"代替它可以真正加起来.B)存储过程可用于将过程代码移动到DBMS中.这样就可以节省大量数据到客户端,只是让它发回一小撮信息(或者根本不发送!).类似于在DBMS中与您的代码中进行JOIN(每个人最喜欢的WTF!)

还有优势吗？ A)现代1Gb(以及10Gb及以上!)以太网确实可以忽略不计.B)取决于你的网络是多么饱和 - 为什么没有充分理由来回推送几兆字节的数据？

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

还有优势吗？ 除非您的应用程序共享内存访问DBMS数据,否则边缘始终是存储过程.

当然,如果不讨论参数化和临时SQL,就不会讨论存储过程优化.

参数化/准备SQL
存储过程和临时SQL之间的交叉类型,它们是使用"参数"作为查询值的宿主语言的嵌入式SQL语句,例如:

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

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

Ad Hoc SQL 只需打开DBMS的控制台窗口并输入SQL语句即可.过去,这些是"最差"的表现者(平均而言),因为DBMS无法像参数化/存储过程方法那样预先优化查询.

还有一个缺点吗？ 不必要.大多数DBMS都能够将ad hoc SQL"抽象"为参数化版本 - 因此或多或少地否定了两者之间的差异.有些是隐式执行或必须使用命令设置启用(SQL server:http://msdn.microsoft.com/en-us/library/ms175037.aspx,Oracle:http://www.praetoriate.com/oracle_tips_cursor_sharing. htm).

得到教训？ 摩尔定律继续发展,每次发布时,DBMS优化器都变得更加复杂.当然,您可以将每个愚蠢的SQL语句放在存储过程中,但只要知道编写优化器的程序员非常聪明并且不断寻找提高性能的方法.最终(如果它不在这里)临时SQL性能将与存储过程性能无法区分(平均而言!),因此任何类型的大规模存储过程仅仅出于"性能原因"使用**确实听起来像是对我的过早优化.

无论如何,我认为如果你避免边缘情况并且有相当普通的SQL,你就不会注意到ad hoc和存储过程之间的区别.

---

这是一个很好的响应,但你应该注意它必须*参数化*SQL - 普通的vanilla"用替换构建一个巨大的字符串"SQL也不会表现得那么好.

2> DOK..：

---

使用存储过程的原因:

减少网络流量 - 您必须通过网络发送SQL语句.使用sprocs,您可以批量执行SQL,这也更有效.

缓存查询计划 - 第一次执行sproc时,SQL Server会创建一个执行计划,该计划将被缓存以供重用.对于经常运行的小查询,这尤其高效.

能够使用输出参数 - 如果发送返回一行的内联SQL,则只能返回记录集.使用sprocs,您可以将它们作为输出参数返回,这相当快.

权限 - 当您发送内联SQL时,您必须向用户授予对表的权限,这比仅授予执行sproc的权限授予更多访问权限

逻辑分离 - 删除SQL生成代码并将其隔离在数据库中.

无需重新编译即可进行编辑 - 这可能会引起争议.您可以在sproc中编辑SQL,而无需重新编译应用程序.

查找表的使用位置 - 使用sprocs,如果要查找引用特定表的所有SQL语句,可以导出sproc代码并进行搜索.这比尝试在代码中找到它容易得多.

优化 - 当使用sprocs时,DBA更容易优化SQL并调整数据库.找到缺失的索引等更容易.

SQL注入攻击 - 正确编写的内联SQL可以防御攻击,但是sprocs更适合这种保护.

3> Jon Galloway..：

---

在许多情况下,存储过程实际上较慢,因为它们更加通用.虽然存储过程可以高度调整,但根据我的经验,它们有足够的开发和制度摩擦,一旦它们工作就会留在原地,因此存储过程往往会返回很多列"以防万一" - 因为你没有想要在每次更改应用程序时部署新的存储过程.另一方面,OR/M仅请求应用程序使用的列,这会减少网络流量,不必要的连接等.

4> Steve Morgan..：

---

这是一场激烈争论(例如,这里).

编写错误的存储过程和在应用程序中编写错误的数据访问逻辑一样容易.

我更喜欢Stored Procs,但那是因为我通常在企业环境中使用非常大而复杂的应用程序,在那里有专门的DBA负责保持数据库服务器的运行.

在其他情况下,我很高兴LINQ等数据访问技术可以处理优化问题.

但是,纯粹的表现不是唯一的考虑因素.安全性和配置管理等方面通常至少同样重要.

编辑:虽然Frans Bouma的文章确实很冗长,但它却忽略了一英里的安全问题.它5岁的事实也没有帮助它的相关性.

5> Joel Coehoor..：

---

对于大多数现代数据库,存储过程与参数化或准备好的查询之间没有明显的速度差异,因为数据库还将缓存这些查询的执行计划.

请注意,参数化查询与ad hoc sql不同.

今天仍然支持存储过程的主要原因更多地与安全性有关.如果您独占使用存储过程,则可以为应用程序的用户禁用INSERT,SELECT,UPDATE,DELETE,ALTER,DROP和CREATE等权限,只保留EXECUTE.

这为二阶 sql注入提供了一些额外的保护.参数化查询仅防止一阶注入.

6> 小智..：

---

安全性是尚无人提及的受益于存储过程的一个主题。如果仅通过存储过程通过数据访问来构建应用程序，则可以锁定数据库，以便仅通过这些存储过程进行访问。因此，即使有人获得了数据库ID和密码，他们在查看或操作该数据库方面也会受到限制。