Overview

Using object-relational mapping tools like Hibernate can significantly simplify and speed up the development process. For a developer, it’s easier to operate with object entities rather than switching to relational tables every time he needs to access a database. Nevertheless, it has its disadvantages. The main disadvantage is a high chance to ruin the performance if used without a good understanding of how it works under the hood. This tutorial covers the common problem called “N+1 select” and describes one of the solutions – JOIN FETCH.

N+1 select problem

Let’s imagine we have two entities with a one-to-many relationship: Order and OrderEntry. For some use-cases, we may want to load only Order-related attributes, but for other cases, we may need to load orders together with their entries.

Hibernate provides two fetch types: EAGER and LAZY. EAGER fetches everything, while LAZY fetches only requested entity and nested attributes only when they are accessed. One of the solutions which beginners usually start with is to set EAGER fetch type for a relationship, but it decreases performance because it forces loading of all relationships even when they are not needed. Also, using EAGER fetch type forces you to always use it. It can’t be overridden on a method level.

The next situation is when you already realize that EAGER fetch type works fine, but it doesn’t meet performance requirements and as a result, fetch type is turned back to LAZY. After that, you may try to load a list of parent entities (e.g. Orders) and iterate through them accessing their nested relationships (OrderEntries in our case). From the code perspective, it looks correct, but this is where “N+1 select” problem appears. Hibernate sends the following queries:

1. The first SQL query to get a list of Orders
2. Additional queries to get Order Entries by order (query per order).

As a result, we get N+1 select queries, where N is a number of Orders. Imagine N=1000 or 10_000 and you’ll understand how it would affect the performance.

JOIN FETCH solution

One of the solutions for “N+1 select” problem in Hibernate is a custom query with JOIN FETCH. Instead of querying nested order entries for every order with an additional query, we can make a single query to select orders and join corresponding entries. Using “JOIN FETCH” instructs Hibernate to populate nested entities.

Let’s have a look at entities and a Spring Repository with a custom method annotated with a custom JOIN FETCH query.

@Entity(name = "ShopOrder")
@Getter
@Setter
@NoArgsConstructor
public class Order implements Serializable {
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    @Column
    private double total;

    @OneToMany(mappedBy="order", fetch = FetchType.LAZY)
    private List<OrderEntry> entries;
    
}

@Entity
@Getter
@Setter
@NoArgsConstructor
public class OrderEntry implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    private Long id;
    
    @Column
    private String product;

    @ManyToOne
    @JoinColumn(name="order_id", nullable=false)
    private Order order;
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    @Query("select distinct o from ShopOrder o left join fetch o.entries oe")
    List<Order> findAllWithEntries();
    
}

As a result, Hibernate will generate the following single query:

select
        distinct order0_.id as id1_1_0_,
        entries1_.id as id1_0_1_,
        order0_.total as total2_1_0_,
        entries1_.order_id as order_id3_0_1_,
        entries1_.product as product2_0_1_,
        entries1_.order_id as order_id3_0_0__,
        entries1_.id as id1_0_0__ 
    from
        shop_order order0_ 
    left outer join
        order_entry entries1_ 
            on order0_.id=entries1_.order_id

Without using JOIN FETCH there would be N such SQL queries (query per order):

select
        entries0_.order_id as order_id3_0_0_,
        entries0_.id as id1_0_0_,
        entries0_.id as id1_0_1_,
        entries0_.order_id as order_id3_0_1_,
        entries0_.product as product2_0_1_ 
    from
        order_entry entries0_ 
    where
        entries0_.order_id=?

The resulting list will have nested order entries populated without executing additional queries.