Improve DB Performance

[g1m0v0n5]

Hai,

I am maintaining one web application which is done in Java & Oracle 10g & Jasper reports(Reporting tool for Java).

We will be generating Monthly report or Daily report or Report between two Dates (Ex. Invoice Statements)
I am sending query to database which contains 4 sub queries and collecting data from at least 4 tables.
The time taking for query processing and showing report is almost 3 to 5 minutes.
Still I want to reduce the time in terms of seconds.
Here where the Database caching concepts will be implemented.

How to go-a-head to fix up this issue?
Can I use Stored Procedures for this?
If you know about Database Caching in Java & Oracle please guide me to implement.

I have searched a lot in Google, but I feel I didn't get any good article or code.
Thanks in Advance.

Manivannan

[dlorde]

Have you profiled your application to establish exactly where the time is being taken up? If not, what makes you so sure database caching will solve the problem?

Premature optimization is the root of all evil in programming...
C.A.R. Hoare

[nuzzle]

How to go-a-head to fix up this issue?


If you've established that the database queries are the performance bottleneck you need to analyze the queries carefully. A common problem is that deeply nested table searches are sequential. To avoid it you either reformulate your queries or introduce proper table indexing.