Efficient way to go over result set in Java

Question

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

Answer 1

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