View is taking so much time for fetching data

60 pts.
Tags:
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.

Answer Wiki

Thanks. We'll let you know when a new response is added.

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.

Discuss This Question: 4  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • Kccrosser
    (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.
    3,830 pointsBadges:
    report
  • Kccrosser
    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.
    3,830 pointsBadges:
    report
  • carlosdl
    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...
    69,160 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following