Use a view to join tables or a stored procedure for a better execution plan?

Question

First let me state I am a C# developer, not a SQL Server DBA, so please excuse my ignorance on SQL Server database query execution plans.

What I want to know is for a stored procedure which the results are pulled from two or more tables using primary and foreign keys to reference them, is it better practice to:

1. Have a view which performs the joins and contains flattened data for ALL corresponding records from the two (or more) tables and let the stored proc select from and filter that view
2. Let the stored proc do the join and the filtering.

I ask because in a C# world we would separate out the concerns, so one object or function would be responsible for the joining (in this case the view) and another object or function would be responsible for the filtering (in this case the stored proc).

• Which option from above will perform better?
• Which option from above is considered best practice?

In C# separation of concerns is generally considered more important than performance until it is proven that performance has become a serious issue with the code. But I don't know who things stack up in the SQL world!

Answer 1

Which option from above will perform better?

Best case, both will produce exactly the same execution plan, with the same runtime performance.

This can require some careful design and some fairly advanced skills, as Rob Farley mentions in his answer. Rob also has a blog post describing the core issue, and it is also discussed in one of his chapters from the SQL Server MVP Deep Dives Volume 1 book.

Which option from above is considered best practice?

This is likely more a matter of opinion that an established best practice either way, but experienced database professionals tend to limit their use of views to scenarios where they offer real and significant benefits. The more complex a view becomes, (especially if views start to become stacked or layered) the harder it is to avoid undesirable side-effects and the more likely it becomes to encounter optimizer limitations.

The query may be easier to read and maintain if it references a view, but "separation of concerns" is not something you will find database professionals discussing when choosing between a view or writing the underlying query out in a stored procedure. There are many established practices and patterns in programming that do not translate well to database work.

Always bear in mind that a (non-indexed/materialized) view is simply a stored query definition. It is expanded into the referencing query exactly as if copy-and-pasted in place manually before query compilation and optimization begins.

You did not ask for a comprehensive critique of the benefits and drawbacks of using views (which would likely be too broad a question anyway) so I will not attempt to offer one. My final word of caution is that even simple views that optimize well today (perhaps making use of Rob's idea) can easily be modified (often in an innocuous-looking way) so that queries referencing them suddenly encounter an optimizer limitation and a performance cliff.

Answer 2

I show some important things about views in my talk at http://bit.ly/Simplification - the key thing would be to make sure that you're not doing needless joins, that they get optimised out when you don't need those columns.

My talk generally covers the idea of modularisation for an interface for developers, so it's probably quite useful.