存储过程和 ORM [英] Stored Procedures and ORM's

问题描述

与使用 ORM(nHibernate、EF 等)处理某些 CRUD 操作相比，存储过程的目的是什么?要调用存储过程，我们只是传递几个参数，并通过 ORM 发送整个 SQL 查询，但这只是性能和安全性问题还是有更多优势?

What's the purpose of stored procedures compared to the use of an ORM (nHibernate, EF, etc) to handle some CRUD operations? To call the stored procedure, we're just passing a few parameters and with an ORM we send the entire SQL query, but is it just a matter of performance and security or are there more advantages?

我问这个是因为我从未使用过存储过程(我只是用 ORM 编写所有 SQL 语句并执行它们)，并且一位客户告诉我，我将不得不在下一个项目中使用存储过程，我正在考虑何时使用它们.

I'm asking this because I've never used stored procedures (I just write all SQL statements with an ORM and execute them), and a customer told me that I'll have to work with stored procedures in my next project, I'm trying to figure out when to use them.

推荐答案

存储过程通常用 SQL 的方言(T-SQL for SQL Server、PL-SQL Oracle 等)编写.那是因为它们为 SQL 添加了额外的功能以使其更强大.另一方面，您有一个 ORM，比如说生成 SQL 的 NH.

Stored Procedures are often written in a dialect of SQL (T-SQL for SQL Server, PL-SQL Oracle, and so on). That's because they add extra capabilities to SQL to make it more powerful. On the other hand, you have a ORM, let say NH that generates SQL.

ORM 生成的 SQL 语句与编写 T-SQL 存储过程的速度或能力不同.这就是进退两难的地方:我是否需要与 SQL 数据库供应商绑定的超快速应用程序，难以维护，或者我是否需要灵活，因为我需要针对多个数据库，并且我更喜欢通过编写 HQL 查询而不是 SQL 来缩短开发时间那些?

the SQL statements generated by the ORM doesn't have the same speed or power of writing T-SQL Stored Procedures. Here is where the dilemma enters: Do I need super fast application tied to a SQL Database vendor, hard to maintain or Do I need to be flexible because I need to target to multiple databases and I prefer cutting development time by writing HQL queries than SQL ones?

存储过程比 SQL 语句更快，因为它们是在数据库引擎中预编译的，并缓存了执行计划.您不能在 NH 中这样做，但您有其他选择，例如使用缓存级别 1 或 2.

Stored Procedure are faster than SQL statements because they are pre-compiled in the Database Engine, with execution plans cached. You can't do that in NH, but you have other alternatives, like using Cache Level 1 or 2.

另外，尝试使用 NH 进行批量操作.存储过程在这些情况下工作得很好.您需要考虑 SP 与数据库更深层次的对话.

Also, try to do bulk operations with NH. Stored Procedures works very well in those cases. You need to consider that SP talks to the database in a deeper level.

选择可能并不那么明显，因为这一切都取决于您正在处理的场景.

The choice may not be that obvious because all depends of the scenario you are working on.

这篇关于存储过程和 ORM的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！