A performance tuning tip I saw recently said, “Views don’t perform as well as stored procedures.”

<sigh>

Let’s break this down, just a little.

Definitions

A view is nothing but a query. The definition given by Microsoft is that it’s a virtual table that’s defined by a query. It’s a query that is used to mask data or perform a complex join or similar behaviors. Views are queries that get stored in the database. Views can be easily referred to as if they were a tables. That’s it. I’ve written in the past about views, including how they can possibly perform poorly.

A stored procedure is also a query, or a series of queries, or, a whole lot more. Microsoft’s definition of a stored procedure basically defines it as programming object that can accept input through parameters, perform actions, and provide various types of output. Stored procedures are also stored in the database, but that’s about the end of the direct correlations to a view. Heck, you can call views from stored procedures, so I’m really at a loss as to where this tip comes from.

In short, yes, both these objects have in them queries, but these are fundamentally different objects. You can’t really say that using one or the other is faster because they each do different things. Further, you can write code that will perform poorly using either construct.

In this way, we can run each of these queries independently and compare the results between them.

Results Comparing Stored Procedures With Views

If you run each of the queries above, you will find that they all create a nearly identical execution plan:

You can click on that to make it bigger. If we compare all the different plans, one set of details does stand out:

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:

Query

duration

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%. While that may seem like the view is suffering, please note that the view inside the procedure actually ran faster by 5%. Again, this is explained by the fact that we’re only talking about a 10-microsecond difference. I’m not sure if that’s within the margin for error on the Extended Event sql_batch_complete or not (I couldn’t find documentation stating what it might be), but I’ll bet it’s close. I believe it’s safe to say that the average performance of these queries is identical.

All three queries had eight logical reads.

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

The difference in the performance including compile time for the procedure alone is 700mc better on average than the view. That’s an 8% difference. It was almost that high for the view that used the procedure at 7%.

If we’re just talking compile time then, there is a significant win if we avoid the view. This is no doubt because of the extra work involved in unpacking the view and going through the simplification process within the optimizer. Plus, the view alone in our query was parameterized by the optimizer in order to assist it’s performance over time (as we saw in the average results without the recompile). All that extra work explains the 8% difference.

Let’s Break It

What if we change the query around a little. I decide that all I want to see right now from the view is the CustomerID:

SELECT cdi.CustomerID
FROM dbo.CustomerDeliveryInfo AS cdi
WHERE cdi.CustomerID = 556;

When I execute this, I get a whole new execution plan:

The execution time drops a little to around 190mc on average and the reads go from 8 to 2. The stored procedure would have to get rewritten to only return CustomerID. Does that mean that views are faster than stored procs? Absolutely not. It just means that there is some degree of flexibility built into the view, as a construct, that’s not there in a stored procedure, as a construct. These are fundamentally different objects.

Once more the execution plan will change to something different than before:

Performance drops to about 300mc and we get 10 reads instead of 8. Does that mean that views are slower than stored procedures? No. We’re attempting to compare two different objects that perform two different functions within SQL Server.

Conclusion

Since a stored procedure can actually query a view, suggesting that we use stored procedures instead of views becomes quite problematic. 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. There are reasons to use views as well as reasons to not use them. There are reasons to use stored procedures as well as reasons to not use them. Neither of these objects is preferred above the other because of performance concerns.