What are the pros and cons to keeping SQL in Stored Procs versus Code将SQL保留在C＃源代码或存储的Procs中有哪些优点/缺点？ 我一直在和一个朋友在我们正在研究的一个开源项目(C＃ASP.NET论坛)上讨论这个问题。 目前，大多数数据库访问是通过在C＃中构建SQL内联并调用SQL Server DB来完成的。 因此，我正在尝试确定，对于该特定项目，哪一个最好。 到目前为止，我有： 代码的优点：
存储过程的优点：
我不喜欢存储过程
无论如何，当数据类型改变，或者想要返回额外的列或其他内容时，最终还是要重新编译它。总体上，您可以从应用程序下方"透明地"更改SQL的次数很小
包括C＃在内的编程语言具有令人惊奇的东西，称为函数。这意味着您可以从多个位置调用同一代码块！惊人！然后，您可以将可重用的SQL代码放在其中之一中，或者，如果您想获得真正的高科技，则可以使用为您提供帮助的库。我相信它们被称为"对象关系映射器"，并且如今非常普遍。
同意，这就是为什么storedprocs是一件坏事的原因。与将代码重构为SQL块相比，将代码重构和分解(分解为较小的部分)要容易得多。
为什么Windows应用程序直接连接到中央数据库？这似乎是一个巨大的安全漏洞，成为瓶颈，因为它排除了服务器端缓存。他们不应该通过Web服务或类似的Web服务器进行连接吗？ 那么，推送1个新的proc还是4个新的Web服务器？ 在这种情况下，推送一个新的存储过程比较容易，但是根据我的经验，95％的"推送更改"影响代码而不是数据库。如果您要在当月向Web服务器推送20项内容，向数据库推送1项内容，那么如果您将21件事推送至Web服务器，将零值推送至数据库，则几乎不会损失太多。
你能解释一下吗？我不明白特别要注意的是，这些存储程序可能不在源代码控制中，因此无法通过基于Web的SCM浏览器等进行访问。 更多缺点： Storedprocs存在于数据库中，该数据库在外界看来像一个黑匣子。想要将它们放入源代码控制之类的简单事情变成了噩梦。 还有纯粹的问题。如果您想向CEO证明为什么要花700万美元建立一些论坛，那么将所有内容分解为一百万层可能是有意义的，但否则，为每件小事创建一个storageproc就是无用的驴子工作。效益。 当前正在其他几个线程上对此进行讨论。我一直是存储过程的拥护者，尽管已经提出了一些有关Linq to Sql的很好的论据。 将查询嵌入代码中可以使您紧密地与数据模型耦合。存储过程是契约式编程的一种好形式，这意味着只要维护以存储过程的输入和输出表示的契约，DBA即可自由更改过程中的数据模型和代码。 当查询隐藏在代码中而不是集中在一个易于管理的中央位置时，调整生产数据库可能非常困难。 [编辑]这是另一个当前的讨论 我认为您不能就这个问题投赞成票或反对票。这完全取决于您的应用程序的设计。 我完全反对在3层环境中使用SP的情况，在3层环境中，您有一个应用服务器在前。在这种环境中，您的应用程序服务器可以运行您的业务逻辑。如果您另外使用SP，则会开始在整个系统上分发业务逻辑的实现，并且将变得非常不清楚，由谁来负责。最终，您将获得一个应用服务器，该服务器除了以下操作外基本上什么都不做：
因此，最终，您的中间层将在这个非常酷的4服务器集群上运行，每个集群都配备16个CPU，实际上它什么都不做！真是浪费！ 如果您有一个胖gui客户端直接连接到您的数据库或什至更多的应用程序，那就另当别论了。在这种情况下，SP可以充当某种伪中间层，使您的应用程序与数据模型脱钩，并提供可控制的访问。
实际上，我认为您对此感到很落后。恕我直言，代码中的SQL很难维护，因为：
将存储过程视为从数据库对象调用的方法-它们更易于重用，只有一个地方可以编辑，并且如果您更改了数据库提供程序，则更改将在存储过程中而不是代码中发生。 也就是说，正如Stu在我之前所说的那样，存储过程的性能提升是最小的，您还不能在存储过程中设置断点(尚未)。 CON 我发现，在扩展行为时，在存储过程中进行大量处理会使DB服务器变得僵硬。 但是，如果您有多个运行代码的服务器，那么与sql-server相比，在程序中进行所有此类操作可能会使您进行更多扩展。当然，这不适用于仅进行正常获取或更新的存储过程，而不适用于执行更多处理(如遍历数据集)的存储过程。 PROS 存储过程的性能优势通常可以忽略不计。 存储过程的更多优点：
我属于代码方面。我们构建了所有应用程序(Web和客户端)都使用的数据访问层，因此从这个角度来看它是DRY。它简化了数据库部署，因为我们只需要确保表架构的正确性即可。由于我们不必查看源代码和数据库，因此它简化了代码维护。 我对数据模型的紧密耦合并没有太大的问题，因为我看不到有什么可能真正打破这种耦合。应用程序及其数据是固有耦合的。 存储过程的优点： 代码审查更容易。 耦合较少，因此更易于测试。 更容易调整。 从网络流量的角度来看，性能通常会更好-如果有游标或类似游标，则不会多次访问数据库 您可以更轻松地保护对数据的访问，删除对表的直接访问，通过proc加强安全性-这还使您可以相对迅速地找到更新表的任何代码。 如果还涉及其他服务(例如Reporting Services)，则可能会发现将所有逻辑存储在存储过程中而不是代码中会变得更加容易，并且必须对其进行复制 缺点： 对于开发人员来说更难管理：脚本的版本控制：每个人都有自己的数据库吗，版本控制系统是否与数据库和IDE集成在一起？ 存储过程。 如果错误失误或逻辑有所改变，则无需重新编译项目。此外，它还允许从不同来源进行访问，而不仅仅是在项目中为查询编写代码的地方。 我认为维护存储过程并不困难，您不应该直接在数据库中对它们进行编码，而应该先在单独的文件中进行编码，然后就可以在需要设置的任何DB上运行它们。 在某些情况下，用代码动态创建的sql比存储的proc具有更好的性能。如果您创建了一个存储的proc(比如说sp_customersearch)，因为它必须非常灵活，所以它会带有数十个参数，因此变得非常复杂，您可能会在运行时在代码中生成一个简单得多的sql语句。 有人可能会争辩说，这只是将某些处理从SQL移到了Web服务器，但是总的来说这将是一件好事。 这项技术的另一个优点是，如果您正在使用SQL事件探查器查找，则可以看到生成的查询并对其进行调试，这比看到带有20个参数的存储的proc调用要容易得多。 我喜欢存储过程，不知道我能够使用存储过程对应用程序进行更改的次数，该过程不会导致应用程序停机。 Transact SQL的忠实拥护者，调优大型查询对我来说非常有用。大约6年没有写任何内联SQL了！ 您列出了存储过程的2个优点： 性能-并非如此。在Sql 2000或更高版本中，查询计划的优化非常好，并且可以进行缓存。我确信Oracle等会做类似的事情。我认为不再有需要提高性能的存储了。 安全？为什么存储过程会更安全？除非您有一个非常不安全的数据库，否则所有访问都将来自您的DBA或您的应用程序。始终参数化所有查询-切勿内联来自用户输入的内容，这样就可以了。 无论如何，这是性能的最佳实践。 Linq绝对是我现在进行新项目的方式。看到类似的帖子。 @Keith
正如Komradekatz建议的那样，您可以禁止访问表(对于连接到DB的用户名/密码组合)，并且仅允许SP访问。这样，如果有人将用户名和密码获取到您的数据库，则他们可以执行SP，但不能访问表或数据库的任何其他部分。 (当然，执行存储过程可能会为他们提供所需的所有数据，但这取决于可用的存储过程。授予他们对表的访问权使他们可以访问所有内容。) 这样想吧
您有4个网络服务器和一堆使用相同SQL代码的Windows应用程序 我更喜欢存储过程
针对proc进行性能测试也更容易，将其放入查询分析器中 无需运行探查器即可准确查看正在调用的内容 只是我的2美分 在安全性方面，存储过程要安全得多。有些人认为所有访问都将通过应用程序进行。许多人忘记的事情是，大多数安全漏洞来自公司内部。考虑一下有多少开发人员知道您的应用程序的"隐藏"用户名和密码？ 而且，正如MatthieuF所指出的，由于应用程序(无论是在台式机还是Web服务器上)与数据库服务器之间的往返次数较少，因此可以大大提高性能。 以我的经验，通过存储过程抽象数据模型还极大地提高了可维护性。作为过去必须维护许多数据库的人，当面对所需的模型更改以能够简单地更改一个或两个存储过程并使更改对所有外部应用程序完全透明时，这是一种轻松的选择。很多时候，您的应用程序不是唯一指向数据库的应用程序-还有其他应用程序，报表解决方案等。因此，跟踪所有这些受影响的点可能会带来对表开放访问的麻烦。 我还将在plus列中进行检查，以将SQL编程交到专门从事SQL编程的人员手中，并使SP简化隔离和测试/优化代码。 我看到的一个缺点是，许多语言都不允许传递表参数，因此传递未知数的数据值可能会很烦人，并且某些语言仍无法处理从单个存储过程中检索多个结果集的问题(尽管在这方面，后者不会使SP变得比内联SQL更糟。
最好地利用您的应用代码：处理逻辑。
您可以调试存储过程，但是会发现更容易调试和维护代码中的逻辑。 此外，带有可选搜索参数的存储过程效率很低，因为您必须事先指定所有可能的参数，而有时不可能进行复杂的搜索，因为您无法预测参数将在搜索中重复多少次。 我更喜欢将它们保留在代码中(使用ORM，而不是内联或即席使用)，这样它们就可以由源代码控制覆盖，而不必处理.sql文件。 同样，存储过程并不是天生就更安全。您可以像使用内联一样轻松地用sproc编写错误的查询。参数化的内联查询可以像proc一样安全。 我们现在将存储过程与Oracle DB一起使用。我们还使用Subversion。所有存储过程都将创建为.pkb和.pks文件，并保存在Subversion中。我之前做过内联SQL，这真是太痛苦了！我非常喜欢我们在这里做的方式。创建和测试新的存储过程比在代码中轻松得多。 有个 如果将其放入存储过程中，则绝对易于维护。如果涉及复杂的逻辑，将来可能会改变，那么当您有多个客户端连接时，将其放入数据库中绝对是一个好主意。例如，我现在正在开发一个具有最终用户Web界面和管理桌面应用程序的应用程序，这两个应用程序(显然)都共享一个数据库，并且我试图在数据库中保持尽可能多的逻辑。这是DRY原理的完美示例。 我坚决支持存储过程，假设您不作弊并在存储过程中使用动态SQL。首先，使用存储的proc允许dba在存储的proc级别而不是表级别设置权限。这不仅对打击SQL注入行为至关重要，而且对于防止内部人员直接访问数据库和更改事物也至关重要。这是帮助防止欺诈的一种方法。除非通过繁琐的程序，否则不得访问包含个人信息(SSN，信用卡号等)或以任何方式创建财务交易的数据库。如果使用任何其他方法，则将数据库开放给公司中的个人使用，以创建虚假的财务交易或窃取可用于身份盗用的数据。 与从应用程序发送的SQL相比，存储的proc也更容易维护和性能调整。它们还使dba能够查看数据库结构更改对数据访问方式的影响。我从未见过一个好的dba，它允许动态访问数据库。 我参加的Microsoft TechEd安全会议的建议之一是通过存储的procs进行所有调用，并拒绝直接访问表。据称这种方法可提供额外的安全性。我不确定仅出于安全性考虑是否值得，但是如果您已经在使用存储的proc，就不会感到伤害。 较小的原木 尚未提到的存储过程的另一个小优点：涉及SQL流量时，基于sp的数据访问产生的流量要少得多。当您监视流量以进行分析和分析时，这一点很重要-日志将变得更小且可读。 SQL存储的proc不会提高查询的性能 我不是存储过程的忠实拥护者，但是在一种情况下使用它们：
当查询很大时，最好将其作为存储过程存储在数据库中，而不是从代码中发送。这样，就不会发送大量的字符串字符从应用程序服务器到数据库，而只会发送 当数据库服务器和Web服务器不在同一网络上(例如，Internet通信)时，这是多余的。即使不是这种情况，压力太大也意味着大量浪费带宽。 但是，伙计们，管理起来真是太糟糕了。我会尽量避免他们。 很显然，与在代码中构造SQL相比，使用存储过程具有多个优势。 存储过程更具可维护性，因为：
尝试构建可维护的应用程序时，代码重复是最糟糕的事情！ 当您发现需要在多个地方纠正的逻辑错误时，会发生什么？您更容易忘记更改复制和粘贴代码的最后一个位置。 我认为，性能和安全性方面的收益是额外的优势。您仍然可以编写不安全/效率低下的SQL存储过程。
将所有存储过程编写脚本以在另一个数据库中创建并不是很困难。实际上-因为没有主键/外键值得担心，所以比导出表更容易。 我通常写OO代码。我怀疑你们中的大多数人也可能这样做。在这种情况下，对我来说显而易见的是，所有业务逻辑(包括SQL查询)都属于类定义。拆分逻辑，使其一部分驻留在对象模型中，而一部分驻留在数据库中，这比将业务逻辑放入用户界面更好。 在较早的答案中，关于存储过程的安全性好处已经说了很多。这些分为两大类： 1)限制直接访问数据。在某些情况下，这绝对是重要的，而且当您遇到一个问题时，存储的procs几乎是您唯一的选择。以我的经验，这种情况是例外而不是规则。 2)SQL注入/参数化查询。这个异议是红鲱鱼。内联SQL-甚至是动态生成的内联SQL-都可以像任何存储的proc一样完全参数化，并且可以用任何值得赞扬的现代语言轻松地完成。这两种方式都没有优势。 ("懒惰的开发人员可能不会为使用参数而烦恼"是无效的反对。如果您的团队中有一些开发人员只希望将用户数据连接到他们的SQL中而不是使用参数，则首先尝试对其进行教育，然后将其解雇如果那行不通，就像您和其他具有不良，明显有害习惯的开发人员一样。) 到目前为止，我还没有看到一些东西：最了解数据库的人并不总是写应用程序代码的人。存储过程为数据库人员提供了一种与真正不想学习太多有关SQL的程序员进行交互的方式。大型数据库(尤其是传统数据库)并不是最容易完全理解的东西，因此程序员可能只喜欢一个简单的界面，该界面可以满足他们的需求：让DBA弄清楚如何联接这17个表来实现这一目标。 话虽如此，用于编写存储过程的语言(PL / SQL是一个臭名昭著的示例)非常残酷。它们通常不提供您在当今流行的命令式，OOP或功能性语言中看到的任何精美信息。想想COBOL。 因此，坚持只提取关系细节而不是包含业务逻辑的存储过程。 @Terrapin-存储过程同样容易受到注入攻击。就像我说的：
这适用于sproc和动态Sql。 我不确定不重新编译您的应用是否有好处。我的意思是，您已经针对该代码(应用程序和数据库)运行了单元测试，然后仍然再次上线。 @Guy-是的，没错，存储过程确实可以让您控制应用程序用户，以便他们只能执行存储过程，而不能执行基础操作。 我的问题是：如果通过您的应用程序进行的所有访问(使用连接和权限有限的用户来更新/插入等)，此额外级别是否会增加安全性或额外的管理？ 我的看法是后者。如果他们破坏了您的应用程序，可以重新编写应用程序，则他们可以使用许多其他攻击。 如果它们动态地内联代码，则仍然可以针对那些sproc执行sql注入，因此黄金法则仍然适用，所有用户输入都必须始终参数化。 我是SPROC的代码的大力支持者。原因之一是保持代码紧密耦合，其次是源代码控制的简便性，而无需使用许多自定义工具。 在我们的DAL中，如果我们有非常复杂的SQL语句，通常会将它们作为资源文件包含在内，并根据需要进行更新(这也可以是一个单独的程序集，并且可以按db换出，等等。)。 这样可以将我们的代码和sql调用存储在相同的版本控制中，而无需"忘记"运行某些外部应用程序进行更新。 @Keith
存储过程为SQL注入攻击提供了固有的保护。 但是，您并没有得到完全的保护，因为您仍然可以编写容易受到此类攻击的存储过程(即，存储过程中的动态SQL)。 我在其他答案中没有发现的一点如下： 如果在您的环境中数据库及其架构是体系结构的核心，而应用程序则扮演着更重要的角色，那么大量使用存储过程可能是有道理的，这可能有助于为需要访问的所有应用程序提供一个基础层DB，从而减少代码重复(例如，您确定所有访问DB的应用程序将始终以C＃或其他.NET语言编写吗？)。 另一方面，如果应用程序扮演着更重要的角色，而数据库更多地充当了该应用程序的后备存储，那么明智的做法是减少存储过程的使用，并通过提供一个通用的持久层来减少代码重复，可能基于ORM工具/框架。 在这两种情况下，都不要将数据库视为存储过程的便捷存储库，这一点很重要。将它们保存在版本控制系统中的源文件中，并尝试使其自动化尽可能多(这实际上对所有与架构相关的工件都是有效的)。 我想再投票赞成使用存储的proc(尽管在维护和版本控制方面可能会带来麻烦)作为限制直接访问基础表以提高安全性的一种方式。 我对存储过程的投票；作为接近数据的抽象层，具有集高效，可被许多"客户端"(客户端语言)重用。 T-SQL语言有点原始(我想这是SO的大多数C＃人士都知道的)，但是Oracle的PL / SQL与任何现代编程语言都差不多。 至于版本控制，只需将存储过程代码放在版本控制下的文本文件中，然后运行脚本以在数据库中创建proc。 没人提到单元测试！ 如果您有一个saveOrder方法，则可以在其中调用多个方法，并为每个方法创建一个单元测试，但是，如果仅调用存储过程，则无法这样做。 当存储过程用于在应用程序和数据库之间站立时，它们是最糟糕的。可以通过视图更好地解决上述使用它们的许多原因。 安全论点是虚假的。它将安全问题从应用程序转移到数据库。代码就是代码。我已经看到了存储过程，这些存储过程从应用程序中获取SQL，并使用它来构建受到SQL注入攻击的查询。 通常，它们往往会在所谓的数据库开发人员和所谓的应用程序开发人员之间造成裂痕。实际上，所有编写的代码都是应用程序代码，仅是执行上下文的不同。 使用LINQ，Rails ActiveRecord或Hibernate / NHibernate等丰富的SQL生成库可以使开发更快。在混合中插入存储过程会降低速度。 我更喜欢使用O / R映射器，例如LLBLGen Pro。 它为您提供了相对轻松的数据库可移植性，允许您使用强类型对象以与应用程序相同的语言编写数据库访问代码，而且我认为，您可以更加灵活地处理回退的数据。 实际上，能够使用强类型对象足以使用O / R映射器。 SQL注入攻击呈上升趋势。有人很容易找到此代码并在您的网站上进行注入攻击。您必须始终始终将查询参数化。最好永远不要在动态SQL查询上运行exec(@x)。 IMO，使用内联SQL并不是一个好主意。 某些人认为，存储过程很麻烦，因为存储过程是另一套与代码保持独立的项目。但是它们是可重用的，并且如果您最终在查询中发现错误，则可以在不重新编译的情况下对其进行修复。 在我工作的地方sproc的最大优点是，时间到了，我们可以使用更少的代码来移植到VB.NET(从VB6开始)。而且它的代码更少，因为我们对所有查询都使用了存储过程。 当我们需要更新查询而不是更新VB代码，重新编译并在所有计算机上重新安装它时，它也很有帮助。 您的编程语言和应用程序框架可能是：
如果这两个条件为两个，则跳过存储过程。 存储过程可以比代码更容易在数据库和源代码控制系统之间失去同步。应用程序代码也可以，但是当您进行持续集成时，它的可能性较小。 数据库就是这样，人们不可避免地要更改生产数据库，只是为了暂时摆脱困境。然后忘记在环境和源代码控制系统之间同步它。迟早，生产数据库将成为事实上的记录，而不是源代码控制系统-您陷入无法删除任何存储过程的情况，因为您不知道它是否正在被使用。 一个好的过程应该只允许通过适当的渠道对生产进行更改，以便您应该能够从源代码控制系统(无数据)重新构建数据库。但是，我只是说这是因为它可以完成，而且确实可以完成-在热闹的时刻，在大声呼唤客户的电话，经理垂涎三尺等等之间对生产数据库进行更改。 使用存储过程运行临时查询很尴尬–使用动态sql(或ORM)更容易完成，这可能是为我自己使用存储过程的最大缺点。 另一方面，在您进行更改但不需要重新部署应用程序代码的情况下，存储过程非常有用。它还允许您在通过网络发送数据之前对数据进行整形，在这种情况下，代码中的sql可能必须进行多次调用才能检索，而不是对数据进行整形(尽管现在有多种方法可以运行多个sql语句并在单个"调用中返回多个结果集""(如ADO.NET中的MARS)一样，可能会导致更多数据通过网络传输。 不过，我不买任何其他有关性能和安全性的论据。无论是好是坏，都可以平等地加以控制。 程序员希望在其应用程序中使用代码。 DBA希望在数据库中使用它。 如果两者都有，则可以使用存储过程在两者之间进行分配，而程序员不必担心所有这些表如何连接在一起等等。(对不起，我知道您想控制一切。) 。 我们有一个第三方应用程序，允许在数据库的"视图"或"存储过程"上创建自定义报告。如果我将所有逻辑都放在另一个应用程序的代码中，则无法重用它。如果您在使用数据库编写所有应用程序的情况下，这不是问题。 我还没有找到在源代码管理中轻松维护存储的proc的好方法，从而使其与代码库一样无缝。只是没有发生。仅此一项就使我值得将SQL放入代码中。在现代系统上，性能差异可以忽略不计。 坚定地参加"存储的Procs对CRUD /业务逻辑使用不利"阵营。我了解报告，数据导入等方面的需求 在这里写...
存储过程的优点 我不确定其他数据库，但是您可以在大型机上的db2中以宿主语言创建存储过程，这使它们非常强大。
首选存储过程，因为： 缺点-如果WHERE子句在使用条件上有很大差异并且需要高性能，则存在问题。 对于Microsoft SQL Server，应尽可能使用存储过程来协助执行计划缓存和重用。为什么要优化计划重用？因为执行计划的生成相当昂贵。 尽管在SQL Server的更高版本(尤其是2005和2008)中，对临时查询的执行计划的缓存和重用已得到了显着改善，但处理存储过程时计划重用的问题仍然比临时查询少得多。例如，仅在计划文本完全匹配时，SQL Server才会重新使用执行计划-直到注释和空白为止，例如，如果下面的每一行SQL要独立执行，则它们都不会使用相同的执行计划：
最重要的是，如果您未明确指定参数的类型，则SQL Server很有可能会出错，例如，如果使用输入4执行上述查询，则SQL Server将对参数进行参数化。使用@id作为SMALLINT(或可能是TINYINT)的查询，因此，如果您随后使用@id说4000来执行相同的查询，则SQL Server会将其参数化为INT，并且不会重复使用相同的缓存。 我认为还存在其他一些问题，老实说，大多数问题都可以解决-尤其是在SQL Server的更高版本中，但是存储过程通常为您提供更多控制权。 |
