Skip to main content

JPA Tips: Avoiding the N + 1 select problem


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 -

public class PurchaseOrder {

    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 -

public class PurchaseOrderItem {

    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 -

    PurchaseOrder P
    P.customerId = :customerId

which when translated to SQL looks something like below -

select as id1_1_,
    purchaseor0_.customer_id as customer2_1_ 
    purchase_order purchaseor0_ 
    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

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 FROM PurchaseOrder P WHERE P.customerId = :customerId", String.class);
jpaQuery.setParameter("customerId", "Sayem");
String orderId = jpaQuery.getSingleResult();

// after some calculation

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 -

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

A few caveats of using the constructor expression -
  1. The target DTO must have a constructor whose parameter list match the columns being seleected
  2. 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.


Popular posts from this blog

Dealing with Java's LocalDateTime in JPA

A few days ago I ran into a problem while dealing with a LocalDateTime attribute in JPA. In this blog post I will try to create a sample problem to explain the issue, along with the solution that I used.
Consider the following entity, which models an Employee of a certain company -
@Entity @Getter @Setter public class Employee { @Id @GeneratedValue private Long id; private String name; private String department; private LocalDateTime joiningDate; }
I was using Spring Data JPA, so created the following repository -
@Repository public interface EmployeeRepository extends JpaRepository<Employee, Long> { }
I wanted to find all employees who have joined the company at a particular date. To do that I extended my repository from JpaSpecificationExecutor - @Repository public interface EmployeeRepository extends JpaRepository<Employee, Long>, JpaSpecificationExecutor<Employee> { }
and wrote a query like below -
@SpringBootTest @RunWith(SpringRunne…

Clean Code from the trenches - Validation

Let's directly start with an example. Consider a simple web service which allows clients to place order to a shop. A very simplified version of the order controller could look something like below -

@RestController @RequestMapping(value = "/", consumes = MediaType.APPLICATION_JSON_VALUE, produces = MediaType.APPLICATION_JSON_VALUE) public class OrderController { private final OrderService orderService; public OrderController(OrderService orderService) { this.orderService = orderService; } @PostMapping public void doSomething(@Valid @RequestBody OrderDTO order) { orderService.createOrder(order); } }
And the corresponding DTO class -

@Getter @Setter @ToString public class OrderDTO { @NotNull private String customerId; @NotNull @Size(min = 1) private List<OrderItem> orderItems; @Getter @Setter @ToString public static class OrderItem { private String menuId; private String description; private String pric…

Java Tips: Creating a Monitoring-friendly ExecutorService

In this article we will be extending an ExecutorService implementation with monitoring capabilities. This monitoring capability will help us to measure a number of pool parameters i.e., active threads, work queue size etc. in a live production environment. It will also enable us to measure task execution time, successful tasks count, and failed tasks count.

Monitoring Library As for the monitoring library we will be using Metrics. For the sake of simplicity we will be using a ConsoleReporter which will report our metrics to the console. For production-grade applications, we should use an advanced reporter (i.e., Graphite reporter). If you are unfamiliar with Metrics, then I recommend you to go through the getting started guide.

Let's get started.

Extending the ThreadPoolExecutor We will be using ThreadPoolExecutor as the base class for our new type. Let's call it MonitoredThreadPoolExecutor. This class will accept a MetricRegistry as one of its constructor parameters -

public …