aspose file tools*
The moose likes JDBC and the fly likes Performance Issue - Constant Delay in ResultSet.next() Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Performance Issue - Constant Delay in ResultSet.next()" Watch "Performance Issue - Constant Delay in ResultSet.next()" New topic
Author

Performance Issue - Constant Delay in ResultSet.next()

James Gordon
Ranch Hand

Joined: Aug 09, 2002
Posts: 106
Hi,
<br><br>
My application server and database server are on different machines.
I executed a query which resulted in few thousands of rows.
Then, I would loop through the resultset and set it to my object
(code snippet as below):
<br><br>
while (resultset.next()) {
// print some log info - (1)
// create my object - (2)
// set resultset to object - (3)
}
<br><br>
I noticed that before entering the loop for the first time, there would
be a delay for about 10 seconds. And this would re-occur constantly after processing about 500 records. Meaning to process 4000 records,
the minimum time needed is 10 x (4000 / 500) = 80 seconds.
<br><br>
I tried executing the same query with a database client program. I tooks
less than 10 seconds to return all the rows to me.
<br><br>
1) Could someone please explain what is the ~10 seconds delay for with
the JDBC query ?
2) Is there anyway to improve the performance for the query ?
(I tried setting the fetchsize but my JDBC driver don't seem
to bother)
<br><br>
Please advice.
<br><br>
Thank you.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30749
    
156

James,
That delay is being caused by network traffic. It is probably returning the rows in groups of 500. Fetchsize is unlikely to improve your overall response time. All it could do is make the initial delay larger. (I think fetchsize is a suggestion anyway.)
If you are happy with the query time itself when run on the database, you should examine how much data you are returning. I assume you can't return less rows. However, you may be able to return less columns. Even if you can only save one column, it is a significant amount of data over 4000 rows.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
James Gordon
Ranch Hand

Joined: Aug 09, 2002
Posts: 106
Hi,
Initially I felt that the network traffic is causing all the delays.
But when I tried with a database client program (Aqua Data Studio through
JDBC), I got the results in 1/8th of the time. The client program access
the same database through the same network but with far better performance.
Thus, I felt that there might be some other reason for this.

Thank you.
Jeanne Boyarsky
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 30749
    
156

James,
The JDBC driver adds some time, but that wouldn't account for such a large difference.
Mark Latham
Greenhorn

Joined: May 27, 2003
Posts: 22
You may want to also consider garbage collection as a contributor to the delay (though 10s seems a bit long). Excessive object creation in a loop can very quickly cause a "stop the world" collection.
You can test this out by executing the same query and stub out the ResultSet processing portion of your code (something like "while(rs.next){}"). If that's speedy, then just add rs.getString() for each column you need from the ResultSet. Keep adding stuff back until you get the delay. Or, if you're in a lazy mood, you could just allocate a bunch more memory via the java command line.
Just a thought.
Jamie Robertson
Ranch Hand

Joined: Jul 09, 2001
Posts: 1879

I agree that you are probably having the garbage collector running. If you think it is the fetchsize, check what the current fetch size is by using getFetchSize() method on the statement. Then see if the delay you are experiencing is coming at the fetching stage in your code. It is the only way you can tell if it is the fetching.
If the code isn't too long, you could post the loop here and we can give you a more detailed response( remember to use the UBB code tags to preserve formatting.
Jamie
just for our info, which database/driver are you using?
Jamie
*make sure you don't use a scrollable resultset ( all the records will be cached )
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Performance Issue - Constant Delay in ResultSet.next()