• 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

Delay in JDBC ResultSet

 
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi all,
I am using Websphere 5 app server, Oracle 8.1.7. Oracle is on Unix box and WSAD (Websphere Server) is at my local machine. Using JSP, I executed a simple query which has got 7500 records. The sample code is:
PreparedStatement ps = conn.prepareStatement("select a,b,c from table");
ResultSet rs = ps.executeQuery();
while (rs.next()) {
// Add each record in ArrayList
}
// store ArrayList in session.
I come to that the JSP page to display all 7500 records is taking around 26-28 seconds. The more processing time is in while loop. I tried to improve performance with and without connection pool, but no improvement.
I need all this 7200 records to be shown by pagewise, so when I get all records in an ArrayList, I am soring it in session and later using it by pagewise which is making application fast, but the main issue is the time taking is more when the page is loading for very firt time. I even tried the same with Servlets, no improvement
Is there any best way to improve this performance? Any JDBC changes or do I need to put the JDBC logic in EJB's?
Please advice
Thanks,
 
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
Search this forum and the servlet forum for "paging." You can get just the records you need for the current page from the database.
 
Greenhorn
Posts: 4
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi chandra
Try putting System time in miillis before and after the code where
you actually fetch data from RS for adding to ArrayList.
You will find the java itself takes lot of time in retriveing data from RS
This slows your process there is not much you can do about it
 
chandubcs
Ranch Hand
Posts: 47
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
By using system millisec's only, I am able to find out the exact time its taking for processing.

long begin = System.currentTimeMillis();
while (rs.next()) {
// Add each record in ArrayList
}
System.out.println("Page loading in millisec's"+(System.currentTimeMillis()-begin)+"...in sec's"+((System.currentTimeMillis()-begin))/1000);
// store ArrayList in session.
Is there any better way to improve rs.next() performance?
Thanks,
 
Wanderer
Posts: 18671
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No, this is misleading. You're probably not waiting for "Java processing" here; you're really waiting for (a) the DB, and (b) the network. Just because a ResultSet object has been returned, that does not mean that all the data has been received from the DB. (Even though it might look that way.) Instead, a ResultSet is typically still associated with the Connection and Statement that created it, in such a way that the ResultSet is still busy getting data from the DB when you start using it. This allows you to start using the ResultSet and processing results as soon as any rows of data are available, rather than waiting until all the rows have been sent. It's a form of multitasking. But if you Java-based processing is simple, then the JVM can keep up with data rows as they're sent across the network. Which means that every time you call rs.next(), you're probably waiting for the DB to send more data.
Jeanne has given good pertinent advice here. Chances are, you don't want to try to display all 7200 records on the page at one anyway, right? You want to gather maybe 20-50 records at once and make a page that shows them, right away, rather than waiting for the remaining 7150 records to be sent. No matter what you do, it would take time to find and send 7200 records, and you don't need to wait that long to generate one page.
Offhand, I would probably have one thread which processes the ResultSet and adds received data to a List. Then another thread generates a page by looking at the List to select the appropriate rows. For the first page request, you might need rows 1-50. Maybe only 20 rows are available the first time you check. So you rocess the rows that are available, then wait for more. Once row 50 has arrived, the page-formatting thread can finish a page and send it off. Meanwhile the RS-processing thread just keeps on processing results as they come in. Of course you need to synchonize access to the shared mutable List here; that's normal.
 
reply
    Bookmark Topic Watch Topic
  • New Topic