Conversion of resultset to Java model slow for a million records

Question

This is what my code looks like. The query executes in less than a minute, but the while loop takes more than 2 hours to finish! I have around 800,000 records. Can you please let me know the best way to save in the reportModelList. I can even switch to Hibernate if required.

PreparedStatement ps =connection.prepareStatement(sql);
ResultSet rs = ps.executeQuery();
try{
while (rs.next()) {
ReportModel t = new ReportModel();
t.set1(rs.getDate(1));
t.set2(rs.getDouble(2));
...
...
t.set60(rs.getString(60));
reportModelList.add(t)

}}finally {
DbUtils.closeQuietly(rs);
}

Answer 1

Three suggestions.

1. As @Luiggi Mendoza writes as a comment, initialize your List with a large initial buffer, so it isn't reallocating and copying all day.
2. Depending on your DB and JDBC driver, you may get a speedup with ps.setFetchSize(50000); (Play around with constant.) If your fetch size is small you are doing a lot of I-want-more round-trips to your server.
3. Don't guess which step is the bottleneck; use a profiler.