JPA Hibernate N+1 Query Problem

Introduction

The N+1 Query problem refers to the inefficient way of loading entities and their relations. In a nutshell, a query (1) is issued and results returned but because of the relation of the entity, further queries (N) are invoked (hence N+1). In JPA (with hibernate) the N+1 issue can be triggered by both lazily and eagerly loaded relations.

The accompanying code is configured to log the generated SQL statements to the console. It has various tests with validations.

There is also a tutorial on enabling SQL logs.

The Model

We have a product that has multiple variants (ProductVariant) and multiple reviews (Review).

The relationship to ProductVariant is a OneToMany relation that is eagerly loaded.

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "product", fetch = FetchType.EAGER)
    private Set<ProductVariant> productVariants;

The relationship to Review is a lazily loaded OneToMany relation.

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "product", fetch = FetchType.LAZY)
    private List<Review> reviews;

The reason we have these two relations is to demonstrate the N+1 problem when we have an “eager” relation as well as when we have a “lazy” relation.

We also have the spring repo as depicted below

public interface ProductRepo extends JpaRepository<Product, Long> {    
}

N+1 Demo

We will demonstrate the N+1 queries for both eager and lazy fetch types.

Eager Fetch

Below is a test that will trigger N+1 queries.

    @Test
    void whenXProductsCreatedWithYVariantsThenAllProductsFetched_expectXPlus1Selects() {
        final int prodCount = 10;
        final int expectedProdVariantInvocations = prodCount;

        List<Product> products = new ArrayList<>();
        for (int i = 0; i < prodCount; i++) {
            products.add(createProduct());
        }
        
        productService.saveProducts(products);

        final Statement statement = SqlStatementHolder.initStatement();

        final List<Product> fetchedProds = productService.fetchAllProds();

        //assert N+1 selections
        statement.assertThatSelect().hasCount(expectedProdVariantInvocations + 1);

        statement.assertThatSelect().executedOnTable(CountStrategyFactory.once(),"product");
        statement.assertThatSelect().executedOnTable(CountStrategyFactory.times(expectedProdVariantInvocations), "product_variant");
        assertThat(fetchedProds).hasSize(prodCount);
    }

Above, in lines 6-11, ten products are created and saved to the database. In line 15, we fetch the 10 products. The effect of the test is that 1 select is made on the product table and N (10) select statements are made on the product_variant table. So in total we have 11 select statements that are made to get 10 products from the database.

Let’s see the case where we fetch productVariants.

    @Test
    void whenXProductsCreatedWithYVariantsThenAllProductVariantsFetched_expectXPlus1Selects() {
        List<Product> products = new ArrayList<>();
        for (int i = 0; i < 10; i++) {
            products.add(createProduct());
        }
        productService.saveProducts(products);

        final Statement statement = SqlStatementHolder.initStatement();

        //join done for each unique product_id got from product_variant table. (here there is 10 unique ids)
        productService.fetchAllProdVariants();

        statement.assertThatSelect().hasCount(11);
        statement.assertThatSelect().executedOnJoinTables(CountStrategyFactory.times(10), "product", "product_variant");
        statement.assertThatSelect().executedOnTable(CountStrategyFactory.once(), "product_variant");
    }

Above, 10 products are created (Each product is created with at least 1 productVariant). In line 12, all productVariants are fetched. So at least 10 productVariants are fetched in the primary query, leading to 10 (N) secondary queries since you get 10 unique product ids.

Now let’s see the case where you will not have the N+1 select issue when the relationship is eagerly fetched.

    @Test
    void whenUsingFindById_expectTableJoin() {
        final Product product = createProduct();
        final Product savedProduct = productService.saveProduct(product);

        final Statement statement = SqlStatementHolder.initStatement();

        //under the hood is the findById query (of the entityManager) which contains a JOIN FETCH directive to the productVariant
        productService.fetchProduct(savedProduct.getId());

        //join used when single product is fetched
        //"select p1_0.id,p1_0.business_id,p1_0.name,pv1_0.product_id,pv1_0.id,pv1_0.name,pv1_0.sku from product p1_0 left join product_variant pv1_0 on p1_0.id=pv1_0.product_id where p1_0.id=?" (1X)
        statement.assertThatSelect().executedOnJoinTables(CountStrategyFactory.once(), "product", "product_variant");
        statement.assertThatSelect().hasCount(1);
    }

Above, in line 9, the product is fetched by id leading to a single join statement. Under the hood, the findById query (of the entityManager) is used, which contains a JOIN FETCH directive to the productVariant.

Unlike the findById method, if we use the method below to fetch our product, we will get 2 queries (1 on product and the second on product_variant) instead of 1 with a join.

public interface ProductRepo extends JpaRepository<Product, Long> {
    @Query("select p from Product p where p.id = :id")
    Optional<Product> getProductById(Long id);

}

Lazy Fetch

For lazily loaded relations, hibernate will not fetch the related entity so long as it is not explicitly accessed within an open transaction.

    @Test
    void whenLazilyLoadedAssociationAccessed_expectNPlus1Selects() {
        final int prodCount = 10;

        List<Product> products = new ArrayList<>();
        for (int i = 0; i < prodCount; i++) {
            products.add(createProduct());
        }
        productService.saveProducts(products);

        //prepare to capture SQL statements
        final Statement statement = SqlStatementHolder.initStatement();

        //fetch and access within a transaction
        template.execute((status) -> {
            final List<Product> fetchedProds = productService.fetchAllProds();
            // Since the transaction is active queries will be made to the database. If you do the below out of a transaction, you get a lazy exception thrown
            //this is more or less what is going to happen if you have a mapper (to convert to a DTO) or if product is converted to json
            fetchedProds.forEach(product -> product.getReviews().get(0));
            return 0;
        });

In the code above, in line 19, the child relation (reviews) is accessed within a transaction, leading to a database query each time the child collection is accessed. 10 fetched products will eventually lead to 10 (N) additional queries being issued. It is important to note that the code above does the same thing that a DTO mapper or a json mapper will do if the transaction is active. If the transaction is not active and the mapper does the mapping, a LazyInitializationException will be thrown once the mapper attempts to map the relation with the “lazy” fetch type.

It is important to note that the findById method will not generate a “join” if the relation fetch type is “lazy”.

Avoiding the N+1 query problem

The first way to avoid the problem is to annotate the relation to have a lazy fetch type. If you do not intend loading the relation then explicitly check if the relation is loaded before mapping it. There is the PersistenceUnitUtilImpl class of hibernate that can be used to verify if a relation is loaded or not without triggering a load.

If you intend to load the relation in some calls and not load in others, mark the relation as lazy then on a case by case basis, add the JOIN FETCH directive.

public interface ProductRepo extends JpaRepository<Product, Long> {
    @Override
    @EntityGraph(type = EntityGraph.EntityGraphType.FETCH, attributePaths = {"productVariants"})
    List<Product> findAllById(Iterable<Long> ids);

}

Above the default findAllById is overriden so that a “join” is performed. A custom method could also be created. The “join” will be generated regardless of the relation fetch type.

Conclusion

We can now establish some facts about the N+1 query problem.

It occurs with select queries
It occurs because of entity relationships
The N+1 issue can occur for lazy as well as eager fetch types
The fewer the unique parents fetched, the lower the value of N
It can occur when either side of the relation is fetched

The accompanying code has several tests and validations that help understand the N+1 query problem.

Code Major