Avoiding n+1 eager fetching of child collection element association

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.

Are you having 'n+1 selects' issue for Baz or Bar ? In the stacktrace trace that you provided, it looks like the n+1 selects is for 'Baz'. — Sashi, Nov 14 '12 at 17:19
That is correct. The collection of Bar entities is eagerly loaded correctly, as expected, but this eager load apparently forces N selects of Baz elements. — Morten Jacobsen, Nov 15 '12 at 07:02

Sashi · Accepted Answer · 2012-11-15T13:57:48.760

5

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;

    //...
}

edited Nov 15 '12 at 13:57

answered Nov 15 '12 at 13:50

Sashi

1,457
11
12

It works! Can't believe I didn't figure that out (that is to say, I thought i had already tried that, but apparently not).. Wasn't aware that it forced eager loading for those associations. Thanks for the heads-up. – Morten Jacobsen Nov 16 '12 at 07:33

score 0 · Answer 2 · answered Nov 14 '12 at 12:20

0

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.

answered Nov 14 '12 at 12:20

SJuan76

23,682
6
41
79

I see, this seems like a decent approach when the number of Baz entities is limited. However, in my case its not a viable solution, since the number of Baz entities is > 150. – Morten Jacobsen Nov 14 '12 at 12:49
That is not that big, my entities were 180. In a development setup calling a page that used such method twice and had some more logic was needed less that 250ms to complete (calling the old way needed more than 1000 second way). – SJuan76 Nov 14 '12 at 12:59
I understand, my problem is that the number is not fixed, so as more data is added to the application in a production setup, the more entities of this type will be created. – Morten Jacobsen Nov 14 '12 at 13:06
Hadn't thought about that. Doesn't seem to help me much here though, as it seems to fetch alot of other properties instead (not shown in the example). Generally, the domain model could have been given more thought wrt. the mapping, but sadly its not easy to change.. – Morten Jacobsen Nov 14 '12 at 13:30

score 0 · Answer 3 · answered Nov 14 '12 at 13:10

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

I understand what you are suggesting, but the problem is not really the loading of the collection, that works fine. The problem is that while loading the collection eagerly, hibernate eagerly fetches associations of each element in the collection in a SELECT N+1 fashion (without me ever touching the association). — Morten Jacobsen, Nov 14 '12 at 13:15
I see. I finally got the issue. OK, but even the class supports batch-size="N". Could not this help on the Baz class? please check this (scroll a bit down) http://docs.jboss.org/hibernate/orm/4.1/manual/en-US/html_single/#mapping-declaration-class — Radim Köhler, Nov 14 '12 at 13:23

Avoiding n+1 eager fetching of child collection element association

3 Answers3

Linked