Understand different behaviours of Hibernate/JPA for findAll and findOne

Question

I was debugging a performance issue with a findAll method (from a Spring Data JPA Repository) and the problem was the fetch plan that was being used. (Hibernate was performing additional Selects to retrieve the additional entities instead of using a fetch join)

My entity has some relations to another entities and the problem that I was facing was related with the *ToOne relations.

After spending some time on google, I realised that there are some ways to manage the queries that Hibernate will perform (using JPA Criteria, NamedEntityGraph, custom queries, ...).

I've created a small example to demonstrate the different behaviours but I would like to understand why Hibernate is doing that by default. I spent some time searching in the docs but I wasn't able to find any reference to this default behaviour.

https://github.com/pmvilaca/jpa-test

The difference:

Hibernate: 
SELECT
  user0_.id                 AS id1_1_0_,
  userdetail1_.id           AS id1_21_,
  contactdet2_.id           AS id1_0_2_,
  user0_.contact_details_id AS contact_3_1_0_,
  user0_.name               AS name2_1_0_,
  userdetail1_.nationality  AS national2_2_1_,
  userdetail1_.user_id      AS user_id3_2_1_,
  contactdet2_.email        AS email2_0_2_,
  contactdet2_.mobile       AS mobile3_0_2_
FROM user user0_
  LEFT OUTER JOIN user_details userdetail1_ ON user0_.id = userdetail1_.user_id
  LEFT OUTER JOIN contact_details contactdet2_ ON user0_.contact_details_id = contactdet2_.id

and

Hibernate: select user0_.id as id1_1_, user0_.contact_details_id as contact_3_1_, user0_.name as name2_1_ from user user0_
Hibernate: select contactdet0_.id as id1_0_0_, contactdet0_.email as email2_0_0_, contactdet0_.mobile as mobile3_0_0_ from contact_details contactdet0_ where contactdet0_.id=?
Hibernate: select userdetail0_.id as id1_2_2_, userdetail0_.nationality as national2_2_2_, userdetail0_.user_id as user_id3_2_2_, user1_.id as id1_1_0_, user1_.contact_details_id as contact_3_1_0_, user1_.name as name2_1_0_, contactdet2_.id as id1_0_1_, contactdet2_.email as email2_0_1_, contactdet2_.mobile as mobile3_0_1_ from user_details userdetail0_ left outer join user user1_ on userdetail0_.user_id=user1_.id left outer join contact_details contactdet2_ on user1_.contact_details_id=contactdet2_.id where userdetail0_.user_id=?
Hibernate: select contactdet0_.id as id1_0_0_, contactdet0_.email as email2_0_0_, contactdet0_.mobile as mobile3_0_0_ from contact_details contactdet0_ where contactdet0_.id=?
Hibernate: select userdetail0_.id as id1_2_2_, userdetail0_.nationality as national2_2_2_, userdetail0_.user_id as user_id3_2_2_, user1_.id as id1_1_0_, user1_.contact_details_id as contact_3_1_0_, user1_.name as name2_1_0_, contactdet2_.id as id1_0_1_, contactdet2_.email as email2_0_1_, contactdet2_.mobile as mobile3_0_1_ from user_details userdetail0_ left outer join user user1_ on userdetail0_.user_id=user1_.id left outer join contact_details contactdet2_ on user1_.contact_details_id=contactdet2_.id where userdetail0_.user_id=?
Hibernate: select userdetail0_.id as id1_2_2_, userdetail0_.nationality as national2_2_2_, userdetail0_.user_id as user_id3_2_2_, user1_.id as id1_1_0_, user1_.contact_details_id as contact_3_1_0_, user1_.name as name2_1_0_, contactdet2_.id as id1_0_1_, contactdet2_.email as email2_0_1_, contactdet2_.mobile as mobile3_0_1_ from user_details userdetail0_ left outer join user user1_ on userdetail0_.user_id=user1_.id left outer join contact_details contactdet2_ on user1_.contact_details_id=contactdet2_.id where userdetail0_.user_id=?

Any idea?

Thanks

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

The default fetch type for @OneToOne is FetchType.EAGER. Therefore, without any hints on how to optimise the query, Hibernate will follow these steps:

Select all User's:

Hibernate: 
select
    user0_.id as id1_1_,
    user0_.contact_details_id as contact_3_1_,
    user0_.name as name2_1_ 
from
    user user0_

Now eagerly load each User's ContactDetails and UserDetails

Hibernate: 
select
    contactdet0_.id as id1_0_0_,
    contactdet0_.email as email2_0_0_,
    contactdet0_.mobile as mobile3_0_0_ 
from
    contact_details contactdet0_ 
where
    contactdet0_.id=?
Hibernate: 
select
    userdetail0_.id as id1_2_2_,
    userdetail0_.nationality as national2_2_2_,
    userdetail0_.user_id as user_id3_2_2_,
    user1_.id as id1_1_0_,
    user1_.contact_details_id as contact_3_1_0_,
    user1_.name as name2_1_0_,
    contactdet2_.id as id1_0_1_,
    contactdet2_.email as email2_0_1_,
    contactdet2_.mobile as mobile3_0_1_ 
from
    user_details userdetail0_ 
left outer join
    user user1_ 
        on userdetail0_.user_id=user1_.id 
left outer join
    contact_details contactdet2_ 
        on user1_.contact_details_id=contactdet2_.id 
where
    userdetail0_.user_id=?

...

This is commonly referred to as the n + 1 problem. See here for example.

edited May 23 '17 at 12:14

Community

1
1

answered Apr 28 '15 at 09:52

Donovan Muller

3,822
3
30
54

Thanks for your answer @donovan. Are you saying that the **default** behaviour of Hibernate for queries that will return a List is **always** an initial `select * from ` and then perform the additional selects for the relations? – pVilaca Apr 28 '15 at 11:34
1

Yes, this is one of the reasons `EntityGraph` was [added to JPA 2.1](http://www.radcortez.com/jpa-entity-graphs). Without additional hints from the developer I guess it's difficult for Hibernate to optimise properly and therefore takes the simplest (but less efficient) option. – Donovan Muller Apr 28 '15 at 11:39
Plus, in the case of lazy loading, it wouldn't make sense to join so early on, considering that the lazy loaded properties might never be used. Which is why the way Hibernate does it by default is reasonable. – Donovan Muller Apr 28 '15 at 11:47
Thanks @donovan. I was just trying to find a link to the Hibernate documentation or JPA specification with the description of this behaviour but I'm not able to find it. But if this is the case for all multi-entry queries, I can accept your answer. – pVilaca Apr 28 '15 at 14:55

Understand different behaviours of Hibernate/JPA for findAll and findOne

1 Answers1