If you are scanning through millions of records and just looking at each one in turn, and then moving on, you would not expect Java to try and keep the past records in memory. However, as you move forward, this may be exactly what it's going, and you may get an out of memory exception.
The JDBC driver for MySQL has "magic" set of calls to fix this:
myStatement = myConnection.createStatement(
java.sql.ResultSet.TYPE_FORWARD_ONLY,
java.sql.ResultSet.CONCUR_READ_ONLY
);
mStatement.setFetchSize(Integer.MIN_VALUE);
It's the second line using MIN_VALUE that is the trick. It's actually a negative number (-2147483648 on my machine), which would normally not make sense, how can you have a "negative" fetch size?, but is actually a magic value for the driver.
At this point your code should run smoother.
I found this info at:
http://dev.mysql.com/doc/refman/5.0/en/connector-j-reference-implementation-notes.html
Comments