December 19, 2011 at 3:36 pm
OK, I've been working as a database administrator at a new company for six months now. So far, every single performance problem I have run into has finally come down to some hideous view. I have heard that some DBA's simply forbid views in new development, and I am starting to understand why.
This leads me to my question for today. When is a VIEW appropriate? When is a VIEW the superior solution? Do you use VIEWs or not, and why (or why not)? If you do use VIEWs, how do you prevent them from mutating out of control, like some horrible cancer of the database?
I don't really want to provoke a religious debate. I just want to hear everyone's opinion on the matter.
December 19, 2011 at 3:39 pm
David Moutray (12/19/2011)
OK, I've been working as a database administrator at a new company for six months now. So far, every single performance problem I have run into has finally come down to some hideous view. I have heard that some DBA's simply forbid views in new development, and I am starting to understand why.This leads me to my question for today. When is a VIEW appropriate? When is a VIEW the superior solution? Do you use VIEWs or not, and why (or why not)? If you do use VIEWs, how do you prevent them from mutating out of control, like some horrible cancer of the database?
I don't really want to provoke a religious debate. I just want to hear everyone's opinion on the matter.
I don't like to use massive nested views. That seems to be most frequently when performance issues are encountered (with regards to views).
Views can be very useful. One nice use is an indexed view. Also, using views to when you have to perform the same piece of code many times - can be good.
It really boils down to getting a good feel for how it will be used, controlling how it will be used and testing it for performance. The dba should be able to help control those things.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 19, 2011 at 3:48 pm
SQLRNNR (12/19/2011)
Views can be very useful. One nice use is an indexed view. Also, using views to when you have to perform the same piece of code many times - can be good.It really boils down to getting a good feel for how it will be used, controlling how it will be used and testing it for performance. The dba should be able to help control those things.
Control is one issue. How do you maintain control, unless you personally approve every single line of T-SQL code that goes into production?
Also, using views to implement encapsulation seems like a pipe dream to me. Once a view has been deployed into production, it will be very, very difficult to change, because you will have to track down all possible impact that your change might have.
A good object, in object-oriented code, does one thing and does it well. It seems to me that SQL does not encapsulate well, because a single statement does many things.
You can write a SQL statement that only does one thing, but if all of your code is written like this, the Query Optimizer isn't going to optimize it very well.
Thoughts? Objections?
December 19, 2011 at 5:13 pm
Forbidding views doesn't really help anything in my opinion. Simply taking tools out of the toolbox doesn't prevent anyone from mishandling the tools that are still available. Like just about every other tool in SQL Server, views are fantastic when used correctly and they have the potential to be terrible when used incorrectly.
Control is a difficult subject. In my work history the best success I've had is to create a parallel architecture to prove the advantages of doing things correctly. If there is a big enough difference between the two that can be demonstrated, the powers that be will determine that there needs to be a higher level of control to prevent garbage architecture and code from corrupting the database. And if they see it and still don't really care, then maybe you are in the wrong place.
December 19, 2011 at 5:20 pm
bteraberry (12/19/2011)
Forbidding views doesn't really help anything in my opinion. Simply taking tools out of the toolbox doesn't prevent anyone from mishandling the tools that are still available. Like just about every other tool in SQL Server, views are fantastic when used correctly and they have the potential to be terrible when used incorrectly.
Fair enough, but what is the proper use of VIEWs? It would really help me if you could give me an example of a use case where VIEWs are actually superior to the alternatives.
Thank you! 🙂
December 19, 2011 at 9:12 pm
David Moutray (12/19/2011)
bteraberry (12/19/2011)
Forbidding views doesn't really help anything in my opinion. Simply taking tools out of the toolbox doesn't prevent anyone from mishandling the tools that are still available. Like just about every other tool in SQL Server, views are fantastic when used correctly and they have the potential to be terrible when used incorrectly.Fair enough, but what is the proper use of VIEWs? It would really help me if you could give me an example of a use case where VIEWs are actually superior to the alternatives.
Thank you! 🙂
There are a couple of proper uses, IMHO...
1. Control. If a system has been reasonably well locked down for privs, views are exceptional for controlling which columns of data from common tables that different user groups can see.
2. Easing development by denormalizing data. That's "geekineese" for doing such things as creating a view to, say, join an InvoiceHeader table together with the InvoiceDetail table as would so often be done in code.
As a side bar, I'm with you and understand the problems with people creating views. About 90% of the time, they create views with aggregates in them and then try to do really stupid things like build query predicates based on the aggregated columns which causes the whole bloody view to "materialize" before it will return anything. To add to that and generally speaking, if you do a SELECT TOP 10 * from a view and it doesn't produce an instantaneous return, there's a pretty good chance the view is very, very poorly written. I had such a case where SELECT TOP 10 * on a view at work took {drum roll, please} more than an hour to return. In this particular case, it was a view of a view with functions and aggregates in both and the criteria of the first view read the aggregate columns from the second as a condition of what to return. I've found that things like that are the norm when non-qualified developers write their own views. Most of them don't understand the concept of "Divide'n'Conquer".
--Jeff Moden
Change is inevitable... Change for the better is not.
December 19, 2011 at 9:22 pm
The other issues with views of views of views of views, is that you often access the same objects multiple times.
Now the optimizer is EXTREMELY smart. If it can it'll remove redundant or useless joins because you don't need those columns or the tables entirely. But there's a point where it just cannot simplify the view and it flat out gives up on trying to get a better plan (time out warning in reason for early terminsaison (really should be treated as an error)). This is usually where you get screwed (aside from Jeff's point).
I don't remember the exact figures but I remember Gail talking about a case where just the compile time of a view like that was in the minutes. Yes MINUTES to compile the effing query.
You haven't started running it yet!
On the other hand I have a very complex report (business requirement-wise and query wise) which takes in an absurd 17-20 parameters to do filtering and all kinds of other weird stuff.
3000+ lines of compact code, 20+ queries and the thing runs under 1 second for 1 month of data (nothing preagregated).
You got to help the optimizer do his job and you'll never have any issues (never-ish => sounds better than it depends :-D).
December 19, 2011 at 9:42 pm
I'd like to post the view I just spent a week decomposing. It is several thousand lines long. However, I think my company might object, and I just can't take the immense amount of time it would require to obfuscate the thing. It is, however, an amazing thing to behold. It is far from the largest one I have to deal with, either.
If I were the optimizer and someone submitted this query to me, I would take about 0.1 seconds to return the result, "You have got to be *&(^*& kidding me." I am amazed it runs at all. I am amazed the optimizer was able to create a plan for it at all - let alone an optimal plan.
My observation is that views are never refactored. You would have to test each of the hundreds of places the view is used - assuming you could even find them all. However, views are constantly added to. They constantly accrue additional columns and conditions which have to be applied to the entire result set, because no one has the time to decompose the thing. (That is why I compare them to a cancer.)
Worse, we have no naming convention for views. I am constantly running across a seemingly simple table reference in a stored procedure which turns out to be - surprise, surprise - another hideous view.
I like the idea of using views to control data access, though. That is very elegant.
December 19, 2011 at 9:57 pm
Wow - > 1000 lines in a view. That sounds like Microsoft CRM. In many cases we stopped accessing the views because they were so large (line count) and terribly ill-performing that we had to refactor other code to stop using them.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 19, 2011 at 11:12 pm
Jeff Moden (12/19/2011)
There are a couple of proper uses, IMHO...1. Control. If a system has been reasonably well locked down for privs, views are exceptional for controlling which columns of data from common tables that different user groups can see.
2. Easing development by denormalizing data. That's "geekineese" for doing such things as creating a view to, say, join an InvoiceHeader table together with the InvoiceDetail table as would so often be done in code.
...
In addition to what Jeff said, I believe Views are not DEV team’s tool. They should be used by Database Designer / Data Modeller considering above points. The views lose its objective when DEV team start using it in replacement to a procedure or a function (up to some extent).
IMO for an application if a view is required it should appear in early design stages (subject to few exceptions).
December 21, 2011 at 7:11 am
When is a view appropriate? Here are a couple of my candidates:
1) You want to grant power users access to a boatload of data they need day-in, day-out. The user-readable data resides in many foreign tables with DRI. Think state names, country names, customer type, etc.
2) Short-duration projects. Your marketing team is evaluating a subset of customers based on a combination of criteria, such as last-order-date, region, size, industry sector, etc. Once they have this subset of customers, they want to run lots of queries about them (sales history, specific items purchased, sales vs. time-of-year, etc.). Once you lock down the rules in the VIEW that returns who you're evaluating, there's a lot less ambiguity about whatever results you turn up.
I don't have the dread of VIEWs that many have, probably b/c I've been lucky not to have senseless, redundant, over-large, or recursive views cluttering up my databases. Yet.
Rich
December 21, 2011 at 7:51 am
I never said I dreaded them, but then again I don't work with a team of devs, so the "bad" uses just don't happen here.
December 21, 2011 at 8:57 am
Another valid use of Views is refactoring. Let's say you discover through use that a particular table should have been normalized further than originally conceived, due to changing business needs. You build a view that has the same name and colums as the old table, and you build the more-normalized tables with new names. Legacy code looks for the table, finds the view, and treats it as the old table. New code takes advantage of the new tables. Best of both worlds without breaking anything (if you do it right).
Of course, that ends up with odd database artifacts. I have a few tables named "dbo.vwMyTable" and a few views named "dbo.tblMyView", because of incorrect uses of object-naming prefixes. (Both would more correctly be prefixed "data", to indicate function [data store] rather than type.)
I've also had a few cases where dozens of stored procedures all needed the same three columns from the same two tables, and that kind of thing. Rather than writing the same exact From Join syntax dozens of times, a simple View makes it easier to write. Intelisense largely makes that a trivial use, but it does come in handy. The problem with it is massive over-use. Lazy devs will often define a "one view to rule them and in the darkness bind them" type of view that joins every table in the database, including all columns, and then build every piece of code in the application to access that view, and thus will kill plan re-use, performance, et al. It's definitely a "good in moderation, horrific in excess" kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 21, 2011 at 10:06 am
GSquared (12/21/2011)
I've also had a few cases where dozens of stored procedures all needed the same three columns from the same two tables, and that kind of thing. Rather than writing the same exact From Join syntax dozens of times, a simple View makes it easier to write. Intelisense largely makes that a trivial use, but it does come in handy. The problem with it is massive over-use. Lazy devs will often define a "one view to rule them and in the darkness bind them" type of view that joins every table in the database, including all columns, and then build every piece of code in the application to access that view, and thus will kill plan re-use, performance, et al. It's definitely a "good in moderation, horrific in excess" kind of thing.
This is a good point. My only objection is this. I have a personal principle that I try to follow when writing code: the code should be as easy to read and understand as possible. I believe this is important for two reasons: 1. Code will be read much more often than it is written, and 2. It is actually easier to write code than it is to read it. I didn't make this up. I read it in Steve McConnell's book, "Code Complete" and Joel Spolsky's blog on software development.
I particularly dislike those little table aliases (i.e. "a", "b", "c", etc.) because I constantly forget which alias stands for what table in a given query. Maybe I am just a little slow in this particular area, but I usually have to re-write someone else's query before I can understand what they were trying to do. Many developers seem to write code as if the compiler is charging by the character. (I know some DBA's that write code that way as well.) This seems to me to be a form of laziness.
My issue with VIEWs in this regard is that they often obscure understanding. SELECT * FROM [SomethingOrOther] looks awfully simple until you realize that [SomethingOrOther] is some hideous uber-VIEW. In my opinion, many developers like views because they make code easy to write. As the poor schmuck who has to maintain this code, I would greatly prefer that code be easy to read.
Thoughts? Objections? Personal Insults? 🙂
December 21, 2011 at 11:33 am
David Moutray (12/21/2011)
GSquared (12/21/2011)
I've also had a few cases where dozens of stored procedures all needed the same three columns from the same two tables, and that kind of thing. Rather than writing the same exact From Join syntax dozens of times, a simple View makes it easier to write. Intelisense largely makes that a trivial use, but it does come in handy. The problem with it is massive over-use. Lazy devs will often define a "one view to rule them and in the darkness bind them" type of view that joins every table in the database, including all columns, and then build every piece of code in the application to access that view, and thus will kill plan re-use, performance, et al. It's definitely a "good in moderation, horrific in excess" kind of thing.This is a good point. My only objection is this. I have a personal principle that I try to follow when writing code: the code should be as easy to read and understand as possible. I believe this is important for two reasons: 1. Code will be read much more often than it is written, and 2. It is actually easier to write code than it is to read it. I didn't make this up. I read it in Steve McConnell's book, "Code Complete" and Joel Spolsky's blog on software development.
I particularly dislike those little table aliases (i.e. "a", "b", "c", etc.) because I constantly forget which alias stands for what table in a given query. Maybe I am just a little slow in this particular area, but I usually have to re-write someone else's query before I can understand what they were trying to do. Many developers seem to write code as if the compiler is charging by the character. (I know some DBA's that write code that way as well.) This seems to me to be a form of laziness.
My issue with VIEWs in this regard is that they often obscure understanding. SELECT * FROM [SomethingOrOther] looks awfully simple until you realize that [SomethingOrOther] is some hideous uber-VIEW. In my opinion, many developers like views because they make code easy to write. As the poor schmuck who has to maintain this code, I would greatly prefer that code be easy to read.
Thoughts? Objections? Personal Insults? 🙂
On the "a", "b", "c" thing, I completely agree. I hate that practice! Makes debugging/refactoring that much harder, saves zero effort in writing the code in the first place, and I've seen it lead to things like unplanned cartesian joins when someone mixed up one for another.
I go back and forth on the point about views making code harder to read.
If you're familiar with the database and its code, then a commonly used view in a From clause can make reading, refactoring, et al, easier. You see "MyCommonView" in the From clause, you immediately know a lot about the query, if it's been used in dozens of places and is a narrow, efficient, simple view. That can be easier to read than five or six tables aliased and joined.
It's also easy, in a more object-dense query, to see those common tables, and have your eyes skip over some error in how they're joined. You see those tables joined, always in the same manner, in dozens of places. Then you run into the one query where the join criteria were accidentally mis-written, and your eyes may glance past it, in the same way you don't necessarily notice that your wife got a haircut, and with potentially comparable consequences. You see what you expect to see, not what's actually in front of you. But if, instead of multiple tables, there's a view that has all the join math for those tables, then it's either right everywhere or wrong everywhere, and the "notice anything different today" question doesn't have to be asked.
That's on the plus, or potentially plus, side.
On the minus side, I've had to fix many performance problems that were well hidden because they took place inside views that were poorly written. Look over a query, don't realize one of the objects in the From clause is a view, and think, "Huh? It looks pretty standard and simple. Why is it so slow?" Only to realize, when you begin looking at execution plans more closely, that some of the indexes being scanned/seeked aren't in ANY of the tables mentioned, and you finally find them burried in a view (or worse, a view nested n levels deep in other views).
I had one like that in one contract job last year. A page that should have been a very simple query against some medium-sized tables, was timing out after six minutes of the query not returning any data. The query looked very straightforward. The execution plan showed low-cost seeks and the expected nested loop joins on small, unordered datasets. Should have been very fast. I/O stats finally gave away the problem. A table not even mentioned in the From clause had something like 5-million scans, over something like 50-million logical reads, on a table with less than 1000 rows in it. A view nested in a view had a CTE (not even a recursive one) in it that was causing that. The dev and two other DBAs had all looked at it and thought it was fine, but they didn't understand a few of the deeper details of the query engine. Fixed that view, and the query went down to less than 10 milliseconds, from over 6 minutes.
Had the whole thing been written as a CTE or even a Cross Apply in the outer query, it would have saved a lot of time in debugging that query. And since it was in production and was slowing down a whole department, that time mattered.
(As an amusing aside, the manager I had on that contract terminated the contract over that incident. I had asked for some guidance on the scope of the project, and apparently asking for guidance was "not showing enough initiative". So, her comment when she term'ed the contract was, "I didn't like the way you handled the project, but [voice and face get excited] that was really amazing what you did with that query!" People think in strange patterns sometimes. Essentially, I got layed off for a project that produced amazing results.)
So, I go back and forth on views where they aren't actually necessary. Some good, some bad, and abuses are too easy in too many cases. Thus, I usually come down on the side of, "avoid them unless you really know exactly what you're doing". The usual, "Professional driver on a closed track. Do not try this at home." kind of thing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply