SQL-Server 性能：存储过程或视图哪个更快？

SQL-Server Performance: What is faster, a stored procedure or a view?

提问by 7wp

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

SQL Server 2005/2008、存储过程或视图中哪个更快？

EDIT:As many of you pointed out, I am being too vague. Let me attempt to be a little more specific.
I wanted to know the performance difference for a particular query in a View, versus the exact same query inside a stored procedure. (I still appreciate all of the answers that point out their different capabilities)

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

回答by mjv

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

正如这篇文章中多次提到的，存储过程 (SP) 和 SQL 视图是不同的“野兽”。

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...

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

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 efficientthan 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.)

视图仅限于可以在单个 SELECT 语句中表达的任何内容（好吧，可能使用 CTE 和其他一些技巧），但通常，视图与查询的声明形式相关联。另一方面，存储过程可以使用各种过程类型构造（以及声明式构造），因此，使用 SP，可以手工制作一种解决给定查询的方法，这种方法可能比 SQL Server 的方法更有效查询优化器可能已经完成（基于单个声明性查询）。在这些情况下，SP 可能要快得多（但要注意......优化器非常聪明，并且使 SP 比等效视图慢很多并不需要太多。）

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

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

回答by p.campbell

Unfortunately, they're not the same type of beast.

不幸的是，它们不是同一种野兽。

A stored procedure is a set of T-SQL statements, and CAN return data. It can perform all kinds of logic, and doesn't necessarily return data in a resultset.

存储过程是一组 T-SQL 语句，可以返回数据。它可以执行各种逻辑，并且不一定返回结果集中的数据。

A view is a representation of data. It's mostly used as an abstraction of one or more tables with underlying joins. It's always a resultset of zero, one or many rows.

视图是数据的表示。它主要用作一个或多个具有底层连接的表的抽象。它总是零、一或多行的结果集。

I suspect your question is more along the lines of:

我怀疑你的问题更多的是：

Which is faster: SELECTing from a view, or the equivalent SELECTstatement in a stored procedure, given the same base tables performing the joins with the same where clauses?

哪个更快：SELECT从视图或SELECT存储过程中的等效语句，给定相同的基表，使用相同的 where 子句执行连接？

回答by David

This isn't really an answerable question in that an answer will hold true in all cases. However, as a general answer for an SQL Server specific implementaion...

这并不是一个真正可以回答的问题，因为答案在所有情况下都适用。但是，作为 SQL Server 特定实现的一般答案......

In general, a Stored Procedure stands a good chance of being faster than a direct SQL statement because the server does all sorts of optimizations when a stored procedure is saves and executed the first time.

通常，存储过程比直接 SQL 语句更快，因为服务器在第一次保存和执行存储过程时会进行各种优化。

A view is essentially a saved SQL statement.

视图本质上是保存的 SQL 语句。

Therefore, I would say that in general, a stored procedure will be likely to be faster than a view IF the SQL statement for each is the same, and IF the SQL statement can benefit from optimizations. Otherwise, in general, they would be similar in performance.

因此，我会说一般来说，如果每个存储过程的 SQL 语句相同，并且如果 SQL 语句可以从优化中受益，那么存储过程可能比视图更快。否则，一般而言，它们的性能将相似。

Reference these links documentation supporting my answer.

参考支持我的答案的这些链接文档。

http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx

http://www.sql-server-performance.com/tips/stored_procedures_p1.aspx

http://msdn.microsoft.com/en-us/library/ms998577.aspx

http://msdn.microsoft.com/en-us/library/ms998577.aspx

Also, if you're looking for all the ways to optimize performance on SQL Server, the second link above is a good place to start.

此外，如果您正在寻找优化 SQL Server 性能的所有方法，上面的第二个链接是一个不错的起点。

回答by RRUZ

I prefer stored procedures due to Allow greater control over data, if you want to build a good, secure modular system then use stored procedures, it can run multiple sql-commands, has control-of-flow statements and accepts parameters. Everything you can do in a view you can do in a stored procedure. But in a stored procedure, you can do with much more flexibility.

我更喜欢存储过程，因为允许更好地控制数据，如果你想构建一个好的、安全的模块化系统，那么使用存储过程，它可以运行多个 sql 命令，具有控制流语句并接受参数。您可以在视图中执行的所有操作都可以在存储过程中执行。但是在存储过程中，您可以更灵活地进行操作。

回答by Tejasvi Hegde

In short, based on my experience in some complex queries, Stored procedure gives better performance than function.

简而言之，根据我在一些复杂查询中的经验，存储过程比函数提供了更好的性能。

But you cannot use results of stored procedure in select or join queries.

但是您不能在选择或联接查询中使用存储过程的结果。

If you don't want to use the result set in another query, better to use SP.

如果不想在另一个查询中使用结果集，最好使用 SP。

And rest of the details and differences are mentioned by people in this forum and elsewhere.

本论坛和其他地方的人都提到了其余的细节和差异。

回答by JustinT

I believe that another way of thinking would be to use stored procedures to select the views. This will make your architecture a loosely coupled system. If you decide to change the schema in the future, you won't have to worry 'so' much that it will break the front end.

我相信另一种思考方式是使用存储过程来选择视图。这将使您的架构成为松散耦合的系统。如果您决定在将来更改架构，您将不必担心它会破坏前端。

I guess what I'm saying is instead of sp vs views, think sp and views :)

我想我说的不是 sp 与视图，而是 sp 和视图 :)

回答by Michael Riley - AKA Gunny

Stored procedures and views are different and have different purposes. I look at views as canned queries. I look at stored procedures as code modules.

存储过程和视图是不同的，具有不同的用途。我将视图视为预设查询。我将存储过程视为代码模块。

For example let's say you have a table called tblEmployeeswith these two columns (among others): DateOfBirthand MaleFemale.

例如，假设您有一个表tblEmployees，其中包含以下两列（以及其他列）：DateOfBirth和MaleFemale。

A view called viewEmployeesMalewhich filters out only male employees can be very useful. A view called viewEmployeesFemaleis also very useful. Both of these views are self describing and very intuitive.

一种称为viewEmployeesMale仅过滤掉男性员工的视图非常有用。一个名为的视图viewEmployeesFemale也非常有用。这两种视图都是自我描述的并且非常直观。

Now, lets say you need to produce a list all male employees between the ages of 25 and 30. I would tend to create a stored procedure to produce this result. While it most certainly could be built as a view, in my opinion a stored procedure is better suited for dealing with this. Date manipulation especially where nulls are a factor can become very tricky.

现在，假设您需要生成一个所有年龄在 25 到 30 岁之间的男性员工的列表。我倾向于创建一个存储过程来生成此结果。虽然它肯定可以构建为视图，但在我看来，存储过程更适合处理这个问题。日期操作，尤其是在空值是一个因素的情况下可能会变得非常棘手。

回答by IronRod

A couple other considerations: While performance between an SP and a view are essentially the same (given they are performing the exact same select), the SP gives you more flexibility for that same query.

其他一些注意事项：虽然 SP 和视图之间的性能基本相同（假设它们执行完全相同的选择），但 SP 为相同的查询提供了更大的灵活性。

• The SP will support ordering the result set; i.e., including an ORDER BY statement. You cannot do so in a view.
• The SP is fully compiled and requires only an exec to invoke it. The view still requires a SELECT * FROM viewto invoke it; i.e., a select on the compiled select in the view.

• SP 将支持对结果集进行排序；即，包括 ORDER BY 语句。您不能在视图中这样做。
• SP 是完全编译的，只需要一个 exec 即可调用它。该视图仍然需要SELECT * FROM view调用它；即，在视图中编译选择上的选择。

回答by Ade

I know I'm not supposed to turn this into a "discussion", but I'm very interested in this and just thought I'd share my empirical observations of a specific situation, with particular reference to all the comments above which state that an equivalent SELECT statement executed from within a Stored Procedure and a View should have broadly the same performance.

我知道我不应该把它变成一个“讨论”，但我对此很感兴趣，只是想分享我对特定情况的经验观察，特别参考上面所有的评论，这些评论指出从存储过程和视图中执行的等效 SELECT 语句应该具有大致相同的性能。

I have a view in database "A" which joins 5 tables in a separate database (db "B"). If I attach to db "A" in SSMS and SELECT * from the view, it takes >3 minutes to return 250000 rows. If I take the select statement from the design page of the view and execute it directly in SSMS, it takes < 25 seconds. Putting the same select statement into a stored procedure gives the same performance when I execute that procedure.

我在数据库“A”中有一个视图，它在一个单独的数据库（db“B”）中连接了 5 个表。如果我附加到 SSMS 中的 db "A" 并从视图中选择 *，则需要 3 分钟以上才能返回 250000 行。如果我从视图的设计页面中取出 select 语句并直接在 SSMS 中执行它，则需要 < 25 秒。当我执行该过程时，将相同的 select 语句放入存储过程会产生相同的性能。

Without making any observations on the absolute performance (db "B" is an AX database which we are not allowed to touch!), I am still absolutely convinced that in this case using an SP is an order of magnitude faster than using a View to retrieve the same data, and this applies to many other similar views in this particular case.

在没有对绝对性能进行任何观察的情况下（db "B" 是一个我们不允许接触的 AX 数据库！），我仍然绝对相信在这种情况下使用 SP 比使用视图要快一个数量级检索相同的数据，在这种特殊情况下，这适用于许多其他类似的视图。

I don't thinkit's anything to do with creating a connection to the other db, unless by using a view it somehow can never cache the connection whereas the select does, because I can switch between the 2 selects in the same SSMS window repeatedly and the performance of each query remains consistent. Also, if I connect directly to db "B" and run the select without the dbname.dbo.... refs, it takes the same time.

我认为这与创建到另一个数据库的连接没有任何关系，除非通过使用视图，它以某种方式永远无法缓存连接，而 select 可以缓存连接，因为我可以在同一个 SSMS 窗口中的 2 个选择之间反复切换和每个查询的性能保持一致。此外，如果我直接连接到 db "B" 并在没有 dbname.dbo .... refs 的情况下运行选择，则需要相同的时间。

Any thoughts anyone?

有人有什么想法吗？

回答by Nabster

Found a detailed performance analysis: https://www.scarydba.com/2016/11/01/stored-procedures-not-faster-views/

找到了详细的性能分析：https: //www.scarydba.com/2016/11/01/stored-procedures-not-faster-views/

Compile Time Comparison:

编译时间比较：

There is a difference in the compile time between the view by itself and the stored procedures (they were almost identical). Let's look at performance over a few thousand executions:

View AVG: 210.431431431431

Stored Proc w/ View AVG: 190.641641641642

Stored Proc AVG: 200.171171171171

This is measured in microsends, so the variation we're seeing is likely just some disparity on I/O, CPU or something else since the differences are trivial at 10mc or 5%.

视图本身和存储过程之间的编译时间存在差异（它们几乎相同）。让我们看看几千次执行的性能：

查看平均：210.431431431431

存储过程带视图 AVG：190.641641641642

存储过程平均：200.171171171171

这是用微发送来衡量的，所以我们看到的变化可能只是 I/O、CPU 或其他方面的一些差异，因为差异在 10mc 或 5% 上是微不足道的。

What about execution time including compile time, since there is a difference:

包括编译时间在内的执行时间怎么样，因为有区别：

Query duration View AVG: 10089.3226452906

Stored Proc AVG: 9314.38877755511

Stored Proc w/ View AVG: 9938.05410821643

查询时长查看AVG：10089.3226452906

存储过程平均：9314.38877755511

存储过程带视图 AVG：9938.05410821643

Conclusion:

结论：

With the exception of the differences in compile time, we see that views actually perform exactly the same as stored procedures, if the query in question is the same.

除了编译时间的不同，我们看到视图实际上与存储过程完全相同，如果有问题的查询是相同的。