How to Optimize an Inefficient N+1 Query Problem in Spring Boot

The N+1 query problem is a common issue that occurs when using JPA repositories in Spring Boot. It happens when you have a one-to-many or many-to-one relationship between entities, and you’re fetching the parent entity with its children. This results in a separate database query for each child, leading to inefficient performance.

Optimizing N+1 Query Problem in Spring Boot JPA Repository

Suppose we have two entities: Order and OrderItem. An order can have multiple items, and an item belongs to one order.

@Entity
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @OneToMany(mappedBy = "order")
    private List<OrderItem> items;
}

@Entity
public class OrderItem {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    @ManyToOne
    @JoinColumn(name = "order_id")
    private Order order;
}

N+1 Query Problem:

When you fetch an Order entity with its items, Spring Data JPA will execute a separate query for each item. This results in N+1 queries:

public interface OrderRepository extends JpaRepository<Order, Long> {
}

// In the service layer:
@Service
public class OrderService {
    @Autowired
    private OrderRepository orderRepository;
    
    public List<Order> getOrders() {
        return orderRepository.findAll();
    }
}

Optimization Techniques:

To avoid the N+1 query problem, you can use one of the following techniques:

1. Fetch Join

Use a fetch join to load related entities in a single query.

public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("SELECT o FROM Order o JOIN FETCH o.items")
    List<Order> getOrdersWithItems();
}

2. Batch Fetching

Use batch fetching to load related entities in batches.

public interface OrderRepository extends JpaRepository<Order, Long> {
    @Query("SELECT o FROM Order o")
    List<Order> getOrders();
    
    @Modifying
    @Query("UPDATE Order o SET o.items = :items WHERE o.id IN (:ids)")
    void updateOrdersWithItems(@Param("ids") List<Long> ids, @Param("items") List<OrderItem> items);
}

3. EntityGraph

Use an entity graph to define a fetch plan for related entities.

public interface OrderRepository extends JpaRepository<Order, Long> {
    @EntityGraph(attributePaths = {"items"})
    List<Order> getOrders();
}

4. Lazy Loading

Use lazy loading to load related entities on demand. This is the most ineficient and should generally never be used. It may be acceptable only if you know your domain and you know that these queries are like less then 5 and you cache their result

public interface OrderRepository extends JpaRepository<Order, Long> {
}

// In the service layer:
@Service
public class OrderService {
    @Autowired
    private OrderRepository orderRepository;
    
    public List<Order> getOrders() {
        return orderRepository.findAll();
    }
    
    public Order getOrder(Long id) {
        Order order = orderRepository.findById(id).orElse(null);
        if (order!= null &&!order.getItems().isEmpty()) {
            order.getItems().forEach(item -> item.getOrder()); // Load items on demand
        }
        return order;
    }
}

Conclusion:

The N+1 query problem is a common issue in Spring Boot JPA repositories. By using one of the optimization techniques mentioned above, you can improve performance and reduce the number of database queries. And when you move the models out of the Java Application server you most probably will need DTOs.