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

小开

我不知道他们是否更快。我喜欢使用 ORM 进行数据访问(而不是重新发明轮子) ，但我意识到这并不总是一个可行的选择。

Frans Bouma 有一篇关于这个主题的好文章: http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx

小开

存储进程非常适合 SQL 代码频繁运行的情况，因为数据库将它标记化后的代码存储在内存中。如果在存储过程之外重复运行相同的代码，那么在反复修复相同的代码时，数据库会对性能造成影响。

我通常将代码作为存储过程或 SqlCommand (。NET)对象，并根据需要执行多次。

小开

Read Frans Bouma's excellent post (if a bit biased) on that.

小开

在许多情况下，存储过程实际上更慢，因为它们更通用化。虽然存储过程可以进行高度优化，但根据我的经验，存储过程的开发和制度摩擦足以让它们在工作时保持不变，所以存储过程往往会返回大量列“以防万一”——因为您不想在每次更改应用程序时都部署一个新的存储过程。另一方面，OR/M 只请求应用程序正在使用的列，这减少了网络流量、不必要的连接等。

小开

我只能跟 SQL 服务器说话。在该平台中，存储过程非常可爱，因为服务器存储执行计划，这在大多数情况下可以大大提高性能。我之所以说“在大多数情况下”，是因为如果 SP 的执行路径差异很大，那么性能可能就不是最优的。但是，即使在这些情况下，一些开明的 SP 重构也可以加快速度。

小开

这是一场持续不断的争论(例如，给你)。

编写糟糕的存储过程和在应用程序中编写糟糕的数据访问逻辑一样容易。

我倾向于使用存储处理器，但这是因为我通常在一个企业环境中使用非常大和复杂的应用程序，在这种环境中，有专门的 DBA 负责保持数据库服务器运行良好。

在其他情况下，我很乐意使用诸如 LINQ 之类的数据访问技术来进行优化。

不过，纯性能并不是唯一的考虑因素，安全性和组态管理等方面通常也同样重要。

编辑: 虽然 Frans Bouma 的文章确实冗长，但是它在安全问题上完全没有抓住要点。它已经有5年的历史了，这也不利于它的相关性。

小开

我更喜欢在有意义的时候使用 SP。无论如何，在 SQLServer 中，与参数化查询相比，SP 没有性能优势。

然而，在我目前的工作中，我的老板提到我们被迫使用 SP，因为我们的客户需要它们。他们觉得自己更有安全感。我在这里的时间还不够长，无法看到我们是否正在实现基于角色的安全性，但我有一种感觉，我们正在实现这种安全性。

所以在这种情况下，顾客的感受胜过其他所有的争论。

小开

There is no noticeable speed difference for stored procedures vs parameterized or prepared queries on most modern databases, because the database will also cache execution plans for those queries.

注意，参数化查询与 ad hoc sql 不同。

如今，imo 仍然青睐存储过程的主要原因与安全性有更多的关系。如果使用存储过程独家，则可以为应用程序的用户禁用 INSERT、 SELECT、 UPDATE、 DELETE、 ALTER、 DROP 和 CREATE 等权限，只使用 EXECUTE。

这对 二等兵 sql 注入提供了一些额外的保护。参数化查询只对 一等兵注入提供保护。

小开

显然，实际表现应该在个别情况下衡量，而不是假设。但是，即使在存储过程的性能为受阻的情况下，也有充分的理由使用它们:

应用程序开发人员并不总是最好的 SQL 编码人员，存储过程对应用程序隐藏了 SQL。
Stored procedures automatically use bind variables. Application developers often avoid bind variables because they seem like unneeded code and show little benefit in small test systems. Later on, the failure to use bind variables can throttle RDBMS performance.
存储过程创建了一个间接层，以后可能会有用。在不触及应用程序代码的情况下，可以在数据库端更改实现细节(包括表结构)。
创建存储过程的练习对于记录系统的所有数据库交互非常有用。当事情发生变化时，更新文档更容易。

也就是说，我通常在应用程序中使用原始 SQL，这样我就可以自己控制它。这取决于您的开发团队和哲学。

小开

最佳答案

注意，这是对未调节到特定的存储过程的一般性查看一些数据库管理系统(甚至不同的数据库管理系统) 版本的相同数据库管理系统!) 与此相反，所以你会想再次检查目标 DBMS 在假设这一切仍然有效之前。

我已经断断续续地做了将近十年的 Sybase ASE、 MySQL 和 SQL Server DBA (以及 C、 PHP、 PL/SQL、 C # .NET 和 Ruby 中的应用程序开发)。因此，在这场(有时)圣战中，我没有什么特别的目的。

存储处理器的历史性能优势通常来自以下方面(没有特定的顺序) :

预解析 SQL
预生成的查询执行计划
减少网络延迟
缓存的潜在好处

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

还有优势吗？ 在现代的 CPU 上并不是很明显，但是如果您每秒发送一条非常大的 SQL 语句110亿次，那么解析开销就会增加。

预生成的查询执行计划 。如果您有许多 JOIN，那么排列可能会变得非常难以管理(出于性能原因，现代优化器有限制和截止值)。对于非常复杂的 SQL 来说，由于优化器试图找出“接近最佳”的执行计划，因此具有明显的、可测量的延迟(我见过一个复杂的查询仅仅生成一个计划就需要10多秒，在我们调整 DBMS 之前)并不是不可能的。一般来说，存储过程将把它存储在内存中，这样您就可以避免这种开销。

还有优势吗？ Most DBMS' (the latest editions) will cache the query plans for INDIVIDUAL SQL statements, greatly reducing the performance differential between stored procs and ad hoc SQL. There are some caveats and cases in which this isn't the case, so you'll need to test on your target DBMS.

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

缓存查询计划不是性能万能药，有时生成的查询计划不是最佳的。例如，如果您发送 < code > SELECT * 表中 id 在1和 9999999 ，DBMS 可以选择一个全表扫描而不是索引扫描一下，因为你要把每一行都抓起来 in the table (so sayeth the 如果这是缓存的 version, then you can get poor 当你稍后发送 选择 * FROM table WHERE id BETWEEN 1 AND 2 背后的原因是超出了这个职位的范围，但是有关进一步资料，请参阅: Http://www.microsoft.com/technet/prodtechnol/sql/2005/frcqupln.mspx and 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 中。这样可以避免将大量数据转移到客户端，只是让客户端发送一些信息回来(或者根本不发送!).类似于在数据库管理系统和代码中执行 JOIN (每个人都喜欢的卧槽!)

还有优势吗？ A)现代的1Gb (和10Gb 以上)以太网真的让这一点可以忽略不计。 B)取决于你的网络有多饱和——为什么毫无理由地来回推送几兆的数据？

缓存的潜在好处 如果 DBMS 上有足够的内存，并且所需的数据位于服务器的内存中，那么执行服务器端数据转换可能会更快。

还有优势吗？ 除非您的应用程序具有对 DBMS 数据的共享内存访问权限，否则边缘永远是存储进程。

Of course, no discussion of Stored Procedure optimization would be complete without a discussion of parameterized and ad hoc SQL.

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

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

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

Ad Hoc SQL 只需打开 DBMS 的控制台窗口并键入 SQL 语句。在过去，由于 DBMS 无法像参数化/存储过程方法那样对查询进行预优化，因此这些查询的性能(平均而言)是“最差”的。

还是不利吗？ 不一定。大多数 DBMS 都能够将即席 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 性能将变得无法区分(平均而言!)从存储过程的性能来看，所以任何类型的巨大存储过程使用 * * 仅仅是为了“性能原因”。

Anyway, I think if you avoid the edge cases and have fairly vanilla SQL, you won't notice a difference between ad hoc and stored procedures.

小开

将存储过程用于 CRUD 操作可能有些过分，但这取决于所使用的工具和您自己的首选项(或需求)。我更喜欢内联 SQL，但我确保使用参数化查询来防止 SQL 注入攻击。我保留了这个 Xkcd 漫画的打印作为提醒什么可能会出错，如果你不小心。

当使用多组数据返回单组数据时，存储过程可以带来真正的性能优势。在存储过程中处理数据集通常比通过连接发送它们以便在客户端处理更有效。

小开

Yes, they are faster most of time. SQL composition is a huge performance tuning area too. If I am doing a back office type app I may skip them but anything production facing I use them for sure for all the reasons others spoke too...namely security.

小开

意识到这个问题有点跑题了，但是如果你使用了很多存储过程，确保有一个一致的方法将它们置于某种源代码控制之下(例如，subversion 或 git) ，并且能够将更新从你的开发系统迁移到测试系统，再迁移到生产系统。

当这是手工完成的，没有办法轻松地审计什么代码在哪里，这很快成为一个噩梦。

小开

作为存储过程的一个好处，还没有人提到的一个主题是安全性。如果只通过存储过程构建数据访问应用程序，则可以锁定数据库，以便 ONLY 访问是通过这些存储过程进行的。因此，即使有人获得了数据库 ID 和密码，他们也会受到对该数据库的查看或操作的限制。

小开

恕我直言。

将“ C _ UD”操作限制在存储过程中可以将数据完整性逻辑保持在一个位置。这也可以通过将“ C _ UD”操作限制到单个中间件层来实现。

可以向应用程序提供读操作，以便它们只能联接所需的表/列。

小开

还可以使用存储过程代替参数化查询(或特别查询) ，以获得其他一些优势:

如果你需要更正一些东西(排序顺序等) ，你不需要重新编译你的应用程序

您可以拒绝访问该用户帐户的所有表，只授予对存储过程的访问权限，并通过存储过程路由所有访问权限。通过这种方式，您可以对所有输入进行自定义验证，这比表约束更加灵活。

小开

使用存储过程的原因:

Reduce network traffic -- you have to send the SQL statement across the network. With sprocs, you can execute SQL in batches, which is also more efficient.

缓存查询计划 ——第一次执行 sproc 时，SQLServer 创建一个执行计划，该计划被缓存以供重用。这对于频繁运行的小型查询尤其有效。

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

Permissions -- when you send inline SQL, you have to grant permissions on the table(s) to the user, which is granting much more access than merely granting permission to execute a sproc

Separation of logic -- remove the SQL-generating code and segregate it in the database.

Ability to edit without recompiling -- this can be controversial. You can edit the SQL in a sproc without having to recompile the application.

查找表的使用位置 ——使用 sproc，如果希望查找引用特定表的所有 SQL 语句，可以导出 sproc 代码并搜索它。这比试图在代码中找到它要容易得多。

优化 ——当使用 sprocs 时，DBA 更容易优化 SQL 和调优数据库。更容易找到缺失的索引等等。

SQL 注入攻击 ——正确编写的内联 SQL 可以抵御攻击，但 sprocs 更适合这种保护。

小开

减少网络流量—— SP 通常比动态 SQL 更糟糕。因为人们不会为每个选择创建一个新的 SP，所以如果您只需要一个列，那么您被告知使用具有他们需要的列的 SP，并忽略其余的列。获得一个额外的专栏和任何较少的网络使用你刚刚消失。而且，当使用 SP 时，往往会有大量的客户端过滤。

缓存—— MS-SQL 没有区别对待它们，因为 MS-SQL 2000可能是7，但我不记得了。

权限——没有问题，因为我做的几乎所有事情都是 web 或者有一些中间的应用程序层来完成所有的数据库访问。我工作的唯一软件，有直接的客户端到数据库访问是第三方产品的设计，用户有直接访问，并围绕给予用户权限。是的 MS-SQL 权限安全模型 SUCKS! ! ！(还没有花时间在2008年)作为最后一部分，我们希望看到一个调查，有多少人仍然在做直接客户机/服务器编程与网络和中间应用服务器编程; 如果他们正在做大型项目，为什么没有 ORM。

分离——人们会质疑为什么要将业务逻辑置于中间层之外。此外，如果您正在寻找独立的数据处理代码，有一些方法可以做到这一点，而无需将其放入数据库中。

编辑能力——您不需要担心什么测试和版本控制？也只是客户端/服务器的问题，在网络世界中没有问题。

Find the table -- Only if you can identify the SP that use it, will stick with the tools of the version control system, agent ransack or visual studio to find.

优化——您的 DBA 应该使用数据库工具来查找需要优化的查询。数据库可以告诉 DBA 什么语句占用了最多的时间和资源，他们可以从那里进行修复。对于复杂的 SQL 语句，应该告诉程序员与 DBA 对话，如果简单的选择不需要担心的话。

SQL 注入攻击—— SP 没有提供更好的保护。他们唯一得到认可的是，他们大多数教授使用参数与动态 SQL 大多数例子忽略参数。

小开

2007年，我参与了一个项目，我们通过 ORM 使用 MSSQLServer。我们有两个大的，不断增长的表，它们在 SQLServer 上需要7-8秒的加载时间。在制作了2个大型的存储 SQL 过程并从查询规划器中对它们进行优化之后，每个 DB 加载时间降低到了不到20毫秒，因此显然仍然有效率的原因要使用存储 SQL 过程。

尽管如此，我们发现存储过程最重要的好处是增加了维护简单性、安全性、数据完整性和将业务逻辑与中间件逻辑分离，使所有中间件逻辑从2个过程的重用中受益。

Our ORM vendor made the usual claim that firing off many small SQL queries were going to be more efficient than fetching large, joined data sets. Our experience (to our surprise) showed something else.

当然，这可能因机器、网络、操作系统、 SQL 服务器、应用程序框架、 ORM 框架和语言实现的不同而有所不同，所以要衡量做其他事情可能带来的好处。

直到我们进行了基准测试，才发现问题出在 ORM 和承担所有负载的数据库之间。

小开

对我来说，存储过程的一个优点是与主机语言无关: 您可以从 C、 Python、 PHP 或任何应用程序切换到另一种编程语言，而无需重写代码。此外，像批量操作这样的一些特性提高了真正的性能，而且不容易获得(一点也不容易?)用主机语言。