存储过程和ORM（对象关系映射）

Question

存储过程和ORM（对象关系映射）

stored-procedures

39

与使用ORM（如nHibernate、EF等）处理一些CRUD操作相比，存储过程的目的是什么？调用存储过程时，我们只需传递几个参数，而使用ORM则发送整个SQL查询，但这仅仅是性能和安全问题，还有更多优势吗？

我之所以问这个问题，是因为我从未使用过存储过程（我只使用ORM编写所有SQL语句并执行），客户告诉我在我的下一个项目中将需要使用存储过程，我试图弄清楚何时使用它们。

- Gui

7个回答

11

主要（我很倾向于说“唯一”）使用存储过程的原因是如果您确实需要性能，那么您应该使用它。

在数据库中创建能够快速执行复杂任务的“函数”可能会似乎很诱人。但是它很快就会失去控制。

我曾经与那些在SQL中将许多业务逻辑封装起来的应用程序合作，这使得重构任何内容都几乎不可能。数百个对ORM开发人员来说是黑匣子的存储过程。

这样的应用程序变得脆弱，难以调试和理解。通过允许业务逻辑存在于存储过程中，您允许SQL开发人员进行他们不应该进行的设计选择，在一个比ORM更难以工作、记录和调试的工具中。我曾见过用于处理付款处理的存储过程。真正核心的东西。这些内容变得如此核心，以至于没有人敢触及它，所有这一切都是因为某个拥有良好SQL技能的人在5年前编写了一个快速修复的脚本，它从未迁移到ORM，并最终成长为无法管理的怪物，充满了谁也不理解的纠缠逻辑。开发人员最终不得不盲目信任它的功能。更糟糕的是，它几乎总是超出了测试范围，因此当您部署时，即使使用模拟数据测试通过，但某个古老的存储过程突然开始出问题，您可能会导致所有内容都崩溃。

滥用存储过程是您可能积累的最严重的技术债务之一。持久层的数据库不应用于业务逻辑。您应该尽可能严格地保持这种区分。

当然，也会有一些情况下ORM的性能非常糟糕或根本不支持您需要的SQL特性。如果必须使用原始SQL来处理事情，那么只有在这种情况下才应考虑使用存储过程。

我曾经见过存储过程地狱。你不想要那样的结果。

- Christoph Meier

7

在某些情况下，存储过程具有显著的性能优势。通常情况下，由Linq和其他ORM生成的查询可能效率低下，但对于您的目的来说仍然足够好。某些RBDMS（例如SQL Server）将缓存存储过程的执行计划，节省了查询时间。对于您经常使用的更复杂的查询，这种性能节省可能至关重要。

然而，在大多数正常的CRUD操作中，如果可用并且其操作符合您的需求，则我发现只使用ORM通常更易于维护。在.NET世界中，Entity Framework很好地适合我的大部分时间（结合Linq使用），而我非常喜欢PHP的Propel。

- Andrew

大多数关系型数据库管理系统都会为符合特定模式的每个查询缓存执行计划，这种做法已经存在十多年了。已经有很多次证明，执行计划不仅被缓存于存储过程中，而且也被缓存于动态SQL中，这一点在SQL Server帮助文档中也有明确说明。 - Jordan

这不仅仅与缓存或执行计划有关，还与在DBMS的本机SQL变体中可以使用的技巧和技术优化查询和存储过程的事实有关，可大幅度提高性能。这种类型的SQL无法使用ORM方法轻松编写。我一遍又一遍地看到了它。总之，使用DBMS的原生SQL语言编写的存储过程可以比任何ORM生成的SQL快个数量级。然而，ORM方法更易于维护。大多数ORM开发人员不是高级SQL专家，并且没有理解和经验。 - Manachi

值得注意的是，根据文档 https://msdn.microsoft.com/en-us/library/cc293623.aspx ，正确参数化的 SQL 查询（文档中称为“准备查询”）确实会被缓存和重复使用。 - user74754

@Manachi，你的评论假设编写存储过程的人是高级SQL专家。但事实往往并非如此。考虑两种情况：一个被数十甚至数百人开发以支持成千上万个用例的流行ORM，与由一两个人编写的存储过程相比，哪一个应用了最多的专业知识？ - Jordan

6

我偶然发现了这个比较古老的问题，但我很震惊最重要的存储过程好处甚至没有被提及。

安全性和资源保护

使用存储过程，你可以将该存储过程的执行权限授予用户。该用户可以执行该存储过程并且仅限于执行该存储过程。你甚至不需要让用户具有表的读或写访问权限，用户也无需知道所使用的表。

使用ORM，你必须给予用户对所使用的表格的阅读或/和写入访问权限。用户可以阅读你授权的所有表格中的所有数据，并且可以在查询中组合它们，如果你希望或不希望的话，还可以运行会对数据库服务器产生重负荷的查询。

这在应用程序开发和数据库开发由不同团队完成，且数据库由多个应用程序使用的情况下特别有用。

- Erich Bauer

1

正如le dorfier所提到的，使用存储过程（和/或视图）的主要原因之一是为数据库和其客户端（Web应用程序、报表、ETL等）提供抽象层。

这个“DB API”可以使得更改/重构数据库变得更加容易，而不必影响客户端。

请参见为什么要使用存储过程以获取更深入的讨论。

- mbaylon

2

你用引号括起来"'DB API'"的确切原因就是这样做的原因很糟糕。你正在将领域逻辑与持久层耦合在一起。所以你不能使用比喻性的“DB API”来解决这个问题。你还假设替代方案是直接将ORM或硬编码SQL直接放入Web应用程序或类似于表示层的东西中。正确的替代方案（无论是使用ORM还是手动ADO.Net等）是将业务逻辑或数据逻辑适当地抽象成自己的层或层次结构。 - Suamere

1

主要用途是实现抽象层并封装查询逻辑。就像在过程化语言中编写函数一样。

- dkretz

0

我主要使用linq to sql作为ORM，我认为它非常好，但存储过程仍然有其存在的意义。主要是当我想要运行的查询非常复杂时，例如有许多连接（特别是外连接，在Linq中很糟糕），子查询中有大量聚合，递归CTE等类似情况。

但对于一般的crud操作，没有必要使用存储过程。

- Paul Creasey

好的，我没有想起使用连接。在LINQ中，使用连接确实很困难。顺便问一下，你知道所有主要的ORM（EF，nHibernate）是否都可以调用存储过程并将其返回到与之关联的实体吗？ - Gui

@guilhermeGeek EF4 具有此功能。 EF 允许您避免大多数显式连接，前提是您已正确设置了主键和外键，但如果在 Linq 中需要连接，则大多数情况下只是语法上的尴尬。您可能会发现您不需要将连接指定为 Join，而实际上应该作为 Linq 中的 Select 或有时是 SelectMany。 - Andrew

@Andrew，在EF4和L2S中，假设模式已经正确定义并具有正确的键，则手动编写连接操作是相当罕见的。但是，当您无法更改或控制DDL时，或者在大量需要外部连接和其他更复杂的查询时，有时在linq中编写它并不值得花费阅读性或性能方面的努力，而有些事情根本不可能实现。 - Paul Creasey

@guilhermeGeek，Linq to SQL将存储过程映射到实体，但在存储过程中使用动态SQL时可能会变得有些棘手。 - Paul Creasey

同意。如果没有稳定的DDL，使用Linq可能会非常麻烦。据说，你可以通过自己定义带有不太明显的数据库映射的类来在EF中缓解这种情况，但我自己还没有尝试过。我很幸运：如果数据模型出了问题，我可以修复它，而不是试图在代码中绕过它。 - Andrew

你在这里看到像“我不记得连接”的评论，说明了应用程序水平之间的巨大差异。我最近工作过的3-4个系统有100多个表，大多数查询都跨越5-6个表进行高级连接，包括内部连接、左连接、子查询和其他高级SQL。如果一个系统只需要非常基本的CRUD操作，那么它显然非常简单/基础，整个问题并不重要。但是当你涉及到严肃的数据时，存储过程具有显著的性能优势。 - Manachi

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Marcote · Accepted Answer

存储过程通常是用SQL的一种方言编写的 (例如 SQL Server 的 T-SQL，Oracle 的 PL-SQL 等)。这是因为它们为 SQL 增加了额外的功能以使其更强大。

另一方面，你有一个 ORM，比如说 NH，它会生成 SQL。

由 ORM 生成的 SQL 语句不具备编写 T-SQL 存储过程的速度和能力。

这里进入了一个两难境地：我需要一个绑定到 SQL 数据库供应商的超快应用程序，难以维护；还是我需要灵活性，因为我需要面向多个数据库，并且我更喜欢通过编写 HQL 查询而不是 SQL 来缩短开发时间？

存储过程比 SQL 语句快，因为它们在数据库引擎中预编译，并缓存了执行计划。你在 NH 中无法做到这一点，但你有其他选择，比如使用 Cache Level 1 或 2。

此外，试图使用 NH 进行批量操作。在这些情况下，存储过程非常有效。你需要考虑 SP 在更深层次上与数据库交互的能力。

选择可能并不那么明显，因为所有都取决于你正在处理的情景。