在存储过程中保留SQL与代码[关闭]相比有哪些优点和缺点

我不喜欢存储过程

存储过程更易于维护,因为: *每当你想更改一些SQL时,你不必重新编译你的C#应用程序

当数据类型发生变化,或者您想要返回一个额外的列,或者其他什么情况时,您最终都会重新编译它。总的来说,你可以从应用程序下面“透明”地更改SQL的次数非常少

• 您最终会重用SQL代码。

编程语言,包括C#,有一个神奇的东西,叫做函数。这意味着您可以从多个位置调用相同的代码块!太神了然后你可以把可重用的SQL代码放在其中一个里面,或者如果你想获得真正的高科技,你可以使用一个为你做这件事的库。我相信它们被称为对象关系映射器,现在非常常见。

当您试图构建一个可维护的应用程序时,代码重复是最糟糕的事情!

同意,这就是为什么存储光盘是一件坏事。将代码重构和分解(分解成更小的部分)为函数要比将SQL分解为函数容易得多。。。SQL块?

你有4个Web服务器和一堆使用相同SQL代码的windows应用程序现在你意识到SQL代码有一个小问题,所以你宁愿。。。。。。在一个位置更改proc或将代码推送到所有的Web服务器,在所有的windows框上重新安装所有的桌面应用程序(点击一次可能会有所帮助)

为什么您的windows应用程序直接连接到中央数据库?这似乎是一个巨大的安全漏洞,也是排除服务器端缓存的瓶颈。他们不应该通过网络服务或类似于你的网络服务器进行连接吗?

那么,推送1个新存储过程,还是4个新的Web服务器?

在这种情况下 是 更容易推送一个新的存储过程,但根据我的经验,95%的“推送更改”会影响代码,而不会影响数据库。如果你当月向网络服务器推送20件东西,向数据库推送1件,那么你几乎不会损失太多,如果你向网络服务器推出21件,向数据库推出0件。

更容易进行代码审查。

你能解释一下怎么做吗?我不明白。特别是sproc可能不受源代码管理,因此无法通过基于web的SCM浏览器等访问。

更多缺点:

Storedprocs生活在数据库中,在外界看来,数据库就像一个黑盒子。简单的事情,比如想把它们放在源代码管理中,就变成了一场噩梦。

还有一个问题是纯粹的努力。将所有内容分解为 million tiers 如果你想向你的首席执行官证明为什么他们只花了700万美元来建立一些论坛,但在其他方面,为每一件小事创建一个storedproc只是额外的工作,没有任何好处。

这一点目前正在其他一些线程中讨论。我一直支持存储过程,尽管有人提出了一些从Linq到Sql的好论据。

在代码中嵌入查询可以将您与数据模型紧密地结合在一起。存储过程是一种很好的契约编程形式,这意味着DBA可以自由更改过程中的数据模型和代码,只要存储过程的输入和输出所代表的契约得到维护。

当查询隐藏在代码中而不是一个易于管理的中心位置时,调整生产数据库可能会非常困难。

[编辑]这是另一个 current discussion

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

我完全反对在前面有应用程序服务器的三层环境中使用SP。在这种环境中,您的应用程序服务器将运行您的业务逻辑。如果您另外使用SP,您将开始在整个系统中分发业务逻辑的实现,并且将变得非常不清楚谁负责什么。最终,您将得到一个应用程序服务器,它基本上只做以下事情:

(Pseudocode)

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

因此,最终,您的中间层在这个非常酷的4服务器集群上运行,每个集群都配备了16个CPU,实际上它什么都不会做!真是浪费!

如果你有一个直接连接到数据库或更多应用程序的胖gui客户端,那就另当别论了。在这种情况下,SP可以充当某种伪中间层,将应用程序与数据模型解耦,并提供可控的访问。

代码中的优点:

• 更易于维护-不需要运行SQL脚本来更新查询
• 更容易连接到另一个数据库-无需连接到端口

事实上,我认为你已经落后了。IMHO,代码中的SQL很难维护,因为:

• 你最终会在相关的代码块中重复自己
• SQL在许多IDE中都不支持作为一种语言,因此只有一系列未经错误检查的字符串为您执行任务
• 数据类型、表名或约束的更改远比将整个数据库换成新数据库更为普遍
• 随着查询复杂性的增加,难度也会增加
• 测试内联查询需要构建项目

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

也就是说,正如Stu在我之前所说的,存储过程的性能增益是最小的,而且你还不能在存储过程中设置断点。

CON

我发现,在扩展您的行为时,在存储过程中进行大量处理会使您的DB服务器成为一个单一的灵活性点。

然而,与sql server相比,在程序中进行所有这些运算, 可以 如果您有多个运行代码的服务器,则允许您进行更多扩展。当然,这并不适用于只进行正常获取或更新的存储过程,而是适用于执行更多处理(如在数据集上循环)的存储过程。

专业人士

1. 性能可能值得(避免数据库驱动程序/计划重新创建等进行查询解析)
2. C/C++/C#代码中没有嵌入数据操作,这意味着我要查看的低级别代码更少。SQL不那么冗长,单独列出时更容易查看。
3. 由于分离,人们能够更容易地找到和重用SQL代码。
4. 当模式发生变化时,更改内容更容易——你只需要给代码提供相同的输出,它就会很好地工作
5. 更容易移植到不同的数据库。
6. 我可以列出存储过程的各个权限,并控制该级别的访问。
7. 我可以将我的数据查询/持久性代码与数据转换代码分开进行配置。
8. 我可以在存储过程中实现可变条件,而且在客户站点进行自定义也很容易。
9. 使用一些自动化工具将我的模式和语句转换在一起会变得更容易,而不是当它嵌入到我的代码中时,我必须查找它们。
10. 当您将所有数据访问代码都放在一个文件中时,确保数据访问的最佳实践会更容易——我可以检查访问非性能表的查询,或者使用更高级别序列化的查询,或在代码中选择*等。
11. 当所有模式更改/数据操作逻辑更改都列在一个文件中时,查找它们会变得更容易。
12. 当SQL位于同一位置时,对其进行搜索和替换编辑会变得更容易,例如更改/添加所有存储过程的事务隔离语句。
13. 我和DBA发现,当DBA必须查看我的SQL内容时,拥有一个单独的SQL文件会更容易/更方便。
14. 最后,您不必担心SQL注入攻击,因为您团队中的一些懒惰成员在使用嵌入式SQL时没有使用参数化查询。

存储过程的性能优势往往可以忽略不计。

存储过程的更多优点:

• 防止逆向工程(当然,如果使用加密创建)
• 更好地集中数据库访问
• 能够透明地更改数据模型(无需部署新客户端);如果多个程序访问相同的数据模型,则特别方便

我摔倒在 密码 一边我们构建了所有应用程序(包括web和客户端)都使用的数据访问层,因此从这个角度来看,它是DRY。它简化了数据库部署,因为我们只需要确保表模式是正确的。它简化了代码维护,因为我们不必查看源代码和数据库。

我对数据模型的紧密耦合没有太大问题,因为我不知道在哪里可以真正打破这种耦合。应用程序及其数据本质上是耦合的。

存储过程。

如果出现错误或逻辑发生了一些变化,则不必重新编译项目。此外,它还允许来自不同来源的访问,而不仅仅是您在项目中对查询进行编码的一个地方。

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

存储过程的优势 :

更容易进行代码审查。

耦合较少,因此更容易测试。

更容易调整。

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

您可以更容易地保护对数据的访问,删除对表的直接访问,通过proc加强安全性,这也允许您相对快速地找到任何更新表的代码。

如果涉及其他服务(如Reporting服务),您可能会发现将所有逻辑存储在存储过程中比存储在代码中更容易,并且必须复制它

缺点:

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

在某些情况下,代码中动态创建的sql可能比存储的proc具有更好的性能。如果您创建了一个存储过程(比方说sp_customersearch),由于它必须非常灵活,所以它具有数十个参数,因此变得非常复杂,那么您可能可以在运行时在代码中生成一个简单得多的sql语句。

有人可能会说,这只是将一些处理从SQL转移到web服务器,但总的来说,这是一件好事。

该技术的另一个优点是,如果您在SQL探查器中查看,您可以看到生成的查询并对其进行调试,这比看到带有20个参数的存储过程调用要容易得多。

我喜欢存储过程,不知道有多少次我能够使用一个没有给应用程序造成任何停机的存储过程来更改应用程序。

Transact-SQL的忠实粉丝,事实证明调整大型查询对我非常有用。我已经6年没有写过任何内联SQL了!

您列出了存储过程的2个专业点:

表演——不是真的。在Sql2000或更高版本中,查询计划的优化非常好,而且是缓存的。我相信甲骨文等公司也会做类似的事情。我认为sprocs的性能已经不存在了。

安全为什么sproc更安全?除非你有一个非常不安全的数据库,否则所有的访问都将来自你的DBA或通过你的应用程序。始终参数化所有查询-永远不要从用户输入中内联某些内容,这样您就可以了。

不管怎样,这是最好的表现练习。

林克绝对是我现在新项目的方式。看看这个 similar post 。

@Keith

安全为什么sproc更安全?

根据Koradekatz的建议,您可以禁止访问表(对于连接到DB的用户名/密码组合),只允许SP访问。这样,如果有人获得了您数据库的用户名和密码,他们可以执行SP,但不能访问表或数据库的任何其他部分。

(当然,执行存储过程可能会为他们提供所需的所有数据,但这取决于可用的存储过程。允许他们访问表可以让他们访问所有内容。)

这样想吧

您有4个Web服务器和一堆使用相同SQL代码的windows应用程序 现在您意识到SQl代码有一个小问题 所以你宁愿。。。。。。 在1个位置更改过程 或 将代码推送到所有的Web服务器,在所有的windows框中重新安装所有的桌面应用程序(点击一次可能会有所帮助)

我更喜欢储存的proc

对proc进行性能测试也更容易,将其放在查询分析器中 在上设置统计信息io/time 打开showplan_text,瞧

无需运行探查器来查看调用的确切内容

就我的2美分

我更喜欢将它们保存在代码中(使用ORM,而不是内联或特设),这样它们就可以由源代码管理覆盖,而不必保存.sql文件。

此外,存储过程本身并不更安全。使用存储过程可以像内联一样轻松地编写错误查询。参数化内联查询可以和存储过程一样安全。

使用你的应用程序代码做它最擅长的事情:处理逻辑。
使用你的数据库做它最擅长的事情:存储数据。

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

此外,具有可选搜索参数的存储过程非常低效,因为您必须提前指定所有可能的参数,而复杂的搜索有时是不可能的,因为您无法预测参数将在seach中重复多少次。

在安全性方面,存储过程要安全得多。一些人认为,无论如何,所有访问都将通过应用程序进行。许多人忘记的是,大多数安全漏洞都来自公司内部。想想有多少开发人员知道你的应用程序的“隐藏”用户名和密码?

此外,正如MatthieuF所指出的,由于应用程序(无论是在桌面服务器还是web服务器上)和数据库服务器之间的往返次数减少,性能可以大大提高。

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

我还将在加号栏中进行检查,以将SQL编程交给专门从事SQL编程的人员,并检查SP,使其更容易隔离和测试/优化代码。

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

我参加了一个关于安全的微软TechEd会议,其中一个建议是,通过存储的进程进行所有调用,并拒绝直接访问表。这种方法被宣传为提供额外的安全性。我不确定仅仅为了安全起见是否值得,但如果你已经在使用存储的proc,那也没什么坏处。

如果你把它放在存储过程中,肯定更容易维护。如果所涉及的逻辑很困难,将来可能会发生变化,那么当您有多个客户端连接时,将其放入数据库无疑是一个好主意。例如,我现在正在开发一个应用程序,它有一个最终用户web界面和一个管理桌面应用程序,这两个应用程序共享一个数据库(显然),我正在努力在数据库上保留尽可能多的逻辑。这是一个完美的例子 DRY principle 。

我坚定地支持存储过程,假设你不作弊,并在存储过程中使用动态SQL。首先,使用存储过程允许dba在存储过程级别而不是表级别设置权限。这不仅对对抗SQL注入攻击至关重要,而且对防止内部人员直接访问数据库和更改内容也至关重要。这是一种有助于防止欺诈的方法。任何包含个人信息(SSN、信用卡号等)或以任何方式创建金融交易的数据库都不应被访问,除非通过存储程序。如果你使用任何其他方法,你的数据库将完全开放,让公司中的个人创建虚假的金融交易或窃取可用于身份盗窃的数据。

存储的proc也比从应用程序发送的SQL更容易维护和性能调整。它们还允许dba了解数据库结构变化对数据访问方式的影响。我从来没有遇到过一个优秀的dba允许动态访问数据库。

我们在我现在工作的Oracle数据库中使用存储过程。我们也使用Subversion。所有存储过程都创建为.pkb&。pks文件并保存在Subversion中。我以前做过内联SQL,这很痛苦!我更喜欢我们在这里做这件事的方式。创建和测试新的存储过程比在代码中进行要容易得多。

特蕾莎

较小的原木

另一个未提及的存储过程的次要专业:当涉及到SQL流量时,基于sp的数据访问会生成 很 流量减少。当您监视流量进行分析和分析时,这一点就变得很重要了——日志会更小,可读性更强。

我不太喜欢存储过程,但我在一个条件下使用它们:

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

当数据库服务器和web服务器不在同一网络上时(例如,互联网通信),这就太过分了。即使事实并非如此,过多的压力也意味着浪费了大量的带宽。

但是,伙计,他们太难管理了。我尽量避开他们。

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

很明显,与在代码中构造SQL相比,使用存储过程有几个优点。

1. 您的代码实现和SQL变得相互独立。
2. 代码更容易阅读。
3. 写一次用多次。
4. 修改一次
5. 无需向程序员提供有关数据库的内部详细信息。等等。

存储过程是 更多 可维护,因为:

• 每当你想更改一些SQL时,你不必重新编译你的C#应用程序
• 您最终会重用SQL代码。

代码重复是 最差的 当您试图构建一个可维护的应用程序时,您可以做的事情!

当你发现一个逻辑错误需要在多个地方纠正时,会发生什么?你更容易忘记改变你复制&粘贴了您的代码。

在我看来;安全收益是一个额外的好处。 您仍然可以编写不安全/效率低下的SQL存储过程。

更容易连接到另一个数据库-无需连接到端口

编写所有存储过程的脚本以便在另一个数据库中创建并不困难。事实上 更容易 而不是导出表,因为不需要担心主键/外键。

@Terrapin-sproc同样容易受到注入攻击。正如我所说:

始终参数化所有查询-永远不要从用户输入中内联某些内容,这样您就可以了。

这适用于存储过程和动态Sql。

我不确定不重新编译你的应用程序是否有优势。我的意思是,在重新投入使用之前,您已经针对该代码(包括应用程序和DB)运行了单元测试。

@盖伊-是的,您是对的,存储过程确实允许您控制应用程序用户,使他们只能执行存储过程,而不能执行底层操作。

我的问题是:如果所有人都通过你的应用程序访问它,使用连接和更新/插入权限有限的用户等,这个额外的级别会增加安全性还是额外的管理?

我的意见完全是后者。如果他们已经将你的应用程序泄露到可以重写的地步,他们还有很多其他攻击可以使用。

如果这些存储过程动态内联代码,则仍然可以对它们执行Sql注入,因此黄金法则仍然适用,所有用户输入都必须始终参数化。

到目前为止,我还没有看到有人提到:最了解数据库的人并不总是编写应用程序代码的人。存储过程为数据库人员提供了一种与那些不想学习太多SQL知识的程序员交互的方式。大型数据库——尤其是遗留数据库——并不是最容易完全理解的东西,所以程序员可能更喜欢一个简单的接口,为他们提供所需的东西:让DBA找出如何连接17个表来实现这一点。

也就是说,用于编写存储过程的语言(PL/SQL是一个臭名昭著的例子)非常残酷。它们通常不会提供你在当今流行的命令式、OOP或函数式语言中看到的任何细节。想想COBOL。

因此,坚持使用那些只抽象掉关系细节的存储过程,而不是那些包含业务逻辑的存储过程。

我通常编写OO代码。我怀疑你们大多数人可能也会这样做。在这种情况下,我似乎很清楚,所有的业务逻辑——包括SQL查询——都属于类定义。将逻辑拆分为部分驻留在对象模型中,部分驻留在数据库中,并不比将业务逻辑放入用户界面更好。

关于存储proc的安全优势,在前面的回答中已经说了很多。它们分为两大类:

1) 限制对数据的直接访问。这在某些情况下肯定很重要,当你遇到这种情况时,存储的proc几乎是你唯一的选择。然而,根据我的经验,这种情况是例外,而不是常规。

2) SQL注入/参数化查询。这种反对意见是转移注意力。内联SQL——甚至是动态生成的内联SQL——可以像任何存储过程一样完全参数化,而且在任何值得一试的现代语言中都可以轻松完成。这两种方式都没有优势。(“懒惰的开发人员可能不愿意使用参数”并不是一个有效的反对意见。如果你的团队中有一些开发人员更喜欢将用户数据连接到他们的SQL中,而不是使用参数,你首先会尝试教育他们,如果不起作用,你就会解雇他们,就像你对待有任何其他不良、明显有害习惯的开发人员一样。)

我非常支持SPROC的代码。第一个原因是保持代码的紧密耦合,第二个原因是易于进行源代码管理,而无需大量自定义实用程序。

在我们的DAL中,如果我们有非常复杂的SQL语句,我们通常会将它们作为资源文件包括在内,并根据需要进行更新(这也可以是一个单独的程序集,并按数据库交换,等等)。

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