Does OneToMany(fetch=FetchType.EAGER) executes N+1 queries

Question

I am developing an application by using spring boot, spring data and Hibernate. I have an entity "Client" that has a relationship "onetomany" with another entity "Reservation", as follows:

@Entity
public class Client implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @OneToMany( mappedBy = "client", fetch=FetchType.EAGER)
    @Fetch(value = FetchMode.JOIN)
    private Set<Reservation> reservations = new HashSet<>();
}

I have implemented JpaRepository inteface so I can manipulate the client data:

public interface ClientRepository extends JpaRepository<Client, Long> {}

In a service class, I have implemented a method to find all the clients in my Database:

@Service
public class ClientService {

@Autowired
private ClientRepository clientRepository;

@Transactional(readOnly = true)
public List<Client> findAll() {

    return clientRepository.findAll();
}}

The execution of findAll() works well and returns all the clients and their reservations. However, im my sql log, I have N+1 queries that have been executed (N the count of clients), although that I have set fetch=FetchType.EAGER in my Client entity. I thought that hibernate will create a single query in which it joins all necessary data to retreive the clients and their reseravations.

So, I am forced to explicitly join client and reservation with a query:

    @Query("select client from Client client left join fetch client.reservations")
public List<Client> findAllEager();

I have also found another alternative which permits to execute only two queries to retrieve all the clients and their reservations:

    @OneToMany(mappedBy = "client", fetch = FetchType.EAGER)
@Fetch(value = FetchMode.SUBSELECT)

I note that from this discussion JPA eager fetch does not join, it appears that @OneToMany(fetch=FetchType.EAGER) @Fetch(value = FetchMode.JOIN) will execute only one query to retrieve the result which is not my case.

Does OneToMany(fetch=FetchType.EAGER) executes N+1 queries to fetch data?

score 12 · Accepted Answer · answered Dec 27 '18 at 03:09

12

@OneToMany(fetch=FetchType.EAGER) only defines when the related entities are fetched , it does not defines how the entities are fetched. (i.e whether they are fetched by a single joined SQL or many separated SQL) ,so it may execute N+1 queries.

@Fetch(value = FetchMode.JOIN) defines how the related entities are fetched However, it only works when the entities are fetched directly using ID (i.e through entityManager.find(Client.class, 100) but will have no effect on JPQL query or Criteria API. (As said by Hibernate user guide as below ):

The reason why we are not using a JPQL query to fetch multiple Department entities is because the FetchMode.JOIN strategy would be overridden by the query fetching directive.

To fetch multiple relationships with a JPQL query, the JOIN FETCH directive must be used instead.

Therefore, FetchMode.JOIN is useful for when entities are fetched directly, via their identifier or natural-id.

Spring data internally use Criteria API for the findAll() query , so @Fetch(value = FetchMode.JOIN) will not have any effect. You have to explicitly use the JOIN FETCH query to avoid the N+1 queries.

answered Dec 27 '18 at 03:09

Ken Chan

84,777
26
143
172

1

In your opinion, why not always using @Fetch(value = FetchMode.SUBSELECT) instead of @Fetch(value = FetchMode.JOIN), since the former one permits to execute only two queries to retrieve data instead of N+1 queries which it is better in performance in most cases? Thus, why is it not the default option that should be set by Hibernate? Also, for this reason, why not using @Fetch(value = FetchMode.SUBSELECT) rather than JOIN FETCH query (that executes only one query) since it is very simple to implement and provide good performances too? – Akram KAMMOUN Dec 27 '18 at 11:21
If we always use @Fetch(value = FetchMode.SUBSELECT), we lose a little flexibility as the related records will always be fetched even we do not want to fetch them in some use-case. But using `JOIN FETCH query` , we can more flexibility to decide how the query look like depending on different use-cases. – Ken Chan Dec 27 '18 at 16:51
1

@AkramKAMMOUN one reason to not use SUBSELECT is having a complex query twice. Let's say you need to find all clients who have at least 3 reservations, but you want only top 10 clients of those ordered by last reservation date and the reservation dates must be at least 1 month apart. That obscure usecase will make a long complex query, and when selecting reservations, the subquery for finding clients again, will recreate that complex query again. – Madis Mar 25 '19 at 13:18
@KenChan JOIN FETCH should also be avoided if you want to use LIMIT (e.g pagination, or just first n results). Use either `@BatchSize` or entity graphs. – Madis Mar 25 '19 at 13:25
1

Many thanks!!. Indeed, I believe one of the problems of SUBSELECT is performance as you have mentioned. In my work, I am using either @BatchSize or entity graphs. – Akram KAMMOUN Mar 26 '19 at 19:10
see also https://stackoverflow.com/a/54528693/7095884 – olivmir Aug 22 '22 at 10:34

Does OneToMany(fetch=FetchType.EAGER) executes N+1 queries

1 Answers1

Linked