1 Reply Latest reply on Sep 12, 2006 3:56 PM by Laszlo Fogas

    How to efficiently page object with initialized collections

    Pawel Miniewicz Newbie


      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;
       rl = q.setMaxResults( 1000).setFirstResult(resIt).getResultList();
       for (Example e: (List<Example>) rl)
       //my code
       resIt += rl.size();
       } 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