View is taking so much time for fetching data
Home > IT Answers > Database > View is taking so much time for fetching data
440638
45 pts.
0
Q:
View is taking so much time for fetching data
Database Views, Query optimization
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.
ASKED: Apr 16 2009  9:05 AM GMT
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _
RELATED QUESTIONS
0
Carlosdl
29845 pts.
0
A:
 RATE THIS ANSWER
0
Click to Vote:
  •   0
  •  0
  • AddThis Social Bookmark Button
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.
Last Answered: Apr 16 2009  1:53 PM GMT by Carlosdl   29845 pts.
  •   
0
0
RSS - Discussions Help
Discuss This Answer:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _



_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

Kccrosser   1850 pts.  |   Apr 16 2009  6:28PM GMT

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:

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:

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.

 

Kccrosser   1850 pts.  |   Apr 16 2009  6:32PM GMT

(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.

 

Kccrosser   1850 pts.  |   Apr 16 2009  6:34PM GMT

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.

 

Carlosdl   29845 pts.  |   Apr 16 2009  7:06PM GMT

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…

 
0