Efficient way to iterate over a result in Java

Question

I run a select command that returns 1,000,000 rows, iterating over a ResultSet. The code below takes 5 minutes.

Is there a faster way to iterate 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 when looking at all the records in a result set.

thanks

Answer 1

You can use setFetchSize(rows) to optimize the size of a sample that retrieves the specified number of rows at a time from the database.

 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 of DB , and it can ignore this value. Only testing will show if it is optimal.

Also, in your case, it might be better to change the Scrollable Statement attribute, since you cannot process all records at once. Which scrollable option to choose depends on whether you want to see other changes during the iteration 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 the JDBC API Guide for more information.

Answer 2

Instead of trying to get 1 million rows out of the database (which you won’t be able to do quickly, no matter how hard you try), you should make a database to do your work and return the answer instead of intermediate results. Try writing a more complex selection query or stored procedure.

Answer 3

I don’t think there is a more efficient way to iterate over the results. You checked how fast the query is executed, for example. in SQLDeveloper or some other database tool? My assumption is that the table is not indexed or that there is no other performance bottleneck at the end of the database.

Answer 4

Make sure you are using federated connections.

Using PreparedStatements can also have a positive effect on performance.

(Although this probably won't save you a few minutes.)

What do you do with the data? Do you really need to download all this data at once?