The moose likes JDBC and Relational Databases and the fly likes Delay in JDBC ResultSet Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Delay in JDBC ResultSet" Watch "Delay in JDBC ResultSet" New topic

Delay in JDBC ResultSet

Ranch Hand

Joined: Jun 03, 2001
Posts: 47
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 ( {
// 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
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33119

Search this forum and the servlet forum for "paging." You can get just the records you need for the current page from the database.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2

Joined: Nov 20, 2003
Posts: 4
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
Ranch Hand

Joined: Jun 03, 2001
Posts: 47
By using system millisec's only, I am able to find out the exact time its taking for processing.

long begin = System.currentTimeMillis();
while ( {
// Add each record in ArrayList
System.out.println("Page loading in millisec's"+(System.currentTimeMillis()-begin)+" sec's"+((System.currentTimeMillis()-begin))/1000);
// store ArrayList in session.
Is there any better way to improve performance?
Jim Yingst

Joined: Jan 30, 2000
Posts: 18671
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, 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.

"I'm not back." - Bill Harding, Twister
I agree. Here's the link:
subject: Delay in JDBC ResultSet
It's not a secret anymore!