@BatchSize but many round trips when fetching a @ManyToOne association

Question

I use pagination with hibernate spring-data-jpa and querydsl and i use @BatchSize(size=10) to make just one round-trip to the database.

@Entity
@Table(name = "appel_offre", catalog = "ao")
public class AppelOffre implements java.io.Serializable {

    ....
    @OneToMany(fetch = FetchType.LAZY, mappedBy = "appelOffre")
    @BatchSize(size=10)
    public Set<AoActivite> getAoActivites() {
        return this.aoActivites;
    }

and :

@Entity
@Table(name = "ao_activite", catalog = "ao")
public class AoActivite implements java.io.Serializable {
    .....
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "ID_ACTIVITE", nullable = false)
    @BatchSize(size=10)
    public Activite getActivite() {
        return this.activite;
    }

my query

JPAQuery query = new JPAQuery(entityManager).from(ao) 

    .leftJoin( ao.acheteur, ach ).fetch()

    .leftJoin( ao.aoActivites , ao_ac )
    .leftJoin( ao_ac.activite , ac )
    .offset(...).limit(...).list(..);

but in the log got many round trip to the database:

1 - round-trip

.....
Hibernate: select ... from ao.ao_activite aoactivite0_ where aoactivite0_.ID_APPEL_OFFRE in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?

2 - round-trip

.....
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?

3 - round-trip

.....
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?

4 - round-trip

.....
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?

5 - round-trip

.....

6 - round-trip

.....
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?

7 - round-trip

......

8 - round-trip

.....
Hibernate: select ... from ao.activite activite0_ where activite0_.ID_ACTIVITE=?

9 - round-trip

.....

10 - round-trip

Decalcify

Answer 1

The @BatchSize makes sense on both

• One-To-Many and
• Many-to-One as well

Just in case of Many-To-One, we have to apply it on @Entity level (in our case on mapping of the Activite class)

@Entity
@BatchSize(size=25)
@Table(name = "activite" ...
public class Activite implements java.io.Serializable {
...

Check it here in doc (small cite appended below):

20.1.5. Using batch fetching

...

Batch fetching for classes/entities is easier to understand. Consider the following example: at runtime you have 25 Cat instances loaded in a Session, and each Cat has a reference to its owner, a Person. The Person class is mapped with a proxy, lazy="true". If you now iterate through all cats and call getOwner() on each, Hibernate will, by default, execute 25 SELECT statements to retrieve the proxied owners. You can tune this behavior by specifying a batch-size in the mapping of Person:

<class name="Person" batch-size="10">...</class>

...

Contradiction

I will try it soon as possible. – Decalcify

Great to see that, enjoy amazing Hibernate Youssef ;) – Selfrealization

Answer 2

1. @BatchSize makes more sense for to-many associations (e.g. @OneToMany), not for to-one relations.

2. With batch fetching, you have (M/N + 1) database roundtrips, where M is the number of children entities in your uninitialized to-many association and N is the batch size.

3. If you want one and only one secondary database roundtrip, you need to use sub-select fetching.

4. For to-one associations (e.g. @ManyToOne) use JPQL or Criteria JOIN FETCH instead.

Junette

4 - I cant use join fetch if i use pagination. 3 - sub-select fetching i think work also just for to-many associations e.g just for collection. – Decalcify

But the JPA query already supports .fetch(), right? As for 3. It's for collections if you want to fetch those as well. – Junette

Yes it works with fetch but with pagination offset () limit () there is a memory problem if we fetch collection. – Decalcify

It depends on how large the to-many collection is. That's why I said to use sub-select fetching, instead of joining. When combining collection join fetches with pagination, Hibernate resembles to fetching the whole collection to make sure that children associations are not truncated. – Junette

I will try it and see if it usefull in my case. – Decalcify

i understand now what u mean but in my case i want to fetch just 10 collection not the all the collections and fetch sub select i think don't work for to-one associations. @RadimKöhler explain in its answer that batch size work also for to-one relations – Decalcify

I am glad you found what you were searching for. – Junette