File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Performance Issue Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Performance Issue" Watch "Performance Issue" New topic

Performance Issue


Joined: Feb 20, 2003
Posts: 5
am making call Oracle Function on remote database .
The values are taken in result set.The numbers of records in result set are aroung 6000.
when I do
//My logic
This takes aroung 5 min(300 sec) to get completed
Now I commented all my logic inside while loop of
then also its taking around 4 min (250 sec) to just loop through records.
Is there any way I can minize my time to just loop through records.
I am using oracle thin driver to make connection.
Jon Strayer
Ranch Hand

Joined: Dec 04, 2002
Posts: 133
I just ran a test using DbVisualizer (JDBC based) on an Oracle database using the Oracle thin drivers and it took just under a minute to fetch 6000 rows from a database that in another state.
How big are your rows? Mine are about 350 bytes each. If your's are larger, and you don't need all the columns, the maybe you can change the query to not return as much.
You can also try setting fetchSize on the results set to 100 or 1000 to see if that helps.

Lu Battist
Ranch Hand

Joined: Feb 17, 2003
Posts: 104
Make sure the sql you run can take advantage of table's indexes and make sure the right indexes have been defined.
Another thing to try if possible is to move the java program as close (network-wise) as possible to the database, preferably on the same machine.
A good test to see if the sql that's the problem or the java program logic is to run the command via a different tool like powerbuilder or dbartisan and take note of the time.
I agree. Here's the link:
subject: Performance Issue
It's not a secret anymore!