Avoiding n+1 eager fetching of child collection el

Question

I have the following classes:

@Entity
@Table(name = "base")
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
@DiscriminatorColumn(name = "DISCRIMINATOR", discriminatorType = DiscriminatorType.STRING)
@ForceDiscriminator
public class Base {
    // ...
}

@Entity
@DiscriminatorValue("foo")
public class Foo extends Base {
    @OneToMany( mappedBy = "foo", cascade=CascadeType.ALL )
    private List<Bar> bars = new ArrayList<Bar>();

    // ...
}

@Entity
public class Bar {
    @ManyToOne (optional = false)
    @JoinColumn(name = "foo_id" )
    private Foo foo;

    @OneToOne
    @JoinColumn(name = "baz_id", nullable = false)
    private Baz baz;

    //...
}

@Entity
public class Baz {
    // ...
}

Now I basically want to load all Base, but eager load bars when applicable, so I use the following query:

SELECT b FROM Base b LEFT JOIN FETCH b.bars

While this works, it seems to generate a SELECT N+1 problem for the Bar entities:

Hibernate: /* SELECT b FROM Base b LEFT JOIN FETCH b.bars */ SELECT ...
Hibernate: /* load com.company.domain.Baz */ SELECT ...
Hibernate: /* load com.company.domain.Baz */ SELECT ...

Is it possible to tell hibernate to eagerly load an association for each element in the child collection without resorting to N+1 SELECTs?

I tried something along the lines of the following query, which obviously does not work since its a collection:

SELECT b FROM Base b LEFT JOIN FETCH b.bars LEFT JOIN FETCH b.bars.baz
//Results in: illegal attempt to dereference collection [Foo.id.bars] with element property reference [baz]

I also tried using IN(b.bars) bars, and while this allows me to reference the child collection, it does not seem to eagerly load the bars collection which is my goal.

An explanation of why this happens would also be nice, since I cannot seem to figure it out.

Answer 1

I would say that the change of fetching strategy could help in this case. Documentation says:

http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html_single/#performance-fetching-batch

Extract:

You can also enable batch fetching of collections. For example, if each Person has a lazy collection of Cats, and 10 persons are currently loaded in the Session, iterating through all persons will generate 10 SELECTs, one for every call to getCats(). If you enable batch fetching for the cats collection in the mapping of Person, Hibernate can pre-fetch collections:

<class name="Person">
    <set name="cats" batch-size="3">
        ...
    </set>
</class>

With a batch-size of 3, Hibernate will load 3, 3, 3, 1 collections in four SELECTs. Again, the value of the attribute depends on the expected number of uninitialized collections in a particular Session.

I am using it and it works good. In case I am paging and always selecting only 20 records the batch-size="20" works great. If I need more then 20, still the calls to DB are decreased

Answer 2

If you would like to retrieve Bar and Baz with out (n+1) selects please use the following hql.

SELECT b FROM Base b LEFT JOIN FETCH b.bars bar LEFT JOIN FETCH bar.baz

This should result in just one sql.

Also, if you do not want to fetch 'Baz', just the make the association from Bar->Baz 'lazy'.

JPA by default enforces 'eager' fetching for '@OneToOne' and '@ManyToOne' associations. So, you have to explicitly make it lazy as shown below.

@Entity
public class Bar {

    @OneToOne
    @JoinColumn(name = "baz_id", nullable = false, fetch=FetchType.Lazy)
    private Baz baz;

    //...
}

Answer 3

My approach (I had a limited, stable number of second-level entities)

First, get all Bars into the session:

 SELECT bar FROM Bar bar

After that, all the Bar entities will be in the cache and your query will be able to access them without additional entities.