CODEWITHSUNDEEP
javaout-of-memory
Xiaodong Yu
Xiaodong Yu

Reputation: 33

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

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!

Upvotes: 3

Views: 4883

Answers (5)

Cjxcz Odjcayrwl
Cjxcz Odjcayrwl

Reputation: 22847

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.

Upvotes: 2

Eddie
Eddie

Reputation: 54421

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.

Upvotes: 1

Angelo Fuchs
Angelo Fuchs

Reputation: 9941

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();

Upvotes: 4

Jonathan Drapeau
Jonathan Drapeau

Reputation: 2610

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.

Upvotes: 1

mcfinnigan
mcfinnigan

Reputation: 11638

Close your statements as well.

Upvotes: 5

Related Questions