This week's book giveaway is in the Big Data forum.
We're giving away four copies of Elasticsearch in Action and have Radu Gheorghe & Matthew Lee Hinman on-line!
See this thread for details.
The moose likes Performance and the fly likes ResultSet performance problem Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Elasticsearch in Action this week in the Big Data forum!
JavaRanch » Java Forums » Java » Performance
Bookmark "ResultSet performance problem" Watch "ResultSet performance problem" New topic

ResultSet performance problem

Makesh Kumar Ramakrishnan
Ranch Hand

Joined: Jan 07, 2006
Posts: 88
The resultSet has 4000 rows. Each row/record has 15 columns. The main purpose of the program is to iterate thro the resultSet and add the rows in to the ArrayList.

Suppose consider the resultSet has 4000 employee Records. My program iterates thro these records, for each record it creates the Employee value object and stores it in to ArrayList.

the problem here is, the iteration and adding in to ArrayList, takes almost 3 seconds. I do want to minimize this time.

How can I do that? Any suggestion.. or this is the way resultSet object responds.. Do we need to live with this problem?


Makesh<br /> <br />SCJP | SCWCD | SCBCD | SCEA
steve souza
Ranch Hand

Joined: Jun 26, 2002
Posts: 861
My guess is converting 4000 rows of 15 columns into an ArrayList should be faster than 3 seconds, but as in all tuning you will need to do some tests to see where the time is occuring.

Some options follow.

1) Use the jamon jdbc driver to monitor your sql and jdbc method calls. It doesn't take any code changes just use the jamon jdbc proxy driver that points to your underlying driver. See the jamon link or the javaranch faq sections below for more info.

2) Test calling your sql from a non java client and time the query. If it takes 3 seconds it isn't a java problem. You could also hardcode a table with 4000 rows and 15 columns and return all the data in the table. That way you can see how much the query cost is to return the rows without a join.

3) If the query does not take 3 seconds populate your ArrayList via an array of 15 columns. See how long that takes.

The general rule is find where your bottleneck is and then tune it if you need too. You can't tune when you don't know what to tune... - a fast, free open source performance tuning api.
JavaRanch Performance FAQ
Jim Yingst

Joined: Jan 30, 2000
Posts: 18671
Note that just because a method call has returned a ResultSet does not mean that all the data has been retrieved and sent to your JVM. Some (many?) drivers implement ResultSet in such a way that you can start retrieving some data in the client even while the db may still be busy finding the remaining data on the server. Or while the remaining data may still be being sent across the network. So don't think that just because an executeQuery() has returned that the database is done. It's entirely possible this is a database problem, and can only be resolved with db tuning, query optimization, or by getting a faster / more direct connection between client and server. But maybe not. Try all of Steven's suggestions to try to discover where the problem really is.

Note that JAMon does allow you to monitor the performance of ResultSets as well as other JDBC bojects - but it looks like by default this is turned off. You will want to enable interface monitoring and ResultSet monitoring is order to see what happens with each ResultSet.

"I'm not back." - Bill Harding, Twister
Makesh Kumar Ramakrishnan
Ranch Hand

Joined: Jan 07, 2006
Posts: 88
Thanks for your reply guys.

It took 3 seconds to iterate thro the result set. I mean, after executing the query, from "while" loop starting to end of the "while" loop , it took three seconds just to iterate the result set and store it in to ArrayList. My database is Oracle. I am using oracle thin driver. Do you thin what might be the problem?
Jim Yingst

Joined: Jan 30, 2000
Posts: 18671
I think the problem is probably what I just described: either the database itself takes 3 seconds to get all the info, or the network does. I recommend you try some of the tests Steve just described to see which is the case.
Sandeep Deb

Joined: Jun 01, 2007
Posts: 15
Just posted a similar response to another query:

Try the setFetchSize(int rows) on the ResultSet. As far as I know, the JDBC specification talks about giving caching "hints" to the implementation. I have experimented with varying cache size on a performance related problem and have found quite significant benefits. I was using Oracle 9i and the OCI driver.

setFetchSize(int rows) - Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be. The default value is set by the Statement object that created the result set. The fetch size may be changed at any time.

- <a href="" target="_blank" rel="nofollow">Sandeep</a>
Allen Bandela
Ranch Hand

Joined: Feb 16, 2006
Posts: 128

I just want to inform that the setFetchSize method of ResultSet proved very useful in retrieving the data quickly from the database. I was returning large number of records (~6million).
I agree. Here's the link:
subject: ResultSet performance problem