Too many queries problem with JPA + Hibernate even when using @Fetch(FetchMode.JOIN)

Question

I am developing REST application using spring boot and I am trying to optimize the performance of the queries. I am currently using findAll from the repositories which is causing performance issues. Code is given below:

Person Entity

@Entity
@Table(name = "cd_person")
@Data
@NoArgsConstructor
public class Person {
    ....
    @OneToOne(fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    @JoinColumn(name = "password_id")
    @Fetch(FetchMode.JOIN)
    private Password password;
    ....
    @ManyToMany(fetch = FetchType.EAGER, cascade = {CascadeType.MERGE, CascadeType.PERSIST, CascadeType.REFRESH})
    @JoinTable(name = "cd_person_role",
        joinColumns = @JoinColumn(name = "person_id", referencedColumnName = "id"),
        inverseJoinColumns = @JoinColumn(name = "role_id", referencedColumnName = "id"))
    @Fetch(FetchMode.JOIN)
    private Set<Role> roles = new HashSet<>();
}

Password Entity

@Entity
@Table(name = "cd_password")
@Data
@NoArgsConstructor
public class Password {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id", updatable = false, nullable = false)
    private Long id;

    @Column(name = "password_hash", nullable = false)
    private String passwordHash;
    .......
}

Role Entity

@Entity
@Table(name = "cd_role")
@Data
@NoArgsConstructor
public class Role {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @Column(name = "role_type")
    @Enumerated(EnumType.STRING)
    private RoleType roleType;
    ....
}

Person Repository

public interface PersonRepository extends CrudRepository<Person, Long> {

    Optional<Person> findByEmail(String email);

}

When I do a personRepository.findAll() there are select queries fired for each row in the person table to fetch the password and roles when I access the person. I know I can use @Query annotation with JOIN FETCH in the repository to make it force generate the single query but I was wondering if there was any other way to do so. I am looking for something which we can do at the entity level to reduce queries.

Using spring boot 2.1.5-RELEASE version and related dependencies.

PS. The @Data and @NoArgsConstructor are Lombok annotations.

score 9 · Accepted Answer · answered Aug 30 '19 at 23:31

9

The most minimal code change is to use the ad-hoc EntityGraph feature from spring data . Just override PersonRepository 's findAll() and use @EntityGraph to configure the graph. All entities in this graph will be fetched together.

public interface PersonRepository extends CrudRepository<Person, Long> {

    @EntityGraph(attributePaths = { "password", "roles" })
    public List<Person> findAll();

}

Behind scene it works like JOIN FETCH. Only single SQL with LEFT JOIN will be generated.

answered Aug 30 '19 at 23:31

Ken Chan

84,777
26
143
172

Any performance upside/downside of this over JOIN FETCH or is it just fewer lines of code? – koushikmln Sep 03 '19 at 11:07
good performance if you will loop through all the retrieved person and access its password and roles since all of them are already fetched by a single SQL select , no N+1 problem – Ken Chan Sep 03 '19 at 11:10
I was asking this in comparison to JOIN FETCH. Is it better to use @Query or define the entity graph? – koushikmln Sep 03 '19 at 11:24
they are the same . it generates the same SQL as the JOIN FETCH – Ken Chan Sep 03 '19 at 11:31
@KenChan what if there's already `@Query` annotation with custom join? When I use `@EntityGraph`, it indeed does left join in order to get both sides in one shot, but there's also an additional cross join because of `@Query` annotation. And I need this annotation in order to put "where" conditions. Is it possible to specify "where" conditions with `@EntityGraph` and get rid of that extra cross join and `@Query` annotation? – mangusta Jun 28 '22 at 09:00

score 3 · Answer 2 · answered Aug 29 '19 at 11:08

3

I would leave the Entity as is and override the findAll method in the repository with an @Query annotation. This way, the code refactor is minimal (only one repository change instead of an entity change).

answered Aug 29 '19 at 11:08

JohanB

2,068
1
15
15

score 2 · Answer 3 · edited Sep 04 '19 at 08:03

2

You should place @BatchSize on top of Password class

@Entity
@Table(name = "cd_password")
@Data
@NoArgsConstructor
@BatchSize(size = 50)
public class Password {
...
}

Here are the queries with @BatchSize:

Hibernate: 
    select
        person0_.id as id1_1_,
        person0_.password_id as password2_1_ 
    from
        cd_person person0_
Hibernate: 
    select
        password0_.id as id1_0_0_,
        password0_.password_hash as password2_0_0_ 
    from
        cd_password password0_ 
    where
        password0_.id in (
            ?, ?, ?, ?, ?
        )

edited Sep 04 '19 at 08:03

Rudy Vissers

5,267
4
35
37

answered Aug 28 '19 at 13:06

i.bondarenko

3,442
3
11
21

Thanks for your efforts but I would like to optimize it a bit further. Batch would be better but it's still not optimum. If you could provide any references to how batch performs better than join, I would do it and accept the answer. – koushikmln Aug 28 '19 at 13:38

score 2 · Answer 4 · answered Aug 30 '19 at 08:40

The unsatisfying answer to your question is: no, there's no way to annotate/configure the entities so that the fetch mode applies to a query as well.

As you correctly found yourself, you can manipulate the query itself. Alternatives to this are using Hibernate's fetch profiles or leveraging JPA entity graphs - but all of them require programmatic intervention at the query/session level as well.

score 0 · Answer 5 · answered Aug 28 '19 at 15:06

0

Can't you use lazy fetch and remove the @Fetch ? Using @NamedQuery on top of your entity and using an hibernate session to call session.createNamedQuery in a custom service would do it.

If you can afford to not use the default personRepository.findAll() but this custom service you would run an optimized query. I get that it does not exactly answer your question but my team and I faced the exact same issue and this is how we did it.

answered Aug 28 '19 at 15:06

apa

95
7

The only reason I am not changing repository logic is because I have to do a code refactor which is too much effort. I would have to probably do that if I dont have any other options. – koushikmln Aug 28 '19 at 15:21

score 0 · Answer 6 · answered Sep 04 '19 at 06:55

My suggestions would be:

Try to refactor and use lazy fetching.
I might not understand this part well, but why do you need personRepository.findAll() exactly? I think you would merely need something like personRepository.findById(), so you could fetch the roles and other data easily. Selecting all persons seems to be a huge overload here.
You might need the extended functions of JpaRepository later, so it might be worth changing it now instead of working a little bit more later.

score 0 · Answer 7 · edited Sep 04 '19 at 11:32

0

This should works:

public interface PersonRepository extends CrudRepository<Person, Long> {
     @Override
        @Query("SELECT p FROM Person p JOIN FETCH p.roles JOIN FETCH p.password ")
        Iterable<Person> findAll();
}

edited Sep 04 '19 at 11:32

PaulNUK

4,774
2
30
58

answered Sep 04 '19 at 11:19

H Pat

134
5

Too many queries problem with JPA + Hibernate even when using @Fetch(FetchMode.JOIN)

7 Answers7