Long time while traversing Resultset for large data


General J2EE: Long time while traversing Resultset for large data

  1. I have executed a query which returns Resultset which contains around 10000 records.
    I use while(rs.next)

    This loop takes around 2 minutes. How to reduce the time of the loop.
  2. One of the first thing you may want to try is to set the default fetch size of the resultSet.

    You can set it using statement.setFetchSize(int) or resultSet.setFetchSize(int). If you set in on the statement, it must be set before invoking any of the execute* method that creates a ResultSet. The statement will copy it's value to result sets.

    By setting a larger fetch size, you will have less round-trip between your app server and your db server to fetch data. I think the default is something like 10 (so every ten resultSet.next() invokation goes back to the db server to fill the resultSet buffer).

    I hope this gives you a good start. Google "JDBC optimisations" and you should find some presentations about the subject.