“I hate views,” the DBA said. “They kill performance. And nested views are even worse.”
Wrong. I’ll prove it.
I’m going to use the 50GB StackOverflow2013 database, and I’ll start by creating a couple of indexes on the Users table to help our queries, then create a view on the Users table:
|
1 2 3 4 5 6 7 8 9 |
USE StackOverflow2013; GO DropIndexes; CREATE INDEX DisplayName ON dbo.Users(DisplayName); CREATE INDEX Location ON dbo.Users(Location); GO CREATE OR ALTER VIEW dbo.vwUsers AS SELECT * FROM dbo.Users; GO |
First, compare plans for the table and the view.
If I run two identical queries looking for users in a specific city:
|
1 2 3 4 5 6 7 8 9 10 11 |
SET STATISTICS IO, TIME ON; SELECT * FROM dbo.Users WHERE DisplayName = 'Brent Ozar' AND Location = 'San Diego, CA, USA'; SELECT * FROM dbo.vwUsers WHERE DisplayName = 'Brent Ozar' AND Location = 'San Diego, CA, USA'; |
Both query plans use the index, estimate the same number of rows, and work the same way:
Both queries do the same number of reads, and take the same amount of time:
There’s no monster hiding under your bed.
Same thing with nested views:
there’s nothing innately wrong with them.
Let’s go deep and create a whole series of nested Babushka dolls ten layers deep:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
CREATE OR ALTER VIEW dbo.vwUsers_Nesty1 AS SELECT * FROM dbo.vwUsers; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty2 AS SELECT * FROM dbo.vwUsers_Nesty1; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty3 AS SELECT * FROM dbo.vwUsers_Nesty2; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty4 AS SELECT * FROM dbo.vwUsers_Nesty3; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty5 AS SELECT * FROM dbo.vwUsers_Nesty4; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty6 AS SELECT * FROM dbo.vwUsers_Nesty5; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty7 AS SELECT * FROM dbo.vwUsers_Nesty6; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty8 AS SELECT * FROM dbo.vwUsers_Nesty7; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty9 AS SELECT * FROM dbo.vwUsers_Nesty8; GO CREATE OR ALTER VIEW dbo.vwUsers_Nesty10 AS SELECT * FROM dbo.vwUsers_Nesty9; GO |
And then query the outermost one. It still uses the index, still estimates row counts correctly, and still does just 6 page reads in 0ms.
If you’re purely using views for the purpose of abstraction, you’re fine. You can use them for assigning permissions, for example, and you don’t run into any problems at all – until you start getting fancy with the code.
The problem isn’t the view.
The problem is what you put in it.
Let’s go back to a simple non-nested view and start putting some business logic. For example, let’s say we want to hide users who’ve left a comment that got highly downvoted:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
CREATE OR ALTER VIEW dbo.vwUsers_Filtered AS SELECT * FROM dbo.Users u WHERE NOT EXISTS (SELECT * FROM dbo.Comments c WHERE c.UserId = u.Id AND c.Score < 0) GO SELECT * FROM dbo.vwUsers_Filtered WHERE DisplayName = 'Brent Ozar' AND Location = 'San Diego, CA, USA'; GO |
Now, all of a sudden, the query takes longer to execute and does over a million logical reads, because it needs to scan the Comments table. The execution plan shows that it’s a hot mess and desperately needs an index:
But the problem still isn’t the view, as evidenced by the fact that if we inline that code into a single query, not referencing the view, we get the same execution plan and behavior:
Views aren’t killing performance.
That code sucks by itself, or in a view.
Stop blaming views for crappy code. If you copy/paste all the code from the views and inline it directly into a single query, you’re likely to recoil in horror. The problem isn’t the views: the problem is that someone put bad code into a view, and therefore made it reusable.
Views are great for reusability.
If you choose to reuse crappy code, well, that says less about the view, and more about you.
37 Comments. Leave new
Is that a stored procedure call without an EXEC or a schema qualification?
You saucy wench.
I FEEL SEEN
Would highly nested views, each with some degree of complexity, at least make preparing an execution plan more difficult? And, possibly have it give up before an optimal plan is found?
Thanks
Martin – why not test it to see?
I agree that views as a rule can’t be blamed for performance. However, to claim that it’s still true for nested views, I am not sure I agree. In your example, you just did a SELECT * for each nested level, and the optimizer isn’t that dumb. It knows better than that and has no problem transforming it into something equivalent to the original SQL.
The real problem starts when you try to do something more than the original view intended to do. For example, if the view had GROUP BY foo but you create a new view reading from the first view and do a GROUP BY bar, the performance will suffer for it, especially if the optimizer cannot fully transform & inline the expression into a single GROUP BY foo, bar (and was that what we really meant to do? Maybe, maybe not…). There will be other cases where nesting views will fail to inline completely. For that reason, I’d prefer that they used CTEs instead of nesting views or at least have tight restrictions on what can be used as a base view for other views (e.g. no aggregations or zany joins or anything that could be problematic for optimizer to transform).
Ben – as I said clearly in the post, those are query issues, not view issues. If you copy/paste the code out of the views, and you do that same stunt in plain inline T-SQL, it’s going to suck poorly as well.
If you’d like to post repro demos like I did, by all means, go for it. Otherwise, it’s just a case of, “Trust me, I saw something on the internet this one time…”
Maybe I should rephrase a bit. If one is encouraged to nest views, one is more likely to do it wrong and thus suffer the pain for it. It’s more about developing habits that are less likely to introduce bugs or performance issues than whether it actually works.
I also don’t like the fact that if I am dealing with a nested view, I’ll have to open multiple tabs (oh, the humanity!) just to comprehend what’s going on. CTEs at least keep it in one place and you can read it all. Thus, I can claim that nested query is very bad for my brain’s performance.
Ben – you can just as easily do good code with views as you can with inline T-SQL.
CTEs do keep it in one place, but that’s as much of a problem as it is an advantage. If you want to fix a pattern, you have to fix every query that has that pattern, because you had to copy/paste the CTE everywhere.
This reminds me of a recent conversation with another DBA about triggers. He says all kinds of bad things about them from experience. But I had to point out to him that the triggers themselves were not the problem, rather the methods used and lack of documentation were the true causes of his issues.
Agree. One of our vendor’s views has several CTEs plus joins on a couple of dozen additional tables. It takes several minutes to query it. I drastically cut the time down by creating a version of the view that only queries the tables and columns that are actually relevant to us.
They have several views too that join two tables, one to get a single column that isn’t even used anywhere in their code. Why? I have no idea as it’s a waste of space within the database and processing time (even if it’s minor, you’re still running an unnecessary join on a table that has no purpose).
As I say “No database administrators were consulted in the creation of this code.” Where’s my crying towel?
As a vendor, I can feel your pain. Some of our COTS views are built to accomodate wildly vastly different business needs from customer a to customer b, etc.. I have a feeling this might be the background behind that “single column that isn’t even used anywhere”. Its probably used somewhere but just not by your installation. that said, I WELCOME smart insights such as yours from our customers and we tend to come in and either adopt your version of the view and point to it or take out the useless references in our view.
So I agree with you, this does happen but the vendor may* have a reason. *Or maybe that vendor truly has no clue (I’m not defending them at all) but I thought you might want some perspective from a vendor why there can be “bloat” such as this. COTS products tend to cater to many industries as we do at my shop and we try and cull the bloat post sale by working with our customers.
Well unfortunately they don’t employ anyone with an actual database background and have no DBAs or database developers on staff. When users complain about timeouts and slowness of the application, the vendor is quick to jump on us asking about our hardware and network setup as opposed to their crappy code. Multiple tables without primary keys or any kind of indexes or (thanks to Brent’s knowledge sharing and sp_BlitzIndex!) improperly defined indexes.
I don’t even want to get started on when they send me hotfixes that don’t even compile.
They aren’t used to having customers with IT teams let alone a DBA as they are accustomed to very small clients so..they’re also not used to anyone at a client querying their database directly (and finding the cockroaches). They ignore me when I point out the bugs in their code. In many cases, I just fix it on our end as it’s faster than expecting them to one day get around to it.
Ok, so simple views perform roughly the same as the straight query. So, what value is the view? If I want to create a semantic abstraction from the underlying data there are other ways to do that. Seems like I’m cluttering up my database with unnecessary objects. I’m just curious what your opinion is on this topic, do you commonly use views and if so, what is the value add? In 30 plus years in the biz I’ve never really found a use case for them. p.s. Not trying to start a holy war, just trying to learn. I don’t mind being wrong.
Some of us don’t like writing the same exact queries (table joins, where clauses, etc.) multiple times.
When I want to reuse a query, I wrap it in a stored procedure rather than a view.
I feel the same way – would rather an SP be used. I have only a few applications where a view was employed where it was truly recycling almost all the columns feeding a bunch of screens or reports almost the same columns each time but using very different logic against them.
I have a few main frame application data feeds where the columns all use short names, and in those I’ll usually set up a view that only makes the columns more human readable, but that is only for my benefit so I don’t have to figure out what the hell something like AABKPHTI is.
One of my uses of views were ironically to make a view that I had flattened out into two tables, one containing active records, one inactive, look like one table again. That wouldn’t have been necessary had the view I needed to flatten out wasn’t so horrible and not possible to query more than a few thousand records at a time from without getting a query stuck
I recently had experience with a 3rd party application that had many levels of views and had performance issues. I found the many levels of views made it more difficult to isolate and fix the problem. You could see where the plan went bad, but it was difficult to tie that back to the actual view that needed to be changed.
Interestingly, I noticed a big difference in execution time when running from SSMS with no plan and running with an actual execution plan. For one sproc, execution went from 11 seconds to 1 minute 3 seconds and the XML generated was 70 MB! I don’t think the nested views were the cause of this, but the SQL was just too complex. They went overboard with the layers of views and it was more difficult to debug.
Todd – yep, we agree: the views aren’t the real problem: the code is. If you want to write bad code, you can nest it in any number of ways, like functions and stored procedures.
I think maintenance (this includes debugging) is really the primary reason not to nest views. Nested views tend to be associated with poor performance because they’re more of a design antipattern for a lot of systems, so most of the time you see them they’re just a symptom of an overly-complex system, which is likely to have the poor code that causes the perfomance issues. Debugging can indeed be hell, much the same way as trying to figure out who has permission to do what when you’ve got nested AD groups and database roles.
I’ll save Brent the trouble of executing his Sommarskog subroutine and advise you to do a web search for “Slow in the Application, Fast in SSMS” 🙂
That’s it exactly. Technically there is nothing wrong with creating a nested view. The issue is that once you start doing that it tends to spiral out of control until people have layers of views stacked that ultimately join 50+ different tables together just to retrieve a couple of columns and are causing the optimizer to time out before it can compile an optimal plan.
Yup. I have a view from hell that has nesting at least 5 layers deep, that joins at least 3 tables, joins on views, joins on an nvarchar(max) and its so complex that SQL will spend about 5-10 seconds trying to optimize it, then give up realized that the query plan won’t fit in the XML space that query plans are provided. And its just this horrendous thing that has been appended to over and over for the last 10-15 years that no one understands, and someone is only occasionally brave enough to try and do anything to, which inevitably ends in tears.
30*
Don’t you have UNIONs? You gotta put some UNIONs in place, to account for duplicates, to get some nice WorkTables and to rend those indexes ineffective. Some UDFs would be nice too.
lol Oh yeah there are unions in it. One of the functions it joins on has a union that splits a table in half, turns it into one, and then there was some aggregate keyword in it that I had never heard of before, that was similar to group by but was different. I am wanting to say that it was something along the lines of aggregating on windowed results of a single statement
We use views a lot and we are guilty of some of these horrors but thanks to Ozar training, we are culling out the horrors and making our views run faster. Thanks Brent.
Woohoo!
Views can provide a nice abstraction layer (similar to how an Interface is used in OO languages).
If your code can consistently use a vanilla view (CREATE VIEW SomeInterface AS SELECT * FROM tblSomeTable), then we find that can help hugely when migrating from tblSomeTable to a tblSomeTableBetterStructured, as you can use just switch use via the view (CREATE OR ALTER VIEW SomeInterface AS SELECT * FROM tblSomeTableBetterStructured).
The problem with views, is how they end up metastasizing, or get mistaken for an ETL solution. They are just too easy to repurpose, and when a senior level analyst decides to start using a view for something other than it was intended, then they decide they need something a little bit different and create another view pointing at that view, which then get’s recycled into something else, and then again and again.
Its a lot easier for me to say that they are slow and crappy to get them to not use them inappropriately than trying to explain their queries suck, or teach an excel power user with a masters degree and a semester of Python classes how to write SQL at a university of youtube level of proficiency. Their stored procedures will end up sucking as well, but at least that remains their problem and doesn’t spread to anyone else then become my problem. Or the developer’s problem when they need make a data change that causes all hell to break loose in some ridiculous function nested in a few that can’t handle nulls or an implicit type conversion.
I absolutely agree that it’s the code that sucks and not the fact that the code is in views. But it’s just like telling a kid not to throw up on the floor and then they come running into the bedroom and puke on you and the bed. It’s easier to just tell people to only puke in the toilet… with the lid up… and not to fall in… and…
So most DBAs simply state, “Avoid views” and “Don’t use nested views”.
Me? I simplify it a lot… “OK… you’re not allow to touch computers at work anymore”. 😀
I agree with you completely. I found views are a very good way to simplify code because they lock and the definition of a set in one place, one way and one time. More years ago that I like to think about, I had to do an employee scheduling database for a friend. The personnel had to be female, 18 years of age or over, have a liquor serving permit and a ranking by seniority. This was then passed to another query that would do the scheduling by shift. When the drinking age went to 21, it was simple to just change the view of sending underage strippers to work the floor on a given shift. .
I love this post. I used to have endless battles with my OCD boss, who insists on everything being done in SP’s. SP’s are great for high execution stuff supporting apps, but I never saw anything wrong using a view to simplify content for reports. IMO the reusability reduced the likelihood of similar reports providing inconsistent results.
Thanks, glad you liked it!
about the only time i nest views is if the inner one is able to be schemabound and indexed. which works great until someone tweaks the view (which silently drops the index) and forgets to re-apply the index
Heh… I guess all of this boils down to “your point of view”… Pun massively intended. 😀
Brent Ozar – Myth Buster. I don’t know how many times I been told that the problem is the view only to do exactly what you did here. Even then developers think I’m doing some sort of black magic despite the fact that it’s their own code. Developers – Can’t live with ’em, can’t shoot ’em.
Like plenty of other people on here, the main issues I’ve had with views over the years are when you have several layers of nested views, with unhelpful names and little to no documentation. The issue usually isn’t performance though, it’s readability when some poor sap needs to debug or change an overarching process and the original developer is long gone.
When they’re used right though, they are great ways to simplify your code. A big part of the battle is making sure they’re well named and documented somewhere, so it’s obvious what the code is doing at each layer. And it doesn’t hurt to add comments in the calling code to explain what the view is doing if you think it’s in any way unclear.
I blogged some of my thoughts a couple of years ago
There´s also a query to detect nested views – hope it might be helpful
http://www.sqlservice.se/how-to-query-metadata-to-discover-nested-views/