Postgresql OutOfMemory with Criteria.scroll() in Hibernate with 2M records

I am running a Criteria.scroll() on PostgreSQL on a DB containing 2M records. The memory keeps increasing and finally开发者_如何学编程 it generates an OutOfMemoryException. Please can you advice how to fix this.

Postgresql DB version: 8.4 Postgresql Driver Used: postgresql-8.4-701.jdbc4.jar

Is there some known issue with Hibernate scroll() in PostgreSQL?

Appreciate any guidance/suggestions.

You need to call session.clear() periodically to clear the objects out of the Hibernate session. For example:

while(results.next()) {
  processResults(results);
  getSession().clear();
}

You might only want to clear the session every 100 iterations or so of the loop. I would time a few different ways to see what is best.

I am going to guess that it is a Java OutOfMemoryException based on the way you wrote the name.

Hope this isn't to obvious haha, but you get a ScrollableResults when you do your Criteria.scroll() and I assume you are doing something with that data as you go through it. Are you loading each item into memory?

Or are you getting this error just by getting the ScrollableResults? I would find that odd.

If this is to big you need to add setFetchSize(XXX) on your query

You have to call System.gc() every now and then (for example every 1000 records that you process). Or, don't fetch so many objects at once; use the setMaxResults(int) and setFirstResult(int) methods to fetch smaller data subsets. For example 10K records at a time, then System.gc() and then the next batch, etc.