Selecting @OneToOne entities without N+1 queries ( batching)?

Hibernate ORM
1

So imagine the following scenario… we have two different entities, connected with a @OneToOne annotation-

@javax.persistence.Entity
@Table(name = "spawner")
@Access(value = AccessType.FIELD)
public class Spawner extends HibernateComponent {

    @OneToOne
    @JoinColumn(name = "location_id", referencedColumnName = "identity_id")
    @Fetch(FetchMode.JOIN)
    public Location location;

    public Spawner() { }

@Entity
@Table(name = "location")
@Access(AccessType.FIELD)
public class Location extends HibernateComponent {

    @ManyToOne
    @JoinColumn(name = "chunk_id", referencedColumnName = "identity_id", updatable = false)
    @Fetch(FetchMode.JOIN)
    public Chunk chunk;

    public Location() {}
}

When we select the Spawner with the following statement session.createQuery("select s from Spawner s where s in (...)"); hibernate generates a output looking like this.

Hibernate: select identity0_.id as id1_2_0_, location1_.identity_id as identity1_6_1_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_, location1_.chunk_id as chunk_id4_6_1_, location1_.x as x2_6_1_, location1_.y as y3_6_1_ from identity identity0_ inner join location location1_ on (location1_.identity_id=identity0_.id and (identity0_.id in (8326589099709645653 , 6287325594386187920 , 5407611297503526929 , 8523982519594665733 , 4028725686660451036 , 4729790674605130415 , 6901230747306707572 , 89683718271946391 , 663547951024983400 , 487871300966958647 , 2986281183600263327 , 7129934223659254130 , 7423461182852450838 , 6258927419235452915 , 4756503673250530721 , 7989242675644875262 , 7502164321920434546 , 9125201177335319448 , 5276750027792075277 , 2958385004616501743 , 8131213851438696494 , 5378786184839581257 , 4800722432645154706)))
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?
Hibernate: select chunk0_.identity_id as identity1_0_0_, chunk0_.createdOn as createdo2_0_0_, chunk0_.x as x3_0_0_, chunk0_.y as y4_0_0_, inchunk1_.Chunk_identity_id as chunk_id1_1_1_, identity2_.id as inchunk_2_1_1_, identity2_.id as id1_2_2_, identity2_.tag as tag2_2_2_, identity2_.typeID as typeid3_2_2_ from chunk chunk0_ left outer join chunk_identity inchunk1_ on chunk0_.identity_id=inchunk1_.Chunk_identity_id left outer join identity identity2_ on inchunk1_.inChunk_id=identity2_.id where chunk0_.identity_id=?
Hibernate: select identity0_.id as id1_2_0_, identity0_.tag as tag2_2_0_, identity0_.typeID as typeid3_2_0_ from identity identity0_ where identity0_.id=?

As you can see hibernate runs into the n+1 issue and executes one “select chunk query” for each loaded location…

How do we prevent this and force them to batch/join it into fewer queries being executed ? We still want that “location” reference aswell as the “chunk” reference being loaded eager… so we dont wanna ignore them when loading the “spawners”, instead our goal is to batch the queries getting executed to select the “location” and “chunk” inside the location.

Any ideas on this ? How do we achieve this in hibernate ?

3

@Lars_Matthaus1 you have a two way association, which is not bidirectional. This makes things complicated, as requiring EAGER fetching on both sides of the mappings will mean all your queries will run into the N+1 problem. Note that @Fetch(FetchMode.JOIN) has no effect in HQL queries.

I would strongly suggest mapping all to-one associations as FetcType.LAZY, and only requesting eager fetching for queries where it’s really needed explicitly via join fetch.

In your example, the resulting query would look something like:

  select s from Spawner s
    join fetch s.location l
    join fetch l.chunk
  where s in (...)

You could also slightly mitigate the N+1 problem by setting the default batch fetch size for these entities (@BatchSize or hibernate.default_batch_fetch_size), but that would only temporarily hide the problem and not solve it.

Finally, I recommended reading Hibernate’s introduction guide, especially this chapter and the 2 after it at least which talk specifically about association fetching.