传统观点认为，存储过程总是更快。因此，由于它们总是更快，请一直使用它们。

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

解决方案

回答

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

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

回答

对于SQL代码频繁运行的情况，因为数据库将令牌化的令牌存储在内存中，所以存储的proc非常有用。如果我们在存储的proc之外重复运行相同的代码，则可能会导致数据库性能不断下降，从而一遍又一遍地重新解析相同的代码。

我通常经常将代码称为存储的proc或者SqlCommand(.NET)对象，并根据需要执行多次。

回答

阅读有关Frans Bouma的出色文章(如果有偏见的话)。

回答

在许多情况下，存储过程实际上速度较慢，因为它们的通用性更高。尽管可以对存储过程进行高度调整，但根据我的经验，有足够的开发和机构上的摩擦，一旦工作，它们就会留在原处，因此，存储过程通常会"以防万一"返回很多列，因为我们不想每次更改应用程序时都将部署新的存储过程。另一方面，OR / M仅请求应用程序正在使用的列，从而减少了网络流量，不必要的联接等。

回答

我只能说的就是SQL Server。在该平台上，存储过程很可爱，因为服务器存储了执行计划，在大多数情况下，这可以大大提高性能。我说"在大多数情况下"是因为，如果SP具有广泛变化的执行路径，则我们可能会获得次优的性能。但是，即使在这些情况下，对SP进行一些明智的重构也可以加快处理速度。

回答

这场辩论不断展开(例如，在这里)。

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

我更喜欢存储过程，但这是因为我通常在企业环境中使用大型和复杂的应用程序，在该环境中，有专门的DBA负责保持数据库服务器的良好运行。

在其他情况下，我很高兴让LINQ等数据访问技术照顾到优化。

但是，纯粹的性能并不是唯一的考虑因素。安全性和配置管理等方面通常至少同样重要。

编辑：尽管Frans Bouma的文章确实很冗长，但在安全性方面却少了一点。它已有5年的历史，也无济于事。

回答

我更喜欢在合理的情况下使用SP。无论如何，在SQL Server中，与参数化查询相比，SP的性能没有优势。

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

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

回答

在大多数现代数据库上，存储过程与参数化查询或者预准备查询之间没有明显的速度差异，因为数据库还将缓存这些查询的执行计划。

请注意，参数化查询与即席sql不同。

imo今天仍然支持存储过程的主要原因与安全性有关。如果仅使用存储过程，则可以为应用程序用户禁用INSERT，SELECT，UPDATE，DELETE，ALTER，DROP和CREATE等权限，仅将其保留为EXECUTE。

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

回答

显然，实际绩效应在个别情况下进行衡量，而不是假设。但是，即使在存储过程影响性能的情况下，也有充分的理由使用它们：

• 应用程序开发人员并不总是最好的SQL编码器。存储过程对应用程序隐藏SQL。
• 存储过程自动使用绑定变量。应用程序开发人员通常避免使用绑定变量，因为它们看起来像不需要的代码，并且在小型测试系统中几乎没有好处。稍后，未能使用绑定变量可能会限制RDBMS性能。
• 存储过程创建了一个间接层，以后可能会有用。可以在数据库端更改实现细节(包括表结构)，而无需触及应用程序代码。
• 创建存储过程的练习对于记录系统的所有数据库交互可能很有用。当事情发生变化时，更容易更新文档。

也就是说，我通常将原始SQL粘贴在应用程序中，以便可以自己控制它。这取决于开发团队和理念。

回答

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.

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

• 预先准备的SQL
• 预先生成的查询执行计划
• 减少网络延迟
• 潜在的缓存优势

预先编写的SQL-与编译后的代码相比，与解释后的代码具有相似的优势，只是在非常微观的层面上。

还是优势？
在现代CPU上根本不是很引人注目，但是如果我们发送的一条SQL语句每秒非常庞大，多达一千亿次，那么解析开销可能会加起来。

预先生成的查询执行计划。
如果我们有许多JOIN，则排列可能变得非常难以管理(出于性能原因，现代优化器存在限制和界限)。由于优化程序试图找出"近乎最佳"，因此非常复杂的SQL具有独特，可测量的延迟(在我们调整DBMS之前，我已经看到一个复杂的查询仅需10秒钟就可以生成一个计划)，这并不陌生。执行计划。通常，存储过程会将其存储在内存中，因此可以避免这种开销。

还是优势？
大多数DBMS(最新版本)都将缓存INDIVIDUAL SQL语句的查询计划，从而极大地减少了存储的proc和临时SQL之间的性能差异。在某些警告和案例中并非如此，因此我们需要在目标DBMS上进行测试。

另外，越来越多的DBMS允许我们提供优化器路径计划(抽象查询计划)，以显着减少优化时间(对于即席SQL和存储过程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数据的共享内存访问权限，否则边缘将始终位于存储的procs中。

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

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

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

这些提供了查询的更通用版本，现代优化程序可以使用该版本来缓存(和重用)查询执行计划，从而在存储过程中获得很多性能优势。

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

还是一个劣势？
不必要。大多数DBMS都有能力将临时SQL"抽象"为参数化版本，从而或者多或者少地消除了两者之间的差异。有些是隐式执行此操作的，或者必须通过命令设置启用此操作(SQL Server：http://msdn.microsoft.com/zh-cn/library/ms175037.aspx，Oracle：http://www.praetoriate.com/oracle_tips_cursor_sharing。 htm)。

得到教训？
摩尔定律不断发展，每个版本的DBMS优化器都变得更加复杂。当然，我们可以将每个愚蠢的SQL语句放入存储的proc中，但要知道从事优化器工作的程序员非常聪明，并且一直在寻找提高性能的方法。最终(如果还没有的话)(特别是平均)，临时SQL性能将与存储过程的性能区分开来，因此，任何种类的大型存储过程都仅出于"性能原因"而使用**当然，这听起来像我过早的优化。

无论如何，我认为如果避免出现极端情况并使用相当原始的SQL，我们将不会注意到即席存储过程和存储过程之间的区别。

回答

使用存储过程进行CRUD操作可能会过头，但是这取决于所使用的工具以及我们自己的喜好(或者要求)。我更喜欢嵌入式SQL，但请确保使用参数化查询来防止SQL注入攻击。我保留了这本xkcd漫画的印刷品，以提醒我们如果不注意可能会出问题。

当我们使用多组数据返回单个数据集时，存储过程可以真正带来性能上的好处。在存储过程中处理数据集通常比通过电线在客户端进行处理更为有效。

回答

是的，大多数时候它们速度更快。 SQL组合也是一个巨大的性能调优领域。如果我使用的是后台办公型应用程序，我可能会跳过它们，但出于其他原因，我肯定会使用它们，这是出于其他原因的原因……也就是安全性。

回答

意识到这一点与问题无关，但是如果我们使用大量存储过程，请确保以一致的方式将它们置于某种源代码控制(例如，subversion或者git)下，并能够将更新从开发系统迁移到测试系统再迁移到生产系统。

如果手动完成此操作，却无法轻松审核代码所在的位置，这将很快成为一场噩梦。

回答

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

回答

恕我直言...

将" C_UD"操作限制为存储过程可以将数据完整性逻辑保持在一个位置。这也可以通过将" C_UD"操作限制到单个中间件层来完成。

可以将读取操作提供给应用程序，以便它们可以仅联接所需的表/列。

回答

也可以使用存储过程来代替参数化查询(或者即席查询)，以实现其他一些优点：

• 如果我们需要更正某些内容(排序顺序等)，则无需重新编译应用程序
• 我们可以拒绝访问该用户帐户的所有表，仅授予对存储过程的访问权限，并通过存储过程路由所有访问权限。这样，我们可以对所有输入进行自定义验证，这比表约束要灵活得多。

回答

使用存储过程的原因：

• 减少网络流量-我们必须通过网络发送SQL语句。使用sproc，我们可以批量执行SQL，这也更加有效。
• 缓存查询计划-第一次执行sproc时，SQL Server创建一个执行计划，并将其缓存以供重用。这对于频繁运行的小型查询尤其有效。
• 使用输出参数的能力-如果我们发送返回一行的内联SQL，则只能取回一个记录集。使用sproc，我们可以将它们作为输出参数重新获得，这要快得多。
• 权限-发送内联SQL时，我们必须向用户授予对表的权限，这比仅授予执行sproc的权限要授予更多的访问权限
• 逻辑分离-删除生成SQL的代码并将其隔离在数据库中。
• 无需重新编译即可进行编辑的能力-这可能会引起争议。我们可以在存储过程中编辑SQL，而不必重新编译应用程序。
• 查找在哪里使用表-对于sproc，如果要查找引用特定表的所有SQL语句，则可以导出sproc代码并进行搜索。这比尝试在代码中查找要容易得多。
• 优化-使用存储过程时，DBA可以更轻松地优化SQL并优化数据库。查找丢失的索引等比较容易。
• SQL注入攻击-正确编写的内联SQL可以抵御攻击，但存储过程对于这种保护更好。

回答

减少的网络流量-SP通常比动态SQL更糟糕。因为人们不会为每个选择都创建一个新的SP，所以如果我们只需要一个列，则会被告知使用具有他们所需列的SP，而忽略其余的列。获得更多的专栏，以及我们刚刚离开的网络使用量的减少。同样，使用SP时，我们往往会有很多客户端筛选。

缓存-MS-SQL不会对它们进行任何区别，因为MS-SQL 2000可能只有7个，但我不记得了。

权限-这不是问题，因为我所做的几乎所有工作都是通过Web进行的，或者具有一些可以进行所有数据库访问的中间应用程序层。我使用的唯一具有直接客户端到数据库访问权限的软件是3rd Party产品，该产品旨在让用户具有直接访问权限，并且基于授予用户权限的目的。是的，MS-SQL权限安全模型很烂！！！ (尚未花时间在2008年)作为最后一部分，我们希望了解一下有多少人仍在进行直接的客户端/服务器编程与Web和中间应用程序服务器编程；如果他们正在做大型项目，为什么没有ORM。

分离-人们会质疑我们为什么将业务逻辑置于中间层之外。另外，如果我们希望分离数据处理代码，则可以使用一些方法来将其放入数据库中。

编辑能力-我们不必担心什么测试和版本控制？也是客户端/服务器的问题，在网络世界中不是问题。

查找表-仅在可以识别使用它的SP的情况下，才会坚持使用版本控制系统，代理商抢劫案或者Visual Studio的工具进行查找。

优化-DBA应该使用数据库的工具来查找需要优化的查询。数据库可以告诉DBA，哪些语句占用了最多的时间和资源，并且可以从那里进行修复。对于复杂的SQL语句，如果简单的选择不必担心，则应告知程序员与DBA交谈。

SQL注入攻击-SP没有提供更好的保护。他们唯一得到点头是，他们中的大多数人都使用参数与动态SQL进行教学，大多数示例都忽略了参数。

回答

在2007年，我在一个项目中，我们通过ORM使用MS SQL Server。我们有2个成长中的大表，这些表在SQL Server上的加载时间长达7-8秒。在制作了两个大型的存储SQL过程并通过查询计划程序对其进行优化之后，每个数据库的加载时间降到了20毫秒以内，因此显然仍然有效率的原因使用存储SQL过程。

话虽如此，我们发现存储过程的最重要好处是增加了维护性，安全性，数据完整性以及将业务逻辑与中间件逻辑解耦，使所有中间件逻辑都从重用这两个过程中受益。

我们的ORM供应商通常宣称，触发许多小型SQL查询比获取大型联接数据集更有效。我们的经验(令我们惊讶)显示了其他东西。

当然，这在机器，网络，操作系统，SQL Server，应用程序框架，ORM框架和语言实现之间可能有所不同，因此请衡量任何收益，我们认为我们可能会从其他事情中得到好处。

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