Reduce the response time when querying the database
I am working on a plain java/J2ee project with Oracle 9 at the backend. The issue comes when I am querying the DB and more than 10000 records are retrieved. I can’t change the business logic much, since the code is developed by some other people and I’m not allowed to do so. Any tuning which we can do to overcome such issue?
Software/Hardware used:
java j2ee
Software/Hardware used:
java j2ee
ASKED:
June 11, 2010 8:06 PM
UPDATED:
June 16, 2010 12:23 AM
Answer Wiki:
Correctly setup the indexes to improve performance.
To see all answers submitted to the Answer Wiki: View Answer History.
This question was asked on LinkedIn: http://bit.ly/cmt2ne
The most common tuning is to add/configure the right indexes to speed up the retrieval.
Can you analyze the query and see where all the cost is occurring? Are there large tables where you see table scans? If so, then a good index (particularly a multi-column index) may speed up the result.
How big is the data per returned record? 100 characters (= 1 Megabyte)? 1000 characters (10 Megabytes)? 10,000 characters (100 Megabytes)? You could be running into limits on the actual time to pull that much data down to display it.
So – you need to determine:
1. Is the time being spent in the database selecting the records to return? If yes, then indexes can probably help.
2. Is the time being spent downloading the retrieved data to the client for display? If yes, then you need to constrain the size of the returned data.
3. Are your queries using “select *” instead of enumerating just the specific columns of interest? If so, then changing to enumerate the columns may help both of the above.