首页 文章

将SQL保存在存储过程与代码中的优缺点是什么[关闭]

提问于
浏览 次
274

在您的C＃源代码或存储过程中保留SQL有哪些优点/缺点？我一直在与一位朋友讨论我们正在开发的一个开源项目（C＃ASP.NET论坛） . 目前，大多数数据库访问都是通过在C＃中构建SQL内联并调用SQL Server DB来完成的 . 所以我试图确定哪个，对于这个特定的项目，哪个是最好的 .

到目前为止我有：

代码中的优点：

  • 易于维护 - 无需运行SQL脚本来更新查询

  • 更容易移植到另一个数据库 - 没有移动到端口

存储过程的优点：

  • 表现

  • 安全

c# sql sql-server stored-procedures

30 回答

  • 44

    存储过程 .

    如果错误滑动或逻辑稍微改变，则不必重新编译项目 . 此外，它允许从不同的来源访问，而不仅仅是您在项目中编码查询的地方 .

    我认为维护存储过程并不困难，您不应该直接在数据库中编码，而应首先在单独的文件中编码，然后您可以在需要设置的任何数据库上运行它们 .

    回复于
  • 33

    我不是存储过程的粉丝

    存储过程更易于维护，因为：*无论何时想要更改某些SQL，都不必重新编译C＃应用程序

    无论如何，当数据类型发生更改时，或者您想要返回一个额外的列或其他任何内容时，您最终都会重新编译它 . 您可以从应用程序下方“透明地”更改SQL的次数总体来说非常小

    您最终重用SQL代码 .

    编程语言，包括C＃，有这个神奇的东西，称为函数 . 这意味着您可以从多个位置调用相同的代码块！惊人！然后，您可以将可重复使用的SQL代码放在其中一个中，或者如果您想获得真正的高科技，您可以使用一个为您执行此操作的库 . 我相信它们被称为对象关系映射器，现在很常见 .

    当您尝试构建可维护的应用程序时，代码重复是您可以做的最糟糕的事情！

    同意，这就是为什么storedprocs是一件坏事 . 将SQL重构和分解（分成更小的部分）代码转换为函数比SQL更容易？

    你有4个网络服务器和一堆使用相同SQL代码的Windows应用程序现在你意识到SQl代码存在一个小问题所以你宁愿......在一个地方更改proc或者将代码推送到所有的网络服务器，在所有的Windows框上重新安装所有的桌面应用程序（clickonce可能会有所帮助）

    为什么您的Windows应用程序直接连接到中央数据库？这似乎是一个巨大的安全漏洞，并且它排除了服务器端缓存的瓶颈 . 它们不应该通过Web服务或类似于您的Web服务器连接吗？

    那么，推送1个新的sproc或4个新的web服务器？

    在这种情况下，推送一个新的sproc更容易，但根据我的经验，95％的'pushed changes'会影响代码，而不会影响数据库 . 如果您在那个月向网络服务器推送了20件事，并且在数据库中推送了1件，那么如果您将21件事情推送到网络服务器，那么您几乎不会损失太多，而对数据库则为0 .

    更轻松地审查代码 .

    你能解释一下吗？我不懂 . 特别是因为sprocs可能不在源代码控制中，因此无法通过基于Web的SCM浏览器访问等等 .

    更多缺点：

    存储过程存在于数据库中，该数据库在外部世界中显示为黑盒子 . 想要将它们置于源代码控制中的简单事情变成了一场噩梦 .

    还有一个纯粹的努力问题 . 如果你试图向你的首席执行官证明为什么只花费700万美元来 Build 一些论坛，那么将一切都分解为million tiers可能是有意义的，但是否则为每一件小事创建一个存储过程只是额外的驴工作而没有任何好处 .

    回复于
  • 3

    目前正在讨论其他几个主题 . 我是存储过程的一贯支持者，尽管正在介绍Linq到Sql的一些好的参数 .

    在代码中嵌入查询会使您与数据模型紧密相关 . 存储过程是一种很好的 Contract 编程形式，这意味着DBA可以自由地更改数据模型和过程中的代码，只要保留存储过程的输入和输出所代表的 Contract 即可 .

    当查询隐藏在代码中而不是位于一个易于管理的中央位置时，调整 生产环境 数据库可能非常困难 .

    [编辑]这是另一个current discussion

    回复于
  • 4

    在我看来，你不能在这个问题上投票赞成或不赞成 . 这完全取决于您的应用程序的设计 .

    我完全投票反对在3层环境中使用SP，前面有一个应用程序服务器 . 在这种环境中，您的应用程序服务器可以运行您的业务逻辑 . 如果您另外使用SP，则会开始在整个系统中分发业务逻辑的实现，并且将很清楚谁负责什么 . 最终，您将得到一个应用程序服务器，除了以下内容之外基本上什么也不做：

    (Pseudocode)

Function createOrder(Order yourOrder) 
Begin
  Call SP_createOrder(yourOrder)
End

    所以最后你的中间层运行在这个非常酷的4服务器集群上，每个集群都配备了16个CPU，它实际上什么都不做！多么浪费！

    如果你有一个胖的gui客户端直接连接到你的数据库或者可能更多的应用程序，这是一个不同的故事 . 在这种情况下，SP可以作为某种伪中间层，将您的应用程序与数据模型分离，并提供可控制的访问 .

    回复于
  • 22

    代码的优点：易于维护 - 不需要运行SQL脚本来更新查询更容易移植到另一个数据库 - 没有到端口的过程

    实际上，我认为你倒退了 . 恕我直言，SQL代码很难维护，因为：

    • 你最终在相关的代码块中重复自己

    • SQL isn 't supported as a language in many IDE' s因此您只有一系列未经错误检查的字符串为您执行任务
      数据类型，表名或约束中的

    • 更改比将新数据库更换为整个数据库要普遍得多

    • 随着查询复杂性的增加，您的难度也会增加

    • 并测试内联查询需要构建项目

    将Stored Procs视为您从数据库对象调用的方法 - 它们更容易重用，只有一个地方可以编辑，如果您更改数据库提供程序，更改将在您的存储过程中发生，而不是在您的代码中 .

    也就是说，存储过程的性能提升很小，因为Stu在我之前说过，你不能在存储过程中设置一个断点（尚未） .

    回复于
  • 13

    CON

    我发现在扩展你的行为时，在存储过程中进行大量处理会使你的数据库服务器成为一个单一的不灵活点 .

    但是，如果您有多个运行代码的服务器，那么在您的程序中执行所有与sql-server相反的操作可能会允许您扩展更多 . 当然，这不适用于仅执行常规获取或更新的存储过程，而是适用于执行更多处理（如循环数据集）的存储过程 .

    PROS

    • 性能可能值（避免通过DB驱动程序/计划重新创建等进行查询解析）

    • 数据操作未嵌入到C / C / C＃代码中，这意味着我可以查看较少的低级代码 . 单独列出时，SQL不那么冗长，也更容易查看 .

    • 由于分离，人们可以更轻松地查找和重用SQL代码 .

    • 在架构更改时更容易更改内容 - 您只需要为代码提供相同的输出，它就可以正常工作

    • 更容易移植到不同的数据库 .

    • 我可以列出我的存储过程的各个权限，也可以控制该级别的访问权限 .

    • 我可以将数据查询/持久性代码与我的数据转换代码分开 .

    • 我可以在我的存储过程中实现可更改的条件，并且很容易在客户站点进行自定义 .

    • 使用一些自动化工具将我的模式和语句转换为一起变得更容易，而不是当它嵌入我的代码中时我必须将它们追捕 .

    • 当您在单个文件中包含所有数据访问代码时，确保数据访问的最佳实践更容易 - 我可以检查访问非性能表的查询或使用更高级别序列化的查询或在代码中选择*的查询等等

    • 当一个文件中列出所有模式更改/数据操作逻辑更改时，它变得更容易 .

    • 当SQL位于同一位置时，对SQL进行搜索和替换编辑变得更加容易，例如更改/添加所有存储过程的事务隔离语句 .

    • 我和DBA的家伙发现，当DBA必须审查我的SQL内容时，拥有一个单独的SQL文件更容易/方便 .

    • 最后，您不必担心SQL注入攻击，因为您的团队中的某些懒惰成员在使用嵌入式sqls时未使用参数化查询 .

    回复于
  • 6

    存储过程的性能优势通常可以忽略不计 .

    存储过程的更多优点：

    • 防止逆向工程（如果使用加密创建，当然）

    • 更好地集中数据库访问

    • 能够透明地更改数据模型（无需部署新客户端）;如果多个程序访问相同的数据模型，则特别方便

    回复于
  • 99

    我落在 code 方面 . 我们从这个角度构建了数据访问层's used by all all the apps (both web and client), so it' s DRY . 它简化了数据库部署，因为我们只需要确保表模式's are correct. It simplifies code maintenance because we don't必须查看源代码和数据库 .

    我与数据模型的紧耦合没有太大问题，因为我没有看到真正打破这种耦合的可能性 . 应用程序及其数据本质上是耦合的 .

    回复于
  • 4

    Advantages for Stored procedures ：

    更容易编写代码 .

    耦合较少，因此更容易测试 .

    更容易调整 .

    从网络流量的角度来看，性能通常更好 - 如果你有一个游标或类似的游戏，那么就没有多次访问数据库

    您可以更轻松地保护对数据的访问，删除对表的直接访问，通过过程强制执行安全性 - 这也允许您相对快速地找到更新表的任何代码 .

    如果涉及其他服务（例如Reporting Services），您可能会发现将所有逻辑存储在存储过程中而不是代码中更容易，并且必须复制它

    Disadvantages:

    更难以为开发人员管理：脚本的版本控制：每个人都有自己的数据库，是与数据库和IDE集成的版本控制系统吗？

    回复于
  • 4

    在某些情况下，代码中动态创建的sql可以比存储过程具有更好的性能 . 如果你已经创建了一个存储过程（让我们说sp_customersearch）变得非常复杂，因为它必须非常灵活，你可以在运行时在代码中生成一个更简单的sql语句 .

    有人可能会争辩说，这只是将一些处理从SQL移动到Web服务器，但总的来说这将是一件好事 .

    关于这种技术的另一个好处是，如果你正在查看SQL分析器，你可以看到你生成的查询并调试它比看到存储的带有20个参数的proc调用更容易 .

    回复于
  • 7

    我喜欢存储过程，不知道有多少次我能够使用存储过程对应用程序进行更改，而该存储过程不会对应用程序产生任何停机时间 .

    Transact SQL的大粉丝，调优大型查询已被证明对我非常有用 . 大约6年没有写过任何内联SQL！

    回复于
  • 8

    你列出了2个针对sprocs的专业点：

    表现 - 不是真的 . 在Sql 2000或更高版本中，查询计划优化非常好，并且已缓存 . 我确信Oracle等会做类似的事情 . 我认为不再有针对性能的sprocs了 .

    安全？为什么sprocs会更安全？除非你有一个非常不安全的数据库，否则所有访问都将来自你的DBA或你的应用程序 . 总是参数化所有查询 - 永远不要在用户输入内联内容，你会没事的 .

    无论如何，这是性能的最佳实践 .

    Linq绝对是我现在开展新项目的方式 . 见similar post .

    回复于
  • 16

    @Keith

    安全？为什么sprocs会更安全？

    根据Komradekatz的建议，您可以禁止访问表（对于连接到数据库的用户名/密码组合）并仅允许SP访问 . 这样，如果有人获得数据库的用户名和密码，他们就可以执行SP，但无法访问表或数据库的任何其他部分 .

    （当然，执行sprocs可能会为他们提供他们需要的所有数据，但这取决于可用的sprocs . 让他们访问表格可以让他们访问所有内容 . ）

    回复于
  • 13

    这样想吧

    你有4个网络服务器和一堆使用相同SQL代码的windows应用程序现在你意识到SQl代码有一个小问题所以你宁愿......在一个地方更改proc或者将代码推送到所有Web服务器，在所有Windows框上重新安装所有桌面应用程序（clickonce可能会有所帮助）

    我更喜欢存储过程

    对proc进行性能测试也更容易，将它放在查询分析器中设置统计io / time on set showplan_text on和voila

    无需运行探查器即可查看正在调用的内容

    只是我的2美分

    回复于
  • 2

    我更喜欢在代码中保留它们（使用ORM，而不是内联或临时），因此它们被源代码控制所覆盖，而不必处理保存的.sql文件 .

    此外，存储过程本身并不安全 . 您可以使用sproc编写错误查询，就像内联一样容易 . 参数化内联查询可以像sproc一样安全 .

    回复于
  • 8

    使用您的应用程序代码作为它最擅长的：处理逻辑 .
    使用您的数据库获取最佳功能：存储数据 .

    您可以调试存储过程，但您会发现在代码中更容易调试和维护逻辑 . 通常，每次更改数据库模型时，您都将重新编译代码 .

    此外，具有可选搜索参数的存储过程非常有用，因为您必须事先指定所有可能的参数，并且有时无法进行复杂搜索，因为您无法预测参数在搜索中重复的次数 .

    回复于
  • 2

    在安全性方面，存储过程更加安全 . 有些人认为所有访问都是通过应用程序进行的 . 很多人都忘记的事情是，大多数安全漏洞都来自公司内部 . 想想有多少开发人员知道您的应用程序的“隐藏”用户名和密码？

    此外，正如MatthieuF指出的那样，由于往返次数减少，性能可以大大提高应用程序（无论是在桌面服务器还是Web服务器上）和数据库服务器 .

    根据我的经验，通过存储过程抽象数据模型也极大地提高了可维护性 . 作为过去必须维护许多数据库的人，当面对所需的模型更改时，能够简单地更改一两个存储过程并使更改对所有外部应用程序完全透明，这是一种解脱 . 很多时候，您的应用程序并不是唯一指向数据库的应用程序 - 还有其他应用程序，报告解决方案等，因此跟踪所有这些受影响的点可能是对表的开放访问的麻烦 .

    我还将检查放在加号列中，以便将SQL编程放在那些专门研究它的人手中，而对于SP则使得更容易隔离和测试/优化代码 .

    我看到的一个缺点是许多语言不允许传递表参数，因此传递未知数量的数据值可能很烦人，而且某些语言仍无法处理从单个存储过程中检索多个结果集（尽管在这方面，后者不会使SP比内联SQL差 . ）

    回复于
  • 179

    我参加的Microsoft TechEd安全 Session 的建议之一，通过存储过程进行所有调用，并拒绝直接访问表 . 这种方法被称为提供额外的安全性 . 我不确定它是否值得为安全起见，但如果你已经在使用存储过程，它就不会受到伤害 .

    回复于
  • 6

    如果将其放入存储过程中，则更容易维护 . 如果有's difficult logic involved that will potentially change in the future it is definitely a good idea to put it in the database when you have multiple clients connecting. For example I' m正在处理一个具有最终用户Web界面和管理桌面应用程序的应用程序，这两个应用程序共享一个数据库（显然）并且我试图在数据库上保留尽可能多的逻辑 . 这是DRY principle的一个完美例子 .

    回复于
  • 2

    假设你没有作弊并在存储过程中使用动态SQL，我坚定地站在存储过程的一边 . 首先，使用存储过程允许dba在存储过程级别而不是表级别设置权限 . 这不仅对于解决SQL注入问题，而且对防止内部人员直接访问数据库和更改内容至关重要 . 这是一种有助于防止欺诈的方法 . 除非通过严格的程序，否则不应访问包含个人信息（SSN，信用卡号等）或无论如何创建金融交易的数据库 . 如果您使用任何其他方法，您将为公司中的个人敞开数据库，以创建虚假的金融交易或窃取可用于身份盗用的数据 .

    与从应用程序发送的SQL相比，存储过程更容易维护和性能调整 . 它们还允许dba查看数据库结构更改对数据访问方式的影响 . 我从来没有见过一个允许动态访问数据库的好dba .

    回复于
  • 47

    我们使用Oracle DB的存储过程，我现在在那里工作 . 我们也使用Subversion . 所有存储过程都创建为.pkb和.pks文件并保存在Subversion中 . 我以前做过在线SQL，真是太痛苦了！我更喜欢我们在这里做的方式 . 创建和测试新的存储过程比在代码中执行它更容易 .

    有个

    回复于
  • 2

    Smaller logs

    另一个未提及存储过程的辅助专业人员：在SQL流量方面，基于sp的数据访问产生的流量要少得多 . 当您监视流量以进行分析和分析时，这变得非常重要 - 日志将更小且可读 .

    回复于
  • 3

    我不是存储过程的忠实粉丝，但我在一个条件下使用它们：

    当查询非常庞大时，最好将其作为存储过程存储在数据库中，而不是从代码中发送 . 这样，不是从应用程序服务器向数据库发送大量字符串字符，而是仅发送 "EXEC SPNAME" 命令 .

    当数据库服务器和Web服务器不在同一网络上时（例如，Internet通信），这是过度的 . 即使情况并非如此，过多的压力也意味着大量浪费的带宽 .

    但是男人，他们的管理太糟糕了 . 我尽可能地避免它们 .

    回复于
  • 4

    SQL存储过程不会提高查询的性能

    回复于
  • 9

    显然，使用存储过程比在代码中构造SQL有几个优点 .

    • 您的代码实现和SQL彼此独立 .

    • 代码更易于阅读 .

    • 写一次使用多次 .

    • 修改一次

    • 无需向程序员提供有关数据库的内部详细信息 . 等等

    回复于
  • 11

    存储过程更易于维护，因为：

    • 无论何时想要更改某些SQL，都不必重新编译C＃应用程序

    • 您最终重用SQL代码 .

    代码重复是你在尝试构建时最糟糕的事情可维护的应用！

    当您发现需要在多个位置更正的逻辑错误时会发生什么？您更容易忘记更改复制和粘贴代码的最后一个位置 .

    在我看来，性能和安全性增益是一个额外的优势 . You can still write insecure/inefficient SQL stored procedures.

    更容易移植到另一个数据库 - 没有移动到端口

    它比导出表更容易，因为没有主要/外键需要担心 .

    回复于
  • 2

    @Terrapin - sprocs同样容易受到注射攻击 . 就像我说的：

    始终参数化所有查询 - 永远不要在用户输入内联内容，你会没事的 .

    这适用于sprocs和动态Sql .

    我不确定不重新编译你的应用程序是一个优势 . 我的意思是，你已经对该代码（应用程序和数据库）进行了单元测试，无论如何再次上线 .

    @Guy - 是的，你是对的，sprocs让你控制应用程序用户，这样他们只能执行sproc而不是底层操作 .

    我的问题是：如果所有通过您的应用程序访问它，使用连接和具有有限更新/插入权限的用户等，这个额外级别是否会增加安全性或额外管理？

    我的观点非常重要 . 如果他们将您的应用程序妥协到可重写的程度，那么他们可以使用其他大量攻击 .

    如果动态内联代码，仍然可以对这些sprocs执行Sql注入，因此黄金法则仍然适用，所有用户输入必须始终进行参数化 .

    回复于
  • 3

    到目前为止我还没有看到的东西：最了解数据库的人并不总是编写应用程序代码的人 . 存储过程为数据库人员提供了一种与程序员交互的方式，这些程序员并不真正想要了解SQL . 大型 - 尤其是遗留式 - 数据库并不是最容易理解的东西，因此程序员可能只是喜欢一个简单的界面来为他们提供他们需要的东西：让DBA弄清楚如何加入17个表来实现这一点 .

    话虽这么说，用于编写存储过程的语言（PL / SQL是一个臭名昭着的例子）非常残酷 . 它们通常不提供您在当今流行的命令式，OOP或函数式语言中看到的任何细节 . 想想COBOL .

    因此，坚持只是抽象出关系细节而不是那些包含业务逻辑的存储过程 .

    回复于
  • 3

    我通常写OO代码 . 我怀疑大多数人也可能这样做 . 在这种情况下，对我来说很明显，所有的业务逻辑 - 包括SQL查询 - 都属于类定义 . 拆分逻辑，使其中的一部分驻留在对象模型中，而部分位于数据库中，并不比将业务逻辑放入用户界面更好 .

    在早期的答案中已经说过很多关于存储过程的安全性好处 . 这些分为两大类：

    1）限制对数据的直接访问 . 这在某些情况下肯定很重要，当你遇到一个时，那么存储过程几乎是你唯一的选择 . 根据我的经验，这些案例是例外而不是规则 .

    2）SQL注入/参数化查询 . 这个异议是一个红鲱鱼 . 内联SQL - 甚至是动态生成的内联SQL - 可以像任何存储过程一样完全参数化，并且它可以在任何现代语言中轻松完成 . 这两种方式都没有优势 . （“懒惰的开发人员可能不会使用参数”并不是一个有效的反对意见 . 如果您的团队中的开发人员更愿意将用户数据连接到他们的SQL而不是使用参数，那么您首先尝试教育他们，然后解雇他们如果这不起作用，就像你对那些有任何其他坏的，明显有害的习惯的开发人员一样 . ）

    回复于
  • 6

    我是SPROC代码的巨大支持者 . 最重要的原因是保持代码紧密耦合，然后紧接着是源代码控制的简易性，而没有很多自定义实用程序将其拉入 .

    在我们的DAL中，如果我们有非常复杂的SQL语句，我们通常将它们包含为资源文件并根据需要更新它们（这可能是一个单独的程序集，并且每个数据库交换出来等等） .

    这使我们的代码和我们的sql调用存储在相同的版本控制中，而不会“忘记”运行一些外部应用程序进行更新 .

    回复于

相关问题