java.lang.OutOfMemoryError: Java heap space error when performing millions of queries

Question

In my application, I need to perform millions of queries to MySQL database. The codes look as follows:

for (int i=0; i< num_rows ; i++) {
   String query2="select id from mytable where x='"+ y.get(i) "'";              
   Statement stmt2 = Con0.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);     
   ResultSet rs2 = stmt2.executeQuery(query2);    
   ... // process result in rs2
   rs2.close(); 
}

where num_rows is around 2 million. After 600k loops, java report an error and exit:

java.lang.OutOfMemoryError: Java heap space error.

What's wrong in my codes? How should I avoid such an error?

Thanks in advance!

Answer 1

Close your statements as well.

Answer 2

Statement is no good solution here. Try the following code:

PreparedStatement pre = Con0.prepareStatement("select id from mytable where x=?");

for (int i=0; i< num_rows ; i++) {
   pre.setString(1, y.get(i));
   ResultSet rs2 = pre.executeQuery();
   ... // process result in rs2
   rs2.close(); 
   pre.clearParameters();
}
pre.close();

Answer 3

I don't know if the answer accepted by you have solved your problem, since it doesn't change anything that could cause the problem.

The problem is when ResultSet is caching all the rows returned by the query, which can either be stored while you iterate through set, or prefetched. I've had similar problem with PostgreSQL JDBC driver, which ignored the cursor fetch size, when running in no-trasactional mode.

The JDBC driver should use cursors for such queries, so you should check driver's documentation about fetchSize parameter. As alternative, you can manage cursors yourself executing SQL command to create cursor and fetch next X rows.

Answer 4

Using a preparedStatement, since only the value of X changes in each loop, declared outside de loop should help. You're also, at least in the shown code, not closing the statement used, which might not help the garbage collector to free the used memory.

Answer 5

Assuming that you are using a single connection for all your queries, and assuming your code is more complicated than what you show us, it is critical that you ensure that each Statement and each ResultSet is closed when you are finished with it. This means that you need a try/finally block like this:

for (int i=0; i< num_rows ; i++) {
  String query2="select id from mytable where x='"+ y.get(i) "'";              
  Statement stmt2 = Con0.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);     
  ResultSet rs2 = null;   
  try {
    rs2 = stmt2.executeQuery(query2);    
    ... // process result in rs2
  } finally {
    try {
      stmt2.close();
    } catch (SQLException sqle) {
      // complain to logs
    }
    try {
      if (rs2 != null) { rs2.close(); }
    } catch (SQLException sqle) {
    }
  }
}

If you do not aggressively and deterministically close all result set and statement objects, and if you do requests quickly enough, you will run out of memory.