| View [message #374088] |
Thu, 24 May 2001 09:57  |
Sue
Messages: 49
Registered: May 2000
|
Member |
|
|
Hi,
I have a view which takes a for ever to retreive the values .It is selecting the data from 10 differnet tables.How do I increase the speed of it .Any Idea?
Thanks,
Sue
|
|
|
|
| Re: View [message #374099 is a reply to message #374088] |
Thu, 24 May 2001 12:57  |
Bala
Messages: 205
Registered: November 1999
|
Senior Member |
|
|
Hi,
If you are in datawarehousing environment,
then you should have a look at materialized views.
The actual data itself will be stored in the mat views, at regualer intervels the view will get refreshed.
So no need to say they are multiple times faster then traditional views.
Bala.
|
|
|
|
| Re: View [message #374123 is a reply to message #374088] |
Fri, 25 May 2001 12:34 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
Often a view is only as efficient as the underlying sql. If your query runs fast, but the view is slow, then you can look further. If not, it's juat the SQL. Joining 10 tables in one query is never going to be fast. You might try re-coding the SQL to use sub queries etc. Make sure the tables are analyzed. Make sure the optimizer goal is NOT first_rows (this can mislead you).
Also, where clause you allpy to the view is usually merged with the underlying SQL from the view to result in an efficient statement. If you view has group functions etc, your additional where clause can only be applied to the resulting data returned from the view. This means that you dont end up with an efficient statement.
|
|
|
|
| Re: View [message #374159 is a reply to message #374088] |
Mon, 28 May 2001 17:08 |
Sudhakar Atmakuru
Messages: 58
Registered: May 2001
|
Member |
|
|
I could only suggest to check the referencial integrity that has right primary/foreign key relation between those 10 tables and make sure that WHERE condition first filters all the rows of related data (having matched primary/foreign key) and then goes ahead with other AND/OR conditions.
When it joins or merges the data of several tables, it should take only the related data, so it quickens the data query to find the required rows of information from those filtered data rather than the whole tables.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
