Performance Issue - Constant Delay in ResultSet.next()

[James Gordon]

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]

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.

[James Gordon]

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]

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

[Mark Latham]

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]

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 )