aspose file tools*
The moose likes JDBC and the fly likes rs.next() bottle neck Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Soft Skills this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "rs.next() bottle neck" Watch "rs.next() bottle neck" New topic
Author

rs.next() bottle neck

G Estes
Ranch Hand

Joined: Oct 22, 2002
Posts: 62
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:


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
Ranch Hand

Joined: Sep 30, 2008
Posts: 198
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.


My Blog
G Estes
Ranch Hand

Joined: Oct 22, 2002
Posts: 62
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
Ranch Hand

Joined: Sep 30, 2008
Posts: 198
May be fetch size is too low, causing too many round trips to database. Try to increase the fetch size.
G Estes
Ranch Hand

Joined: Oct 22, 2002
Posts: 62
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
Ranch Hand

Joined: Sep 30, 2008
Posts: 198
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
author & internet detective
Marshal

Joined: May 26, 2003
Posts: 31054
    
232

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.


[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
G Estes
Ranch Hand

Joined: Oct 22, 2002
Posts: 62
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...
 
jQuery in Action, 2nd edition
 
subject: rs.next() bottle neck