• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

rs.next() bottle neck

 
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Ranch Hand
Posts: 198
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 198
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
May be fetch size is too low, causing too many round trips to database. Try to increase the fetch size.
 
G Estes
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 198
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Ranch Hand
Posts: 62
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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...
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic