Hibernate N+1 problem

Context

Using @ManyToOne, @OneToOne annotation

FetchType.LAZY or Eager is get n+1. Lazy when call getXXX then will load/fetch data, Eager is always get data already.

The most implicit n+1 is to use library to copy properties BeanUtils.copyProperties or write your own code using reflection copy properties then all getter and all getter of nested object are called.

Solutions

An easy way to check hibernate n+1 that show query hibernate format_sql=true, example for Spring boot framework

spring.jpa.properties.hibernate.format_sql=true

Solution 1:

Using join load immediately in one query.

Advantages:

• Write code less, only declare annotation is done.
• Inner join.

Disadvantages:

• Many potential risks are out of control, cause always load join data. Adding more and more anotation @ManyToOne makes the entity bigger and bad performance.
• LEFT JOIN:
  • will be very slower when got a lot of data than separate another query
  • will be duplicated data, must use: DISTINCT.

In case, you’re already use left join for search and data (100,000) is very slow, you must create index foreign key.

table customer (id, firstname, lastname) got many addresses. table address (id, postcode, address, customer_id) <--- create index: customer_id

Solution 2:

Don’t using @ManyToOne or @OneToOne for Entity, Write code by hand to load data for model.

Advantages: Write your own code, load/get enought data to use, easy to custom and maintenance.

Disadvantages: Write more codes than (1).

Implement in coding for solution 1:

1. Join: using in query
2. Entity Graph (NamedEntityGraph): for a long time, these classes will be bigger and hard to maintain.

Implement in coding for solution 2:

• Using multiple queries