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...
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
Joined: Jan 07, 2006
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?
Joined: Jan 30, 2000
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.
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.