How to avoid OutOfMemoryError with large data sets??

baer · **Joined:** Thu May 13, 2004 11:07 am **Posts:** 8

Hello

I'm a newbie to hibernate and testing for our purposes. We will have large datasets (several million entries), that are not presented all together, but page by page (maybe 500 entries).
I wrote a sample application, which simply prints the entries to the console (instead of a swing-gui).
I tried to use "ScrollableResults", but after about 700'000 entries I still get a "java.lang.OutOfMemoryError". I suppose it is, because it doesn't dispose old results... (?)

How can i process the results in a matter, that page by page is loaded from the database to the application?

I'm thankful for any type of advice!

thanks,
baer

system-info:
oracle 9 database
hibernate 2.1.3
if needed i'm happy to pass on mapping docs.

code:

Code:

private void printScrollableResult() {
    try {
      Configuration cfg = new Configuration();
      SessionFactory sessionFactory = cfg.configure().buildSessionFactory();
      Session session = sessionFactory.openSession();
      String querySt = "select dyncdr_t from dyncdr_t " + " in class DyncdrT";
      Query q = session.createQuery(querySt);
      ScrollableResults cdrs = q.scroll();
      cdrs.beforeFirst();
      int i = 0;
      do {
        i = 0;
        while (i < PAGE_SIZE && cdrs.next()) {
          msg(new String("row = ") + new Integer(cdrs.getRowNumber()) + new String(": id=") + new String(cdrs.getString(0)));
          i++;
        }
        msg("------- end of page --------");

      }
      while (cdrs.scroll(PAGE_SIZE));      
    }
    catch (HibernateException he) {
      // TODO Auto-generated catch block
      he.printStackTrace();
    }

baer · **Joined:** Thu May 13, 2004 11:07 am **Posts:** 8

I've found a solution.
Is this really the only way?
clear the session after every page?

Code:

     int pagecount=0;
     List result;
      Session session = sessionFactory.openSession();
      boolean loop = true;
      while (loop){    
        Query q = session.createQuery(querySt);
        msg("created query");
        q.setMaxResults(PAGE_SIZE);
        q.setFirstResult(pagecount*PAGE_SIZE);
        result = q.list();
        for (Iterator listIt = result.iterator(); listIt.hasNext();){
          Object o = listIt.next();
          DyncdrT dynT =  (DyncdrT) o;
          msg("page " + pagecount + " object is " + dynT.getCdrid());
        }        
        session.clear();
        pagecount++;
        
      }
      session.close();

I thank you for any pointer in this topic!

christian · **Posted:** Fri May 14, 2004 5:09 am

Yes. Hibernate (and ORM tools in general) are not useful for mass updates or deletes, use a Stored Procedure and don't load your full database into memory.

baer · **Joined:** Thu May 13, 2004 11:07 am **Posts:** 8

christian wrote:

Yes. Hibernate (and ORM tools in general) are not useful for mass updates or deletes

actually, it is only a read operation...

michael · **Posted:** Fri May 14, 2004 6:57 am

well, isn't the reason for paging in the first place not to have to load your whole dataset into memory? Why do you do it then, is this really a realistic testcase for your application?

baer · **Joined:** Thu May 13, 2004 11:07 am **Posts:** 8

Thank you for your answers!

What I want to do:
We have large datasets (several million entries) which can be viewed through a gui.
Because no user really wants to go through this amount of data (and no system can handle it) I want to offer a function where only a part of the data is shown to the user (let's say 500 entries). The user shall be allowed to scroll through the results (as in google for example, where you can click on "next"...)
What I am testing now is what happens if the user went through 100 pages (which is really not very realistic...).
I just want to make sure that is not possible to create an outOfMemeoryException.

michael · **Posted:** Fri May 14, 2004 7:15 am

Well you have to make sure the no longer needed data is evicted from the Session, thats the only way to do it.

christian · **Posted:** Fri May 14, 2004 7:15 am

Query.setMaxResult()

christian · **Posted:** Fri May 14, 2004 7:16 am

Oh, wrong thread.

baer · **Joined:** Thu May 13, 2004 11:07 am **Posts:** 8

michael wrote:

Well you have to make sure the no longer needed data is evicted from the Session, thats the only way to do it.

And is there an other way than calling Session.clear()?

christian · **Posted:** Fri May 14, 2004 7:21 am

Session.evict() and Session.close().

baer · **Joined:** Thu May 13, 2004 11:07 am **Posts:** 8

One thing I don't understand:
It works fine when calling Session.clear().
However, when calling Session.evict(result) ( result = q.list();) or Session.evict(q) (Query q = session.createQuery(querySt);) it doesn't work (=outOfMemeoryError).

That means to me, that the results are not stored in the cache, but somewhere else in the session, where I don't have direct access. Is that correct?

christian · **Posted:** Fri May 14, 2004 7:37 am

Thats because you have to evict() persistent objects, not results or queries.

michael · **Posted:** Fri May 14, 2004 7:38 am

Read the api:

Quote:

evict

public void evict(Object object)
throws HibernateException

Remove this instance from the session cache. Changes to the instance will not be synchronized with the database.

Parameters:
object - a persistent instance
Throws:
HibernateException

Evict the items queried, not the result list or the query object.

baer · **Joined:** Thu May 13, 2004 11:07 am **Posts:** 8

great - that should do it! (I missed the important words in the API:( - thanks for marking them in bold! )

THANK YOU!