[jboss-user] [EJB 3.0] - How to efficiently page object with initialized collections

[pmpm]

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 

View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=3969272#3969272

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=3969272