rs.next() bottle neck

[G Estes]

I have an issue with performance in some code that I have inherited and am looking for a way around it. This Java/Swing application has client side calls via JDBC to the database using a CollableStatement. Summary of code follows:

 
CallableStatement cs;
...
cs.execute();
ResultSet rs = cs.getObject(1);
 
while(rs != null && rs.next())
{
//    new RecordProcessor(rs);
}

In my scenario, say the call returns 5000 rows of data. The code as presented above, that does not instantiate the RecordProcessor (which is a threaded processor for each result returned), simply spinning through the 5000 records takes 17-18 seconds, consistently.

Given this, the bottle neck appears to be the rs.next() method. Short of a complete refactor of the tiered architecture, is there anything that can be done to enhance the performance?

[Vinod K Singh]

If you are not instantiating RecordProcessor for each row then either change the query to return less number of records or run this task in a background thread and notify the user when done.

[G Estes]

The RecordProcessor "IS" a thread/runnable object that processes the given ResultSet in the background. The issue is not with how fast the RecordProcessor takes. Given a Vector of the same data, that logic will process the entire batch of 5000 in approx 2 secs. The time consuming item is the rs.next() method.

[Vinod K Singh]

May be fetch size is too low, causing too many round trips to database. Try to increase the fetch size.

[G Estes]

Thanks, but already tried that with values of 10, 100, 500 and 1000. None had any impact. We have found out that the database side has views that depend on other views, etc... so views on views on views. They are looking into that now and expect to improve performance by better use of database resources.

I think there may just be an inherent over-head causing a bottle neck on performance for JDBC. Does anybody else think 2-3 ms per rs.next() is excessive? I know that on another project where we use Hibernate/Spring and a true n-Tier approach, we are able to bring back thousands of records in much faster times and we can get them back as a List of data in a block. If I could do that with JDBC, that would be nice.

Any other ideas?

[Vinod K Singh]

For me re.next() always takes 0 millis. In this particular case Hibernate will also take same amount of time as that too uses JDBC.

[Jeanne Boyarsky]

Originally posted by G Estes:
[QB]I think there may just be an inherent over-head causing a bottle neck on performance for JDBC. Does anybody else think 2-3 ms per rs.next() is excessive?/QB]

Like most things in computing "it depends". If you are returning large quantities of data across a network, it is possible.

[G Estes]

Also, this morning, I found out that the database is not properly indexed...that in itself should improve performance and the speed at which the next() method is able to move the database cursor and return the next set of data.

Thanks for all of your input, but this one looks like it is a database side issue.

Until next time...