12

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.

Improve this question
0

7 Answers 7

Reset to default
11
+50

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Any performance upside/downside of this over JOIN FETCH or is it just fewer lines of code?
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 👍 👍
I was asking this in comparison to JOIN FETCH. Is it better to use @Query or define the entity graph?
they are the same . it generates the same SQL as the JOIN FETCH
@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?
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).

Improve this answer

Comments

2

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.

Improve this answer

Comments

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 (
            ?, ?, ?, ?, ?
        )
Improve this answer

1 Comment

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.
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.

Improve this answer

1 Comment

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.
0

My suggestions would be:

  1. Try to refactor and use lazy fetching.
  2. 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.
  3. 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.
Improve this answer

Comments

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();
}
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.