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

Question

Please excuse me if this has been asked before (I did not find the question if it has)

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

Answer 1

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?

Answer 2

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.

Answer 3

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.

Bye.

Answer 4

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.

Answer 5

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

Cheers.

Answer 6

Stored Procs and Views are different and have different purposes. I look at Views as canned querries. I look at Stored Procs as code modules.

For example lets say you have a table called tblEmployees with these two fields (among others)DateOfBirth and MaleFemale. A view called viewEmployeesMale which filters out only male employess 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 proc is better suited for dealing with this. Date manipulation especially where nulls are a factor can become very tricky.