存储过程真的能提高 MS SQL/.NET 的性能吗? [英] Do Stored Procedures really boost performance in MS SQL / .NET?

问题描述

Jeff Atwood 在这里写了这篇文章，虽然我明白存储过程可以提供的理论性能提升，这似乎是一个巨大的痛苦.

Jeff Atwood wrote about this here, and while I understand the theoretical performance boost a stored procedure could offer, it does seem like a tremendous pain.

使用存储过程时，您认为哪些类型的查询性能提升最大，哪些类型的查询更愿意即时构建?

What types of queries would you see the most performance increase using stored procedures, and what types of queries would you rather just build on the fly?

我们将不胜感激.

推荐答案

存储过程/无存储过程参数已成为一个宗教问题.对于每个强调 procs 优化执行计划的人，另一个人指出在大多数现代 DBMS 中缓存和优化了常见的动态查询.对于指出 proc 可能提供的安全性的任何人，另一个人解释说动态查询可以同样安全.有些人喜欢无需重新编译应用即可更改 proc 的灵活性，而另一些人则认为应在应用代码中捕获查询，以便它们在相同的代码库中生存和发展.

The stored proc/no stored procs argument has become a religious issue. For every person that emphasizes optimized execution plans for procs, another points out common dynamic queries are cached and optimized in most modern DBMSes. For anyone that points out the security a proc might offer, another explains that dynamic queries can be made just as secure. Some like the flexibility of changing a proc without recompiling your app, while others argue queries should be captured in app code so they live and grow in the same code base.

我说...

做你喜欢的事.我怀疑我们能否得出正确的答案.如果 proc 很麻烦，请不要使用它们.如果它们看起来是个好主意，那就去做吧.我使用过这两种模型，老实说我没有偏好.不管有没有他们，我的工作效率都很高.

Do what you like. I doubt we can come up with the correct answer. If procs are a hassle, don't use them. If they seem like a good idea, go for it. I've worked with both models and I honestly don't have a preference. I'm productive with or without 'em.

这篇关于存储过程真的能提高 MS SQL/.NET 的性能吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！