49

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

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)

ROMANIA_engineer
  • 54,432
  • 29
  • 203
  • 199
7wp
  • 12,505
  • 20
  • 77
  • 103

11 Answers11

80

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

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

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

mjv
  • 73,152
  • 14
  • 113
  • 156
  • 1
    I just ran into this on a Crystal Report that had been changed from a view to a stored procedure. The SP was significantly slower. Several minutes, versus seconds for the view. The report had several parameters to filter by. With the SP, all the results of the SELECT statement were being returned, and then filtered. With a view, the filtering criteria appears to be added dynamically. You can do the same with a SP by adding parameters, and using them in the WHERE clause. But don't just copy the SELECT statement from a view, drop it in a SP and expect the same results. – KevinD Jan 17 '20 at 21:15
  • what is the source for you answer? Could you please share – Shad Jun 15 '22 at 09:31
14

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.

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 SELECT statement in a stored procedure, given the same base tables performing the joins with the same where clauses?

p.campbell
  • 98,673
  • 67
  • 256
  • 322
10

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

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.

A view is essentially a saved SQL statement.

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.

Reference these links documentation supporting my answer.

http://www.sql-server-performance.com/tips/stored_procedures_p1.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.

David
  • 72,686
  • 18
  • 132
  • 173
  • Keeping in mind what you said, Would something crazy like converting all views to stored procedures be a benefit ?? – 7wp Oct 26 '09 at 07:22
  • I don't think so. Views have a place, too. I wouldn't go overboard. I would not turn to a stored procedure as my first stop for improving performance. I'd start by making sure my indexes are correct. I could be wrong on this, but I don't usually consider performance when deciding to create a stored procedure. Usually I'm just working on a query based on untrusted user input and I use a stored procedure rather than a contented SQL statement for security reasons. – David Oct 26 '09 at 11:36
5

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.

johnnyRose
  • 7,310
  • 17
  • 40
  • 61
RRUZ
  • 134,889
  • 20
  • 356
  • 483
5

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.

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

Tejasvi Hegde
  • 2,694
  • 28
  • 20
4

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 :)

johnnyRose
  • 7,310
  • 17
  • 40
  • 61
JustinT
  • 161
  • 1
  • 5
  • 2
    but i'm more concerned about performance rather than loose coupling. (I user nHibernate for loose coupling.) but won't a stored procedure selecting from a view hurt performance?? – 7wp Oct 26 '09 at 07:13
3

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 tblEmployees with these two columns (among others): DateOfBirth and MaleFemale.

A view called viewEmployeesMale which filters out only male employees can be very useful. A view called viewEmployeesFemale is also very useful. Both of these views are self describing and very intuitive.

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Michael Riley - AKA Gunny
  • 5,074
  • 4
  • 42
  • 89
  • Thanks for your answer. I understand the differences of either one, but you didn't talk about performance implications, which is what i'm more interested in. You said in your example you would use a SP because date manipulation is tricky, but you didn't say anything about performance. Are you saying the SP would be faster in your scenario? – 7wp Oct 26 '09 at 07:16
  • I don't really know which one would be faster. You may have to ask someone who understands the internals of the database. – Michael Riley - AKA Gunny Oct 26 '09 at 23:47
  • how can a strategy be better suited for something if its an opinion. Where are your tests. performance wise the view and procedure you described will essentially be handled the same. – Plixxer Jul 03 '19 at 16:16
1

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.

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.

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.

I don't think it'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.

Any thoughts anyone?

Ade
  • 96
  • 5
1

Views:

  • We can create index on views (not possible in stored proc)
  • it's easy to give abstract views(only limited column access of multiple table ) of table data to other DBA/users

Store Procedure:

  • We can pass parameters to sp(not possible in views)
  • Execute multiple statement inside procedure (like insert, update,delete operations)
Swapnil Thube
  • 103
  • 1
  • 5
0

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.

  • 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 view to invoke it; i.e., a select on the compiled select in the view.
AdvApp
  • 1,094
  • 1
  • 14
  • 27
0

Found a detailed performance analysis: 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%.

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

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.

Community
  • 1
  • 1
Nabster
  • 1,187
  • 2
  • 10
  • 27