使用编译查询时我们还需要存储过程吗？
当实体框架（或 linq-to-sql）中的编译查询与 SQL Server 结合使用时，使用存储过程实际上仍然有任何性能优势吗？
已编译的查询将被缓存为参数化查询，因此性能应接近等于存储过程。是否有任何情况下存储过程的性能会显着提高？
- 编辑 -
作为对下面 Yakimych 的回答的回应，我并不是想暗示编译的查询与存储过程相同。我试图弄清楚如果您已经在应用程序端完成了所有可能的优化（在本例中是编译查询），存储过程是否仍然是必要的。所以我想我正在寻找存储过程比应用程序端优化和参数化查询的组合更好的原因（这实际上是编译查询的本质）。
我问这个问题的原因之一是因为有很多人似乎认为由于不同的原因不再需要存储过程（即 这篇文章）。
When using compiled queries in entity framework (or linq-to-sql) in combination with SQL Server, is there actually still any performance benefit in using stored procedures?
Compiled queries will be cached as parameterized queries, so performance should be near equal to stored procedures. Is there any situation where stored procedures would perform significantly better?
-- EDIT --
In response to Yakimych's answer below, I didn't mean to imply that compiled queries are the same as stored procedures. I am trying to figure out if sprocs are still necessary if you have done all possible optimizations on the application side (in this case compiled queries). So I guess I'm looking for reasons why a stored procedure would be better than the combination of application-side optimizations and parameterized queries (which is what compiled queries effectively are).
One of the reasons I'm asking this, is because there are many people who seem to think that stored proedures are no longer necessary for different reasons (i.e. this post).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
首先，编译 EF 查询与使用存储过程可以获得的性能优势无关。
根据 http://msdn.microsoft.com/en-us/library/cc853327 .aspx - 当针对概念模型执行查询时，会发生以下操作：
关于
准备查询的说明：因此，如果您编译该查询并稍后重新使用它，则会在每次后续查询执行期间节省应用程序中此操作的时间。但是，您不会影响针对数据库执行的生成的 SQL 代码。 编译查询时获得的性能优势是在应用程序级别。
另一方面，如果您对生成的 SQL 代码不满意并希望优化性能，则通常会使用存储过程在数据库级别。
编辑以回应您的评论和编辑。
在我看来，您的印象是编译 EF 查询会以某种方式更改将针对数据库运行的生成的 SQL 代码（您提到编译的查询会导致参数化 SQL 查询？）。事实并非如此。无论您是直接运行查询还是使用compiledQuery.Invoke，都会针对数据库运行相同的SQL代码。此外，您无法完全控制它，您宁愿依赖 ORM 以最佳方式生成它。在某些情况下，它并不是最优的，这就是 SP 的用武之地。
所以总结一下：
应用端优化。它
节省编译查询的时间
在代码中被重用。
任何一种技术都不能替代另一种技术。
First of all, compiling EF queries has nothing to do with performance benefits that can be achieved by using stored procedures.
According to http://msdn.microsoft.com/en-us/library/cc853327.aspx - the following operations occur when a query executes against a conceptual model:
And the explanation regarding
Preparing the query:So, if you compile the query and re-use it later, what happens is that you save time on this operation in your application during every subsequent query execution. What you don't do, however, is you don't influence the generated SQL code that is executed against the database. The performance benefits you get when compiling queries are at the application level.
On the other hand, you would typically use stored procedures in case you aren't satisfied with the generated SQL code and would like to optimize performance at the database level.
EDIT in response to your comment and edit.
It seems to me that you are under the impression that compiling an EF query would somehow change the generated SQL code that will be run against the database (you mention that compiled queries result in parametrized SQL queries?). That is not the case. No matter whether you run the query directly or use
compiledQuery.Invoke, the same SQL code will be run against the DB. Furthermore, you don't have full control over it, you rather rely on the ORM to generate it in the best possible way. In some cases it is not optimal, and this is where SP's come in.So to sum up:
application-side optimization. It
saves the time to compile a query
that gets re-used in the code.
In no way is one technique a substitute for the other.
给定在 EF 或存储过程中生成的一段类似的参数化 SQL，它们的性能将相同。
然而，DBA 总是有机会根据他们对数据库模式及其使用模式的经验来进一步优化查询。存储过程允许他们在独立于使用它的应用程序的情况下轻松地完成此操作，而 ORM 则不然。
我们有一个极其复杂的 SQL Server 数据库，其中有许多外部系统通过触发器复制数据。对于 EF 来说，我们面临的问题是，在使用任何 ORM 而不是 DBA 时，应用程序开发人员将对在 DB 上触发的 SQL 负责。
Given a comparable piece of parametrized SQL generated in either EF or a stored proc, they will perform equally.
However, a DBA always has the opportunity to further optimise a query based on their experience with the DB schema and its usage patterns. A stored procedure allows them to do this easily in isolation of the applications using it, whereas an ORM doesn't.
We have an extremely complicated SQL Server DB that has many external systems replicating data in and out via triggers. The issue for us with EF is that the responsibility for the SQL that gets fired at the DB will become the application developers responsibility when using any ORM rather than the DBAs.
来自知名专家的一些固定答案：Paul Nielsen 为什么要使用存储过程？亚当
·马哈尼克：
A few canned answers from well known experts: Paul Nielsen Why use Stored Procedures?
Adam Machanic: No, stored procedures are NOT bad