N+1 query issue and its solution in Hibernate

Hibernate is one of the most popular frameworks which make the work on accessing databases easier. Any developer who uses this framework must be aware of the most popular problem that might occur – generating excessive base queries, one of the most common n+1 issues. This problem is of great importance, and should not be overlooked, as ignoring it often leads to drastic optimalization difficulties. 

N+1 issue refers to an excessive amount of queries, which are not only too complex, but also not necessary. Hibernate, for instance, can generate a much smaller number of less complicated queries instead.

N+1 query issue in praxis

We can assume that the n+1 issue is an anti-pattern. It is an intrusive action, which negatively affects the optimalization. This situation can be observed while we create multiple smaller SQL queries instead of one big query. 

Let us focus on an example:

Entity MeasureDetails 

@Entity(name = "measure_details")
public class MeasureDetails {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(nullable = false)
    private Long id;
    @ManyToOne
    @JoinColumn(nullable = false)
    private MeasurePoints point;

In this example we shall use a method which was implemented by JPARepository interface:

@NoRepositoryBean
public interface JpaRepository<T, ID> extends PagingAndSortingRepository<T, ID>, QueryByExampleExecutor<T> {
    List<T> findAll();

    List<T> findAll(Sort sort);
... }

Let’s create a method, which iterates on all the entities in the MeasureDetails table. The next step is to view the fields of the id MeasurePoints chart.

@PostConstruct
private void testGetAll() {
    List<MeasureDetails> measureDetailsList = measureDetailsRepository.findAll();
    measureDetailsList.forEach(details -> System.out.println(details.getPoint().getId()));
}

For the testing purposes I have used the Spring annotation @PostConstruct – in that manner we can be sure that the method will only be evoked once, right after the initiating of the beans.

Instead of forming one giant query, Hibernate only creates one for each iteration. Let us take a look at the logs now. 

Hibernate: select measuredet0_.id as id1_1_, measuredet0_.address_instance_case_id as address_2_1_, measuredet0_.details as details3_1_, measuredet0_.object_id as object_i4_1_, measuredet0_.point_id as point_id7_1_, measuredet0_.rows as rows5_1_, measuredet0_.timestamp as timestam6_1_ from measure_details measuredet0_

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?

Hibernate: select measurepoi0_.id as id1_2_0_, measurepoi0_.details as details2_2_0_, measurepoi0_.module as module3_2_0_, measurepoi0_.name as name4_2_0_, measurepoi0_.previous as previous5_2_0_, measurepoi0_.type as type6_2_0_ from measure_points measurepoi0_ where measurepoi0_.id=?….

First, you should enable hibernate queries logging. It’s different whether you are using yaml or properties file extension.

jpa: show-sql: true or: spring.jpa.show-sql=true

N+1 queries in praxis – what are the possible solutions?

Let us now focus on the possibilities of solving this problem in praxis. The most popular solution would be writing out own SQL query, which could retrieveall our records at once.

@Repository
public interface MeasureDetailsRepository extends JpaRepository<MeasureDetails, Long> {
    @Query("SELECT m FROM measure_details m LEFT JOIN FETCH m.point")
    List<MeasureDetails> findAllOwnQuery();

}

Next let us execute our custom statement:

@PostConstruct
private void testGetAll() {
    List<MeasureDetails> measureDetailsList = measureDetailsRepository.findAllOwnQuery();
    measureDetailsList.forEach(details -> System.out.println(details.getPoint().getId()));
}

Let us turn our focus to the query now. Hibernate has reacted as expected: instead of forming a query for each iteration, it has created one giant SELECT query.

Hibernate: select measuredet0_.id as id1_1_0_, measurepoi1_.id as id1_2_1_, measuredet0_.address_instance_case_id as address_2_1_0_, measuredet0_.details as details3_1_0_, measuredet0_.object_id as object_i4_1_0_, measuredet0_.point_id as point_id7_1_0_, measuredet0_.rows as rows5_1_0_, measuredet0_.timestamp as timestam6_1_0_, measurepoi1_.details as details2_2_1_, measurepoi1_.module as module3_2_1_, measurepoi1_.name as name4_2_1_, measurepoi1_.previous as previous5_2_1_, measurepoi1_.type as type6_2_1_ from measure_details measuredet0_ left outer join measure_points measurepoi1_ on measuredet0_.point_id=measurepoi1_.id

Eager and Lazy

While discussing this topic we should also mention FetchType – a solution, in which Hibernate fetch the relation entities.

There are two options for us to choose from. The first one is FetchType.LAZY , which only downloads data when it is needed. 

On our example:

details.getPoint().getId())

This will lead to evoking getter on the Point object instance. In this case, Hibernate will not ask us for the child object.

The other type is FetchType.EAGER – it loading data every time when the parent object is fetched. For example, fetch details will be followed by also fetching Point entities.