September 20, 2011 at 9:50 am
I know that Nested Views are evil ... just had a nasty bout with one killing performance and know all the pitfalls to using them.
However, from what I've read, their major downfall appears to be situations where you have one view that references tables A, B and C and then another view that references C, D and E and then you join them together in view X and both views referencing C is where you will probably hit the snag ... or if the view has no need for any data other than A and E and then it just bloats for no good reason. Basically, the more data coming into the underlying views that is superfluous to what view X needs.
All that said, I have the following scenario and am wondering if the use of nested views would be acceptable given the way I plan to use them.
We have an application that is quite old and has gone through several developers (and the developers pretty much double as DB architects) so the Table structure is a culmination of a bunch of different styles. Long term, I'd like to clean that up but in order to do that seamlessly I'd like to reduce the direct dependencies on those tables.
The one way I thought would work best would be just to create a view for each table and then get everything pointing to that that view and then when I restructure the tables I'd just have to change that one view and all references would instantly fixed. To hopefully avoid any problems with nesting I was going to limit the views to only referencing the original table and nothing else.
Would that work or would I still get performance hits? The only way I could see performance hits in this setup would be if the simple fact of going through a view means that certain things like indexes become inaccessible or something to that effect.
Also, if something like this would work then would it also be feasible to include helper tables in those views as well. Say you have an Employees table and there's EMP_TYPE column that ties to an EMPLOYEETYPES table and that is the only time that EMPLOYEETYPES is ever used (so you can be assured that no other view would be built referencing it for any reason) ... would adding that one helper table potentially cause issues that having a single-table view would not?
TIA.
September 20, 2011 at 2:48 pm
I've done something similar to this before, and I didn't see much of a performance impact.. however, the views were pretty simple. I would give it a shot, but do make sure your tables are well-indexed, especially on columns referenced in the WHERE.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply