4

I am running a select command which returns 1,000,000 rows iterating over the ResultSet. The below code takes 5 minutes to execute.

Is there a faster way of iterating over a ResultSet?

conn = getDbConnection();
Statement createStatement = conn.createStatement();
ResultSet rs = createStatement.executeQuery("Select * from myTable");
while (rs.next())
{
    //do nothing
}

Is there a way in java to make it more efficient while go over all the records in the result set.

Thanks

corlettk
  • 13,288
  • 7
  • 38
  • 52
Blue Label
  • 427
  • 1
  • 7
  • 15
  • 1
    You can get the database to perform the logic using a stored procedure or some such. – trojanfoe Nov 21 '11 at 10:43
  • 2
    Why would you select `1000000` rows into memory ? – jmj Nov 21 '11 at 10:44
  • 1
    Its impossible to answer the question without knowing what you are trying to achieve. The code you posted selects a large number of rows then iterates over them doing nothing. Let us know what you are trying to do then you'll get some help. – Qwerky Nov 21 '11 at 10:44
  • Does the same query work faster if you execute it directly in DB? – soulcheck Nov 21 '11 at 10:44

4 Answers4

12

You may use setFetchSize(rows) to optimize fetch size, which fetches specified number of rows in one go from DB.

conn = getDbConnection();
Statement createStatement = conn.createStatement();
createStatement.setFetchSize(1000);
ResultSet rs = createStatement.executeQuery(“Select * from myTable”);
while (rs.next())
{
//do nothing        
}

Note that fetchSize is just a hint to DB and it may ignore the value. Only testing will reveal if its optimal.

Also, in your case its may be better to alter Scrollable attribute of Statement, as you may not process all records at once. Which scrollable option to choose depends on whether you want to see other's changes while you are iterating or not. 

//TYPE_FORWARD_ONLY 
//      The constant indicating the type for a ResultSet object 
//    whose cursor may move only forward.
conn.createStatement(ResultSet.TYPE_FORWARD_ONLY, 
                           ResultSet.CONCUR_READ_ONLY);

Or

//TYPE_SCROLL_INSENSITIVE
//      The constant indicating the type for a ResultSet object that is 
//    scrollable but generally not sensitive to changes made by others.    
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, 
                           ResultSet.CONCUR_READ_ONLY);

Or

//TYPE_SCROLL_SENSITIVE
//      The constant indicating the type for a ResultSet object that is 
//    scrollable and generally sensitive to changes made by others.
conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, 
                           ResultSet.CONCUR_READ_ONLY);

See JDBC API Guide for further details

Prashant Bhate
  • 10,907
  • 7
  • 47
  • 82
4

Rather than trying to pull 1 million rows from the database (which you aren't going to be able to run fast no matter how hard you try), you should make the database do the work for you and return the answer you want instead of intermediate results. Try writing a more complex select query or a stored procedure.

Konstantin Komissarchik
  • 28,879
  • 6
  • 61
  • 61
1

I don't think there is a more efficient way to iterate over a resultset. Have you checked how fast the query actually runs, e.g. in SQLDeveloper or some other database tool? My assumption is, that the table is not indexed or some other performance bottleneck on the database end exists.

Jules
  • 1,352
  • 7
  • 19
1

Make sure you're using pooled connections.

Using PreparedStatements could also have a possitive effect on performance.

(Though that probably won't save you several minutes.)

What are you doing with the data? Do you really need to load all that data at once?

Puce
  • 37,247
  • 13
  • 80
  • 152