SQL-Server 性能:存储过程或视图哪个更快? [英] SQL-Server Performance: What is faster, a stored procedure or a view?

问题描述

SQL Server 2005/2008 中哪个更快，存储过程还是视图?

What is faster in SQL Server 2005/2008, a Stored Procedure or a View?

正如你们中的许多人指出的那样，我太含糊了.让我试着说得更具体一点.
我想知道视图中特定查询与存储过程中完全相同查询的性能差异.(我仍然感谢所有指出它们不同功能的答案)

推荐答案

存储过程 (SP) 和 SQL 视图是不同的野兽"，正如本文多次所述.

Stored Procedures (SPs) and SQL Views are different "beasts" as stated several times in this post.

如果我们排除一些与查询计划缓存相关的[通常是次要的，除了边缘情况]性能考虑因素，与绑定到存储过程相关的时间等，这两种方法在整体上是等效的，性能方面.但是...

If we exclude some [typically minor, except for fringe cases] performance considerations associated with the caching of the query plan, the time associated with binding to a Stored Procedure and such, the two approaches are on the whole equivalent, performance-wise. However...

视图仅限于可以在单个 SELECT 语句中表达的任何内容(好吧，可能还有 CTE 和其他一些技巧)，但总的来说，视图与声明式查询相关.另一方面，存储过程可以使用各种过程类型构造(以及声明式构造)，因此，使用 SP，人们可以手工制作一种解决给定查询的方法这可能比 SQL-Server 的查询优化器所做的(基于单个声明性查询)更有效.在这些情况下，SP 可能要快得多(但要注意……优化器非常聪明，而且无需花费太多时间就可以使 SP 比等效视图慢得多.)

A view is limited to whatever can be expressed in a single SELECT statement (well, possibly with CTEs and a few other tricks), but in general, a view is tied to declarative forms of queries. A stored procedure on the other can use various procedural type constructs (as well as declarative ones), and as a result, using SPs, one can hand-craft a way of solving a given query which may be more efficient than what SQL-Server's query optimizer may have done (on the basis of a single declarative query). In these cases, an SPs may be much faster (but beware... the optimizer is quite smart, and it doesn't take much to make an SP much slower than the equivalent view.)

除了这些性能方面的考虑之外，SP 更通用，并且允许比视图更广泛的查询和操作.

Aside from these performance considerations, the SPs are more versatile and allow a broader range of inquiries and actions than the views.

这篇关于SQL-Server 性能:存储过程或视图哪个更快?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！