Create a view from a view
-
There was a contractor used to work in my company. He created a view, then he created another 3 views using the based view. I thought it would be slow to run a view using a view.
I rewrote it using outer join in the select statement and I got rid of the base view.
Now my DBA asked me what's wrong with a view using another view ?
Did I waste my time and do something totally stupid?
I just want some commets.
-
Not always stupid.. I've heard of cases where a view using a view was slower than a single select statement, but unless you've proven that fact you probably wasted some time...
-
The views of views can be very disadvantageous when you perform a lot of operations that may no be needed on the higher level ones and sometimes you may learn the hard way that there is a ~250 limit in the number of LINKS
but if that is not the case, it may be a good way to simplify maintainability
hth
* Noel -
View is just a piece of code for retrieving data.
This code is compiled first time it's being used and is stored in database compiled until you change it.
Using view is the same as using subroutines in an application.
View in view is subroutine in subroutine.
Now you can open any book and read about advantages and disadvantages of using subroutines.
_____________
Code for TallyGenerator -
There's also one more 'esoteric' limitation when using views ... the concatenated columns of the views cannot exceed the usable page size (approx. 8092 bytes out of an 8192 - 8Kb page). This issue was more problematic when the page size was only 2 Kb.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
I've done some development where I materialize base views into tables... Generally, if the result sets are very large, or some of the base views distinct their results, this can really help performance.
If the data is not volatile, at all, you may want to create VIEWS with the WITh SCHEMABINDING option so that you can index them, and then when you use them in later views, they perform a lot better than before. But you need to be careful before you use this option.
Jonathan Starr
-
Indexed view is a table, in fact.
When you select data from indexed view you don't run the query because data is already prepared for selecting.
Query is being run every time when you update data in any table from the set used for the view.
If you have many tables in your view query and you frequently run insert/update/delete for those tables it may hit performance of these statements.
So you choose carefully what is more important for you - performance for select from the tables or performance for update the tables.
_____________
Code for TallyGenerator -
We all know why views are great. In fact, I built a reporting system based on views upon views. The chains got four or five levels deep.
I knew it was time to materialize a view, when the chain of view would NOT return data in a few seconds.
Several months ago I posted a problem in this forum that appeared to be a bug in SQL when in fact it was a slow performing query on this chained views. The system was telling me it was time to create a new table and continue working from these intermidiate tables.
I don't favor either approach, views or joins on a purely theoretical basis. Do what gets the job nicely and elegantly done, but don't forget about performance and maintainability. Also, dont' forget to consider functions and stored procedures. These should also be in your repertoir.
Ben
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply