| Author |
Topic |
|
woogiee
Starting Member
3 Posts |
 Posted - 2007-05-29 : 14:16:33
|
| Using views inside of other views I am assuming is pretty bad practice(so far its a nightmare to troubleshoot!). I am trying to find a good answer as to why or why not? and whether or not it hinders performance? Any input would be helpful, as I am trying to give an argument to my boss as to why we need a db redesign!-Thanks |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-29 : 15:33:58
|
| How about this as an argument: "Its a nightmare to trouble-shoot". Can't remember where I heard that...People create multiple views thinking they are following the principle or reusable code, but what results is the database equivalent of spaghetti code. Think of each nested views as another link in a long chain of GOTO statements.e4 d5 xd5 Nf6 |
 |
|
|
woogiee
Starting Member
3 Posts |
Posted - 2007-05-29 : 15:58:54
|
| haha well said!I guess the main issue which will hopefully expedite the re-design process is performance. Does creating nested views cause a performance problem? |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-29 : 16:42:37
|
Yikes! Time for another debate ???using views within views is actually the OPPOSITE of spaghetti code and GOTO statements; it is more equivalent to breaking down your larger application into many smaller, reusable functions or classes.It all depends on what you do and how you use them. Many people never use views and they repeat the same SELECT's and JOIN's and groupings and criteria over and over and over and over in all of their stored procedures ... I like to define key things once in a view (for example, "ActiveCustomers" or "ApprovedOrders" or common joins like "OrdersInfo" that includes joins to multiple tables) and use those views to keep my stored procedures short, focused, consistent, and easy to read. Also, when using views consistently, you can make minor tweaks to your application tables and/or logic and, in many cases, nothing needs to change but a few views. For example, if Active customers are defined differently at some point -- perhaps you changed it from a bit column to a more flexible StatusCode. I am more than confident that Blindman will disagree and provide good evidence for his convictions, and even more confident that despite that he will never convince me that "views are bad" ......  - Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-29 : 16:45:16
|
| As for nightmare to troubleshoot -- I don't get it. Suppose you have a SELECT that uses 5 views. If you can confirm that each view works, then you immediately eliminate huge portions of that SELECT to worry about! Again, the analogy is using functions or classes -- if all my classes work individually, then I know the problem is in the code that calls those classes or uses the results of those classes.Just test your views one by one, be sure they are clearly defined, and if they work correctly after thorough testing, you never have to worry about it again! All you need to do is then focus on the SQL that *calls* those views to be sure that they are calling and using the results returned correctly. - Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-29 : 16:53:03
|
| I suppose you could make such an argument, though people who rely on shaving milliseconds to say one method is better that another are scraping the bottom of the barrel of justifications. Views are not precompiled, as stored procedures are, so the server will need to come up with a new query plan each time the nested views are executed (not STRICLY true, but again the devil is in the details).e4 d5 xd5 Nf6 |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-29 : 16:54:20
|
| Views are not bad.NESTED views are bad. I would never nest more than two levels.I had to dig through an application that had six levels of nested views once, and it was an absolute mess.e4 d5 xd5 Nf6 |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2007-05-29 : 17:01:13
|
quote:
Originally posted by blindman
Views are not bad.NESTED views are bad. I would never nest more than two levels.I had to dig through an application that had six levels of nested views once, and it was an absolute mess.e4 d5 xd5 Nf6
Funny, my "pro-View" analogy works both ways .. if you inherit an application with lots of nested classes and functions and you cannot be sure that ANY of them work (or there's no documentation), then indeed it would be a huge problem to have to investigate and test everything to find the bug! Views, like most anything, depend on how well things are documented and structured.Typically, if a select has 4 levels of derived tables all nested together, I'd prefer it was 4 different views since essentially that's how I'd test it anyway -- break each piece down and see how they work individually.Also, with views, you don't want to get too fancy -- they should be VERY lightweight and simple. - Jeffhttp://weblogs.sqlteam.com/JeffS |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-29 : 17:03:44
|
| I think the real answer is "it depends".If the views contain only the joins that are appropriate for your query, the performance should be no worse than writing the select statement. If the view has joins that are not appropriate for the query you are running, it can cause a performance problem. SQL Server resolves the total query at run time, so the query plan will be like you wrote joins and selected the columns you wanted. However, you need to be aware of exactly what the view does, and look at the query plan that results.I will say that views are something that I have often seen abused by lazy developers. However, I would also say this is true for almost everything lazy developers do. There is just no substitute for a developer knowing what they are doing and actually using that knowledge.CODO ERGO SUM |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
|
|
woogiee
Starting Member
3 Posts |
Posted - 2007-05-29 : 18:04:40
|
| thanks you guys make some good points. I guess I already knew the answer to these questions I just wanted a second opinion. I think using views to limit certain result sets is good. I think using nested views multiple times to basically filter your data in a very lazy way is bad(which is the case in my situation).Thanks for the input. |
|
|
|
|
View Performance Question
e4 d5 xd5 Nf6