When views were first explained to me, they were explained incorrectly. I have, since then, heard others regurgitate the same falsehood countless times.
I operated under this false knowledge for years until recently, working with Query Analyzer and actually breaking down the query plans, I saw "the light."
Most of us were taught that Views are slower because the database has to calculate them before they are used to join to other tables and before the where clauses are applied.
If there are a lot of tables in the view, then this process slows everything down. This explanation seems to make sense on the surface, and is there ever easily accepted. However, nothing is further from the truth on SQL Server!
The fact of the matter is that when a query is being broken down by the SQL Server's optimizer, it looks at the fields within the select to see which of them are needed by the consuming query.
If it needs a given field, then it extracts it from the query definition, along with it's table from the FROM clause, and any restrictions it needs from the WHERE clause or other clauses (GROUP BY, HAVING, etc.)
These extracted elements are then merged into the consuming cases and are generally treated as a sub-query. The optimizer then joins the data together along indexes as best it can, just as it does with non-view elements, and then the entire query is run. The view is NOT pre-calculated just because the query chunk came from a view definition.
So, why do views often run slower than the same code typed directly into a query? Three reasons:
Reason 1 – Sort Order: Sub-queries often suffer from not being sequenced in an order that can easily be merged into the main query. This causes the server to do extra work to so the data returned by the sub-query before merging it. In this circumstance, the data is pre-calculated so it can be sorted.
Reason 2 – Inner Joins: When the view is broken down to see what fields on the select are needed, and then the corresponding table from the case, it has to go one step further. It must consider anything in the where clause that may throw out data. As well, Inner Joins from the table in the case cause can also throw out data if the joined in table does not have a matching row. Since the optimizer does not know whether or not the Inner Join was used as a filtering device, it has to include it. Very often, tables are joined in to show data that the consuming query does not need, not just as a filter. In these cases, the Inner Join only causes SQL Server to do more work for no good reason.
Side note: Left Joins are not used as filters. If a view left joins in a table, but there are no fields used in that table, it will be eliminated when the view is dropped in.
Reason 3 – Redundant Tables Calls: When you create a view, you can actually use another view as a source of data. This practice can be nested practically limitlessly. Since each of these views will be have their query definitions dropped in as a Sub-Query, then it's very possible that the same base table will participate in the query multiple times. This is, generally, just a waste. Why go to the same place multiple times?
There you have it. The truth about SQL Servers and 3 reasons why your views may be slowing down the speed of your queries. You can find solutions to these 3 problems at the SQL Coach blog. Simply point your web browser to http://www.sqlcoach.blogspot.com to get the information you need!