| Author |
Topic |
|
LonnieM
Starting Member
15 Posts |
 Posted - 2008-11-26 : 11:05:03
|
| Hello All,I am looking for some general advice concerning views vs. tables. I have several processes for which I have created multiple views. In other words, View 2 depends on View1 and so on. Some of these are nested 6-8 deep.I had some initial difficulty with unexpected join results (SQL choosing a different join than what I coded?) but eventually got around that issue. Now I am questioning my performance.Is this a poor practice (nested views)? Can nested views in fact alter the execution plan from what I specified? Should I be using tables? Should I be nesting my select statements into one or two large stored procedures?Any advice would be appreciated.Sincerely,Lonnie Meinke |
|
|
spirit1
Cybernetic Yak Master
11752 Posts |
Posted - 2008-11-26 : 11:12:29
|
| moved from script library.___________________________________________________________________________Causing trouble since 1980Blog: http://weblogs.sqlteam.com/mladenpSpeed up SSMS development: www.ssmstoolspack.com <- version 1.1 out! |
 |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-11-26 : 11:17:39
|
| Actually We use views for security purposes so that user don't have direct access to everything in tables. Well Performance depends on your index strategy and how efficiently are you applying to it. You should take advantage of indexed views if its possible. |
|
|
|
sakets_2000
Master Smack Fu Yak Hacker
1472 Posts |
Posted - 2008-11-26 : 11:24:55
|
| Its a poor practice if you did it because of your initial difficulty with joins. Its not easily readible at all. Although, the optimizer will choose the same plan to execute the query. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-11-26 : 11:25:03
|
| Don't forget:Unless you are using Enterprise Version, if you don't use noexpand hint the optimizer will look directly at base tables even if you index View. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-11-26 : 11:44:02
|
| Lonniem, I personally would think this would cause you a major headache with performance if the view's hold extremely complex select statements |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-11-26 : 12:34:31
|
| Nested views are a poor practice for both performance and administrative reasons.If it is not practically useful, then it is practically useless. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-28 : 09:18:47
|
| Views are not always poor on performance and in most cases they are the same as the query.For example, select or join to a view SELECT a,b,c from ORDERS where STATUS='ACTIVE' will have the same effect as a query that does the same.The main problems I've seen seem to be related to selecting from views that do more than a particular query needs and that can cause more processing than is required to get the result.It's not always bad but 6-8 seems excessive. Check the query plan against a hand-coded query though and see if it makes a difference. That's the only way you will know. |
|
|
|
NeilG
Aged Yak Warrior
530 Posts |
Posted - 2008-11-28 : 09:46:57
|
| LostinSpace, no one is saying that view's are bad but when you're creating view's to look at other view's that then look at other view's so on and so on then that not good design practice |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-29 : 08:27:27
|
| It's not bad design at all. As you know, a view is simply a query. If it makes sense to use that query then do it. It's similar to using subroutines in other forms of programming. You need to be aware of all the side effects/implementation details that's all.In my example, if you are after all active orders then there is no downside to using the view over a duplicated select statement however deep it is nested. Duplicating the SQL serves no purpose. If you need the data then you need the data. Putting it in a view makes no difference. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-11-29 : 23:37:36
|
quote:
Originally posted by LoztInSpace
Views are not always poor on performance and in most cases they are the same as the query.
I said NESTED views can hurt performance. And they do.If it is not practically useful, then it is practically useless. |
|
|
|
LoztInSpace
Aged Yak Warrior
940 Posts |
Posted - 2008-11-30 : 03:55:37
|
| Can you give an example? I'd be interested to see the differences and maybe try to work out why there are any.Thanks. |
|
|
|
darkdusky
Aged Yak Warrior
591 Posts |
Posted - 2008-12-01 : 05:41:25
|
| Nested views do not necessarily hurt performance. If noexpand hint is Not used (assuming not enterprise version): The optimiser will look directly at base tables - ignoring view - so in this case the view is not even a factor in execution. If the no expand hint is used:The view may be a much smaller set of data with its own index so in this case the View is considered and crucially - if the optimiser thinks using the View is faster it will use it. If the View is slower then it will not be used. |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2008-12-01 : 11:06:20
|
| Even the optimizer has limits.I personally ran across a situation where a database had views nested five or six layers deep, and the engine simply could not come up with a consistently efficient query plan. Condensing the views to a single sproc yielded a dramatic improvement in performance.Granted, this was SQLSVR 2000, but considering all the other valid reasons to avoid nested views, I haven't felt the need to test this out on 2005.If it is not practically useful, then it is practically useless. |
|
|
|
|
RE: Views vs. Tables