JPA Tips: Avoiding the N + 1 select problem

MD Sayem AhmedApril 24th, 2018Last Updated: April 24th, 2018

2 821 3 minutes read

Introduction

ORM frameworks like JPA simplifies our development process by helping us to avoid lots of boilerplate code during the object <-> relational data mapping. However, they also bring some additional problems to the table, and N + 1 is one of them. In this article we will take a short look at the problem along with some ways to avoid them.

The Problem

As an example I will use a simplified version of an online book ordering application. In such application I might create an entity like below to represent a Purchase Order –

@Entity
public class PurchaseOrder {

    @Id
    private String id;
    private String customerId;

    @OneToMany(cascade = ALL, fetch = EAGER)
    @JoinColumn(name = "purchase_order_id")
    private List<PurchaseOrderItem> purchaseOrderItems = new ArrayList<>();
}

A purchase order consists of an order id, a customer id, and one or more items that are being bought. The PurchaseOrderItem entity might have the following structure –

@Entity
public class PurchaseOrderItem {

    @Id
    private String id;

    private String bookId;
}

These entities have been simplified a lot, but for the purpose of this article, this will do.

Now suppose that we need to find the orders of a customer to display them in his purchase order history. The following query will serve this purpose –

SELECT
    P
FROM
    PurchaseOrder P
WHERE
    P.customerId = :customerId

which when translated to SQL looks something like below –

select
    purchaseor0_.id as id1_1_,
    purchaseor0_.customer_id as customer2_1_ 
from
    purchase_order purchaseor0_ 
where
    purchaseor0_.customer_id = ?

This one query will return all purchase orders that a customer has. However, in order to fetch the order items, JPA will issue separate queries for each individual order. If, for example, a customer has 5 orders, then JPA will issue 5 additional queries to fetch the order items included in those orders. This is basically known as the N + 1 problem – 1 query to fetch all N purchase orders, and N queries to fetch all order items.

This behavior creates a scalability problem for us when our data grows. Even a moderate number of orders and items can create significant performance issues.

The Solution

Avoiding Eager Fetching

This the main reason behind the issue. We should get rid of all the eager fetching from our mapping. They have almost no benefits that justify their use in a production-grade application. We should mark all relationships as Lazy instead.

One important point to note – marking a relationship mapping as Lazy does not guarantee that the underlying persistent provider will also treat it as such. The JPA specification does not guarantee the lazy fetch. It’s a hint to the persistent provider at best. However, considering Hibernate, I have never seen it doing otherwise.

Only fetching the data that are actually needed

This is always recommended regardless of the decision to go for eager/lazy fetching.

I remember one N + 1 optimization that I did which improved the maximum response time of a REST endpoint from 17 minutes to 1.5 seconds. The endpoint was fetching a single entity based on some criteria, which for our current example will be something along the line of –

TypedQuery<PurchaseOrder> jpaQuery = entityManager.createQuery("SELECT P FROM PurchaseOrder P WHERE P.customerId = :customerId", PurchaseOrder.class);
jpaQuery.setParameter("customerId", "Sayem");
PurchaseOrder purchaseOrder = jpaQuery.getSingleResult();

// after some calculation
anotherRepository.findSomeStuff(purchaseOrder.getId());

The id is the only data from the result that was needed for subsequent calculations.

There were a few customers who had more than a thousand orders. Each one of the orders in turn had a few thousands additional children of a few different types. Needless to say, as a result, thousands of queries were being executed in the database whenever requests for those orders were received at this endpoint.
To improve the performance, all I did was –

TypedQuery<String> jpaQuery = entityManager.createQuery("SELECT P.id FROM PurchaseOrder P WHERE P.customerId = :customerId", String.class);
jpaQuery.setParameter("customerId", "Sayem");
String orderId = jpaQuery.getSingleResult();

// after some calculation
anotherRepository.findSomeStuff(orderId);

Just this change resulted in a 680x improvement.
If we want to fetch more than one properties, then we can make use of the Constructor Expression that JPA provides –

"SELECT " +
"NEW com.codesod.example.jpa.nplusone.dto.PurchaseOrderDTO(P.id, P.orderDate) " +
"FROM " +
"PurchaseOrder P " +
"WHERE " +
"P.customerId = :customerId",
PurchaseOrderDTO.class);
jpaQuery.setParameter("customerId", "Sayem");
List<PurchaseOrderDTO> orders = jpaQuery.getResultList();

A few caveats of using the constructor expression –

The target DTO must have a constructor whose parameter list match the columns being seleected
The fully qualified name of the DTO class must be specified

Useing Join Fetch / Entity Graphs

We can use JOIN FETCH in our queries whenever we need to fetch an entity with all of its children at the same time. This results in a much less database traffic resulting in an improved performance.

JPA 2.1 specification introduced Entity Graphs which allows us to create static/dynamic query load plans.
Thorben Janssen has written a couple of posts (here and here) detailing their usage which are worth checking out.
Some example code for this post can be found at Github.

Published on Java Code Geeks with permission by Sayem Ahmed, partner at our JCG program. See the original article here: JPA Tips: Avoiding the N + 1 select problem

Opinions expressed by Java Code Geeks contributors are their own.