Hibernate - N+1 Problem

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

EAGER and LAZY are the two fetch types provided by Hibernate. LAZY retrieves only the desired entity and nested attributes when they are accessed, whereas EAGER fetches everything. Setting the EAGER fetch type for a connection is a common option for novices, but it degrades efficiency because it requires the loading of all relationships, even if they aren't needed. Additionally, employing the EAGER fetch type forces you to utilize it all of the time. On a method level, it can't be overridden.

The next scenario is when you notice that while EAGER fetch type works OK, it does not fulfill performance needs, so fetch type is reverted to LAZY. After that, you may loop through a list of parent entities (for example, Products) and retrieve their hierarchical relationships. From the standpoint of the code, it appears to be correct, however this is where the "N+1 select" problem emerges. The following queries are sent by Hibernate:
To retrieve a list of Products, run the first SQL query.
Additional queries to retrieve Product Entries in a particular product (query per product).

As a result... 🔵🔴

N+1 select queries are generated, where N is the number of Products. Consider N=1000 or 10.000 to see how the performance will be affected. While a single query can do your job, 10000 queries also can do. Select your side.

Here comes the solution

A custom query with JOIN FETCH is one of the Hibernate solutions for the "N+1 select" problem: instead of querying nested product records for each product with a separate query, we may build a single query to select products and join relevant entries. Hibernate is told to populate nested entities when "JOIN FETCH" is used.

Consider entities and a Spring Repository with a custom method that is annotated with a custom JOIN FETCH query.

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {
    @Query("SELECT DISTINCT s FROM Products p LEFT JOIN FETCH p.entries pe")
    List<Product> findAllWithEntries();
}

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