Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Poor Little Misunderstood Views

Some people love ‘em, some people hate ‘em. But, one thing I have found to be nearly universal is that most people misunderstand how Views are used in SQL Server.

The Backdrop

A View is merely a pre-defined query that can be treated in many ways as a table. This allows DBAs or Database Developers to pull data from several tables and expose it as a single “virtual table”. This has many advantages: the consumer of the view doesn’t have to have a detailed understanding of the database table layouts (schema); and a single addition or fix to the view then ripples out to all the consuming queries in one fell swoop.

Please note that often views are used as a security measure. They are used to control which information is exposed to the consuming applications as an abstraction layer between the app and the physical data. For this article, I am by and large ignoring this particular property, although many of you will have to wrestle with it whilst performance tuning.

The Problem

Many developers struggle with the performance of Views. Most note that they operate slower than simply joining in the information they need from the base tables in every query, throwing out the advantages of the views. I know many DBAs and Database Developers who live by the “There’s no views allowed on my server” rule.

The Solution, or, How to Make Life Beeter for Everyone Views can be a friend or a foe. The later scenario is generally born out of a lack or misunderstanding of the rules of Views. Once you get to know how SQL Server actually makes use of a view, it will make much more sense, and you can make more appropriate choices which will allow you to benefit from the Views without impacting performance.

The Misconceptions

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 a very long time. A few years back I began working with query plans and I finally saw “the light.”

The falsehood is 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 therefore 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 its table from the FROM clause, and any restrictions it needs from the WHERE clause or other clauses (INNER JOIN, GROUP BY, HAVING, etc.) These extracted elements are then merged into the outer SQL statement. 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 it came from a view definition.

So, why does it often run slower? Three reasons:

Reason 1 - Sort Order: 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 sort the data returned by the sub-query before merging it. In this circumstance, the data is pre-calculated so it can be sorted. However, if the index that is used by the query orders the data correctly, these performance blockers go away. I presume these crops up in views frequently since the writer of the view cannot anticipate which elements of the view will be extracted by the calling queries and, therefore, does not pay close attention to the underlying indices.

Fix 1 - Watch your query plans. If an appropriate index can be created that will return data in the same order that is needed for the join, then it will be pulled in without having to sort it, thus avoiding the need to pre-fetch and pre-calculate.

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 FROM clause, 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 FROM clause can also throw out data if the joined in table does not have a matching row. Since the optimizer doesn’t 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 doesn’t need, not just as a filter. In these cases, the Inner Join only causes SQL Server to do more work for no good reason.

Fix 2 - Whenever possible, limit the number of inner joins in the View definition. Try to only use Inner Joins when you are certain most or all consuming queries will need data from the joined table. If there are many cases where the consuming data will not, consider multiple Views to service the various cases.

Side note: Left Joins are not used as filters. If a View left joins to a table, but there are no fields used in that table, it will be eliminated when the view is pulled 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 have their query definitions pulled 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?

Fix 3 - Try to limit yourself to only using 1 view in a query. Also, try to avoid using Views as a base table within another view. If you find yourself needing info from multiple views, consider breaking form and joining in the base tables rather than pulling in the views. In some cases, doing this has allowed me to call data from a table once rather than THREE OR FOUR times, bringing the response time down by thousands of percents (and I’m not kidding). Sometimes you have to ignore the advantages of the views to gain performance.

Conclusion

Views do have their place, but knowing how they are used will save you grief and performance time. Knowing the views are NOT pre-calculated, but rather are broken down into its parts and the “useful bits” (and inner joined info, which is sometimes just fluff) pulled into the main query can explain a lot of the performance impacts of these objects.

Happy tuning! For other tuning articles, check out my performance tuning series.

Comments

Posted by Steve Jones on 30 July 2010

Interesting views here. I hadn't often heard the "views are slower" argument in my career.

Typically I've used views, often as an abstraction so that it protects the app from schema changes that may break things. I've also used them for row level security, and they worked fairly well.

Posted by wbrianwhite on 31 July 2010

Since you can index views (with some pre-conditions) in SQL Server 2005 and up, I don't think the 'slower' objection would be very relevant any more.  

Posted by ta.bu.shi.da.yu on 1 August 2010

If you have to materialize a view, then you are still looking at update/insert/delete overhead that is at least as much as creating a number of indexes on a base table.

Posted by ALZDBA on 2 August 2010

I recall one of the first "performance calls" of one of our project leaders stating "my select * doesn't perform".

His TSQL : "Select * from theobject"

My analysis showed he actually used 24 objects, of which 14 were tables. The others were (nested) functional views. (e.g. V_Active_products)

View materialisation is indeed the big issue.

Posted by Phil Factor on 2 August 2010

I enjoyed reading this. I think that the construction of Views needs a great deal of care. I value their use as an abstraction layer that takes away the requirement for devs to have access rights to view base tables. Also, many widgets, especially those that have built-in scrolling, tend to require Views or Tables rather then stored Procedures, and so it is handy to be able to provide these in a secure way.

Posted by david.r.buckingham on 2 August 2010

Unless the view provides a necessary abstraction or row/column security, then I normally try to eliminate it.  The issue is that most of the database developers I have had to performance tune code for fall under Reason 2 & 3 in the article.  I have seen as many as 23 inner joins in a single view, views nested 7 levels deep, and even one particular table referenced over two dozen times (through the nested/joined views).  I have also seen unfiltered aggregrate sub-queries embedded within views that were commonly joined back to the table being aggregated in a manner that did cause it to pre-calculate a significant amount of the results that were subsequently disgarded.

Views certainly have their place, but in my experience they are all too often misused...making me instantly suspicious of any view I find.

Posted by jhopkins on 9 August 2010

One option to deal with pre-fetching in Views might be to convert to a parameterized View ("Inline Function"). The approach has obvious limitations depending on usage of the View and correlated changes which would be required, but at least it limits the pre-fetching!

Posted by Steve Stocker on 19 August 2010

Views are an excellent way to manage performance on a database. If views are created for common access patterns, those relationships can be represented in an optimized and correct fashion once and re-used. If you have everyone re-invent the wheel every time not only do you open yourself up to that many more performance problems and bugs. Not to mention it is a lot easier to digest an application where you know the exact same query is used a number of times rather than any number of variations of the same query.

One of the more credible arguments is that once you put views out for consumption developers will use them inappropriately. That they will choose a view that is doing more work than is necessary for what they are trying to accomplish (basically they are too lazy to pull up the view source and think through what is happening underneath). I ask this question though, is it more difficult to audit the use of an existing view or audit all those different versions of roll-your-own queries being used? And what better way to establish that a new view is added to the library then to have someone present an inappropriate use of an existing view for code review…

One downside to views is that the SQL Server optimizer has what I could consider to be bugs when CTEs or in-line views within a view, and less commonly when a view calls a view. This seems to come mainly from SQL Server pushing filtering criteria up the stack. These problems are all correctable though. Often a superfluous looking COALESCE placed on the join or filter criteria will solve this problem. At least in SQL Server you are going to run into cases where using a view comes up with a different plan than using the same SQL exploded into a single query. When this comes up, you tune and evaluate. And if you use views, IMO you will be doing LESS of all that overall, not more.

If I had to knock this article, I find the guidelines presented here a little broad and bordering Draconian. How about just thoroughly evaluating the performance of each view and tuning it appropriately? When a particular query does not seem that it can be satisfactorily tuned that is when it is time to start looking at new indexes on the base table and/or schema bound views and/or filtered indexes; unless a refactoring of the schema itself is feasible.  Why encourage people to use views and then backpedal by presenting your own set of FUD? It just goes back to properly reviewing the performance of all application code, not placing a body condom around the view object.

All schemas and their use are different. But generally I see views as being pretty critical to breaking down schema access into digestible chunks. In a lot of cases, I liken not using views to writing a C# or Java class where you cram an excessive amount of logic and nesting into a single method rather than structuring the code in discrete methods that are properly abstracted.

Leave a Comment

Please register or log in to leave a comment.