Hi,

I have an entity bean with a collection of items.

@OneToMany(cascade = { CascadeType.ALL}, fetch = FetchType.LAZY, mappedBy ="invoice")
@BatchSize( size = 10)
public List<Item> getItems()
{
 return items;
}

Everything works fine in everyday use. But ocassionaly I would like to perform statistics, which is done through selecting all the entities and performing operation on their items.

Because there is a large number of objects I have to page through them in order to not to run out of memory. I made two attempts to solve this problems but both are unefficient and my method timeouts.

First approach.

My query looks like:

"select e from Example join fetch e.items where ..."

Then I page through the result set and make my computations on result objects:

 int resIt = 0;
 List<Example> rl = null;
 do
 {

 rl = q.setMaxResults( 1000).setFirstResult(resIt).getResultList();

 for (Example e: (List<Example>) rl)
 {
 //my code
 }

 resIt += rl.size();
 entityManager.clear();

 } while (rl.size() > 0);

But then I got a warning "firstResult / maxResults specified with collection fetch ; applying in memory". I uderstand that when collection is fetched, Hibernate cannot use sql "limit" because number of rows is not equal to number of objects, and "in memory" means additional selects.

Second approach.
I removed join fetch from query. But then items collection are fetched ( in batch of 10) during computatins, which is also slow. I dont want to change the value of batch in mapping.

Do you see any solution to my problem? Maybe there is a way to temporarily increase the batch size of the collection (for the duration of my statistics method)?

Thanks in advance
Pawel Miniewicz