[SOLVED] @BatchSize but many round trips when fetching a @ManyToOne association

Issue

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

Solution

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>

Answered By – Radim Köhler

Answer Checked By – Willingham (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *