Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ramoneguru
Yak Posting Veteran
69 Posts |
 Posted - 2007-01-24 : 14:39:46
|
| Is it good sql programming to base a view off of another view or even create one view based on multiple other views? Sometimes it seems like the only solution (or rather the fastest to get the product out/tested).If there are alternatives I would be most interested, thanks.--Nick |
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-24 : 14:45:08
|
| It's not an entirely bad idea, but it can get complicated and if you go to too many levels then you will start to get performance problems.One of the new alternatives in SQL Server 2005 is Common Table Expressions (CTEs), they make a lot of cases where you would have used views on views in the past unnecessary. Another alternative is to use subqueries as derived tables. |
 |
|
|
ramoneguru
Yak Posting Veteran
69 Posts |
Posted - 2007-01-24 : 14:57:18
|
| So a subquery will be less of a performance hit than a view of a view? Oh and I'll look into that CTE stuff.--Nick |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-01-24 : 15:14:40
|
| We can't answer that without testing both. So we would need to see code for both.Tara Kizer |
|
|
|
ramoneguru
Yak Posting Veteran
69 Posts |
Posted - 2007-01-24 : 17:07:20
|
| Oh yeah, this was more of a general programming question.......I didn't really have any specific examples. I've been using views of views and it just didn't feel right ahahha.--Nick |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2007-01-24 : 22:58:39
|
| Nothing wrong with views of views provided they do what you think they do and don't get changed from under you (but that's just standard development issues). There is always a danger that a view is doing more that you intend, and that (if I may correct snSQL) is where your "performance problems" come from, not from having "too many levels". Basically you need to do what you need to do in order to get the answer to your question. There is nothing inherently slow in a view - the parser just turns it into the equivalent query. If you need to traverse 26 tables to get your data, then so be it. Assuming you fully understand your data & structure then no amount of twiddling with the SQL will magically get you to where you need to be using fewer joins (which is what I think he meant). |
|
|
|
snSQL
Master Smack Fu Yak Hacker
1837 Posts |
Posted - 2007-01-24 : 23:42:31
|
| Correct - I wasn't saying lots of levels always means bad performance, just that once you get lots of levels its much more difficult to keep track of everything and find performance problems when they do occur. |
|
|
|
|
|
|
A View of a View......