View is taking so much time for fetching data
i have created multilevel view (ie:- a view is inheritng data from multipe view)
and its taking so much time while fetching the data.
can i reduce time taking for fetching data.
Software/Hardware used:
Software/Hardware used:
ASKED:
April 16, 2009 9:05 AM
UPDATED:
April 16, 2009 7:06 PM
Answer Wiki:
More information is needed to give a good answer to this question.
There could be many factors affecting the response time, and different actions could be necessary depending on the database you are using, which you didn't specify.
One common reason for slow queries is the lack of appropriate indexes, so I would recommend to start looking at the execution plan for the query and identify possible reasons for the slowness, such as full table scans.
If you need further help, please provide more details, and post the query if possible.
To see all answers submitted to the Answer Wiki: View Answer History.
In general, cascading views will have performance problems, since by default views are not indexed. Therefore, a view that joins two or more other views will be *usually* doing non-indexed joins on the underlying data, resulting in poor performance.
In SQL Server, you can create “indexed views”, which can solve the performance issues, but there are a number of constraints on what exactly is allowed in an indexed view. This article is a very good discussion (with examples) of how to use SQL Server indexed views:
http://www.sqlteam.com/article/indexed-views-in-sql-server-2000
If you are using Oracle, Oracle uses “materialized views”. See Don Burleson’s very good article on these for tips, and then use the Oracle references:
http://www.dba-oracle.com/art_9i_mv.htm Oracle Materialized View tips
Note that in order for either of these to be effective, you really need to use these for the underlying views – making the outer view an indexed/materialized view won’t help in how long it takes to build the view unless the underlying views themselves are indexed/materialized.
(Reposting to try to fix formatting link problems.)
In general, cascading views will have performance problems, since by default views are not indexed. Therefore, a view that joins two or more other views will be *usually* doing non-indexed joins on the underlying data, resulting in poor performance.
In SQL Server, you can create “indexed views”, which can solve the performance issues, but there are a number of constraints on what exactly is allowed in an indexed view. This article is a very good discussion (with examples) of how to use SQL Server indexed views:
Burleson article on Materialized views
If you are using Oracle, Oracle uses “materialized views”. See Don Burleson’s very good article on these for tips, and then use the Oracle references:
Burleson article on Materialized views
Note that in order for either of these to be effective, you really need to use these for the underlying views – making the outer view an indexed/materialized view won’t help in how long it takes to build the view unless the underlying views themselves are indexed/materialized.
Arrgghh.. Ok – fixed formatting, but copy & paste left wrong link title above. The first link is really to the SQL Server indexed views information, while the second is to Burleson’s Oracle materialized view info.
Great points, Kccrosser.
However, if no indexed or materialized views can be used, I think performance could be improved if the underlying views access well-indexed tables.
Btw, you are not the only one having problems when posting in the discuss section…