Hi,

Here is my situation.

I have a class called Order. This has a many-to-one relationship with Customer, has a one-to-many relationship with OrderItem, which has lazy="true".

I need to generate a report of Order objects using a certain selection criteria, such as orderDate > mm/dd/yyyy. I use a namedQuery to achieve this.

The query returns a large number of results (>50,000). I use the list.iterator() method to iterate through the results. Obviously, I get an OutOfMemory error, since the Session tries to hold all the results.

The recommended solution is to use setFirstResult and setMaxResults on the Query interface, and the flush and clear the Hibernate session for every 'page' . This works. But, the query to select and sort the results is executed totalNumberOfResults / maxResult times - each time with an additional condition on the row_number. This is a severe performance overhead.

Is there any way to achieve the same results in a far better way?

Thanks in advance.

Satish

You might want to evict each order after you process it using Session.evict, this will probably reduce your memory usage.

David

All in all I think your current approach is actually fine, considering you are paging over >50000 rows of data. The user will only ever be able to take in a page at a time anyway, maybe 50 results on a single page.

However if I understand you correctly, its the fact that you have to issue a new query for each page of results that is bothering you?

If so I have an alternative suggestion for you.

Pre-fetch more than one page at time, so if the user can see 50 results in a page, pre-fetch 200, then as the user pages forward you can always read ahead, so there is no noticeable lag from a user perspective.

You can determine how many rows to fetch based on the memory consumption of you Order objects.


Cheers,
Conor.

Having re-read your post, I am not sure if this is what you are looking for, if you are really creating a report, I am not sure if hibernate in appropriate in this instance, esp if you don't really need to work with the object model afterwards.

Thanks, David and Conor, for your replies.

To clarify, I need to generate a report that is saved to a file, so I need to process all the >50,000 objects that are returned by the query.

I am using the object-model to do this (rather than do a non-Hibernate JDBC kind of call), because there are computations that use business rules, and I do not want these formulae/rules to be repeated in all the reports that need to be generated.

And, David, I tried the evict() approach that you suggested, as follows:

Iterator<Order> iterator = getSession().getNamedQuery("...").setParameterList(...).list.iterator();

I am neither using setMaxResults nor setFirstResult, which means the above query would return all the >50,000 objects. This is followed by:

while (iterator.hasNext()) {
Order order = iterator.next();
// Access the lazy collection;
// Work on the order object;
getSession().evict(order);
}

This still throws a OutOfMemoryError after a few hundred records.

Any clues would be appreciated.

Regards

Satish

I suspect that this isn't a hibernate problem, but your code keeping references to objects that it no longer needs.

David

I am inclined to agree with David on this one, I suggest using a profiler to see what's going on in memory, you can also use jconsole to observe the heap.

I would also try defining the Order object outside of the while loop, you might find that the jvm can reclaim the memory better in that case.

You can also try using a ScrollableResults object instead of list.

i.e. ScrollableResults rs = criteria.scroll()

Thank you very much David and Conor.

The problem is resolved, and I have a very efficient way of retrieving large result sets - thanks to your suggestions.

I used ScrollableResults instead of list().iterator() and also moved the declaration of Order outside the loop.

Thanks once again for your help.

Satish