Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Performance Issue

 
Vikas
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
while(rs.next())
{
//My logic
}
This takes aroung 5 min(300 sec) to get completed
Now I commented all my logic inside while loop of rs.next()
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
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 104
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic