How to optimize fetching multiple collections in Hibernate?

Question

I am trying to improve performance on an application using Hibernate which is executing too many SQL calls to the database. I think data fetching can be grouped together to reduce the calls and improve performance but I'm at a bit of a loss here. I've looked at the Hibernate documentation about subselects and batch fetching which does help but I don't think it completely eliminates the problem.

In the example below I need to get details for a list of soldiers that are part of a troop and display it on a webpage.

@Entity
public class Troop {
    @OneToMany(mappedBy="troop")
    public List<Soldier> getSoldiers() {
   ...
}

It's easy to set the fetch strategy to subselect, batch or eager to retrieve all the soldiers part of this troop without too many SQL statements.

@Entity
public class Soldier {
    @Id
    String soldierId

    String firstName;
    String lastName;

    @OneToMany(mappedBy="address")
    public List<Soldier> getAddress() {
     ...
    @OneToMany(mappedBy="combatHistory")
    public List<Soldier> getCombatHistory() {
      ...
    @OneToMany(mappedBy="medicalHistory")
    public List<Soldier> getMedicalHistory() {
      ...
}

Each soldier entity has multiple one to many relationships with other entities that are lazily loaded. I need to initialize these collections and retrieve values from them. If a Soldier has 3 one to many relationships and a Troop has 1,000 soldiers that would result in 3 x 1,000 SQL calls!

Is there a way to optimize this by reducing the number of calls? Since I already know the soldierIds I need to retrieve can I retrieve the entities and have them available in the first level cache?

For example I could query

from Address as a where a.soldierId in (...)
from CombatHistory as a where a.soldierId in (...)
from MedicalHistory as a where a.soldierId in (...)

If the Address, CombatHistory etc entities can be cached then a SQL select won't be executed when the collection is accessed within each soldier. This would reduce the number of calls to one per collection (3 in this case) rather than one per collection per soldier (3 x 1000)

I didn't really see much in the documentation about addressing this issue so any hints and ideas are greatly appreciated. Keep in mind that since these collections are Lists and not Sets the left join fetch cannot be executed on multiple collections or Hibernate will return an exception.

HibernateException: cannot simultaneously fetch multiple bags 

Answer 1

You could also use the annotation @Fetch(FetchMode.SUBSELECT) on your collection if you "touch" one collection of that type, all collections will be fetched in ONE single SQL request.

@Entity
public class Country implements java.io.Serializable {

    private long id;
    private int version;
    private String country;
    private Set<City> cities = new HashSet<City>(0);

    @Fetch(FetchMode.SUBSELECT)
    @OneToMany(mappedBy = "country", cascade = CascadeType.ALL)
    public Set<City> getCities() {
        return cities;
    }


    ...
}

Here is an example of how to use it:

    public List<Country> selectCountrySubSelect() {
        List<Country> list = getSession().createQuery("select c from Country c").list();
        // You don't have to initialize every collections
        // for (Country country : list) {
        // Hibernate.initialize(country.getCities());
        // }
        // but just "touch" one, and all will be initialized
        Hibernate.initialize(((Country) list.get(0)).getCities());
        return list;
    }

the logs :

DEBUG org.hibernate.engine.loading.internal.CollectionLoadContext.endLoadingCollections():  - 2 collections were found in result set for role: business.hb.Country.cities
DEBUG org.hibernate.engine.loading.internal.CollectionLoadContext.endLoadingCollection():  - Collection fully initialized: [business.hb.Country.cities#1]
DEBUG org.hibernate.engine.loading.internal.CollectionLoadContext.endLoadingCollection():  - Collection fully initialized: [business.hb.Country.cities#2]
DEBUG org.hibernate.engine.loading.internal.CollectionLoadContext.endLoadingCollections():  - 2 collections initialized for role: business.hb.Country.cities
DEBUG org.hibernate.engine.internal.StatefulPersistenceContext.initializeNonLazyCollections():  - Initializing non-lazy collections

Answer 2

even if you fetch all items of the collections hibernate won't be sure that all items of the collections are loaded and wont fill in the collections. You could however eager load each collection one at a time since the soldiers are in the session cache.

from Soldier s left join fetch s.Address where s.soldierId in (...)
from Soldier s left join fetch s.CombatHistory where s.soldierId in (...)
from Soldier s left join fetch s.MedicalHistory where s.soldierId in (...)

this will reduce the roundtrips to 3.