What is the N+1 select problem in JPA?

The N+1 select problem is a common performance anti-pattern in object-relational mapping (ORM) frameworks like JPA and Hibernate. It occurs when an application retrieves a collection of parent entities and then, for each parent, separately fetches its associated child entities, leading to an excessive number of database queries.

What is the N+1 Select Problem?

The problem name 'N+1' refers to the number of database queries executed. It consists of one query to retrieve the 'N' primary entities (e.g., a list of Authors) and then 'N' additional queries, one for each of those primary entities, to fetch their associated related entities (e.g., the books for each Author).

This pattern leads to significant performance degradation, especially with a large number of 'N' entities, due to increased network round trips between the application and the database, and the overhead of executing many small queries instead of a few optimized ones.

Example Scenario

Consider two entities, Author and Book, with a one-to-many relationship where an Author can have multiple Books.

import jakarta.persistence.*;
import java.util.List;
import java.util.ArrayList;

@Entity
public class Author {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;

    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    private List<Book> books = new ArrayList<>();

    // Getters and Setters
    public Author() {}
    public Author(String name) { this.name = name; }
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getName() { return name; }
    public void setName(String name) { this.name = name; }
    public List<Book> getBooks() { return books; }
    public void setBooks(List<Book> books) { this.books = books; }
}

@Entity
public class Book {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String title;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "author_id")
    private Author author;

    // Getters and Setters
    public Book() {}
    public Book(String title, Author author) { this.title = title; this.author = author; }
    public Long getId() { return id; }
    public void setId(Long id) { this.id = id; }
    public String getTitle() { return title; }
    public void setTitle(String title) { this.title = title; }
    public Author getAuthor() { return author; }
    public void setAuthor(Author author) { this.author = author; }
}

If we retrieve a list of authors and then iterate through them to access their books (assuming FetchType.LAZY for the books collection, which is the default for @OneToMany), the N+1 problem will occur:

List<Author> authors = entityManager.createQuery("SELECT a FROM Author a", Author.class).getResultList();

for (Author author : authors) {
    System.out.println("Author: " + author.getName());
    // Accessing books will trigger a new query for each author
    for (Book book : author.getBooks()) { // LAZY loading triggers a query here
        System.out.println("  Book: " + book.getTitle());
    }
}

The Problematic Queries

• One query to fetch all authors: SELECT * FROM Author;
• For each author (N authors), one separate query to fetch their books: SELECT * FROM Book WHERE author_id = ?;

If there are 100 authors, this results in 1 (for authors) + 100 (for each author's books) = 101 database queries. This is highly inefficient compared to fetching all necessary data in a single or a few optimized queries.

How to Solve the N+1 Problem

Several strategies can be employed to mitigate or eliminate the N+1 select problem, primarily by eagerly fetching the related entities in a single query or a reduced number of queries.

1. Using FetchType.EAGER (Carefully)

Changing the fetch type of the relationship to EAGER will make JPA fetch the associated entities immediately. However, this often leads to a Cartesian product in the SQL query if not combined with distinct, and can eagerly load data even when not needed, potentially causing performance issues elsewhere. It's generally not recommended for collections unless the collection is small and always needed.

@OneToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.EAGER)
private List<Book> books = new ArrayList<>();

2. Using JOIN FETCH in JPQL/Criteria API

This is often the preferred and most flexible solution. By using JOIN FETCH in a JPQL (Java Persistence Query Language) or Criteria API query, you explicitly instruct JPA to fetch the related collection in the same query as the parent entities, typically using an SQL LEFT JOIN.

List<Author> authors = entityManager.createQuery(
    "SELECT DISTINCT a FROM Author a JOIN FETCH a.books", Author.class
).getResultList();

// Now, accessing author.getBooks() will not trigger additional queries.

3. Using EntityGraph

JPA 2.1 introduced EntityGraph, allowing you to define a graph of entities and their associated relationships to be fetched. This provides a declarative way to specify fetching strategies for specific operations without modifying the default fetch types on the entity mappings.

@NamedEntityGraph(
    name = "author-with-books-graph",
    attributeNodes = @NamedAttributeNode("books")
)
@Entity
public class Author { /* ... */ }

// In a Spring Data JPA repository:
public interface AuthorRepository extends JpaRepository<Author, Long> {
    @EntityGraph(value = "author-with-books-graph", type = EntityGraph.EntityGraphType.FETCH)
    List<Author> findAll();
}

4. Batch Fetching (@BatchSize Annotation)

Instead of executing one query per child collection (N queries), Hibernate (and other JPA implementations) can be configured to fetch collections in batches. The @BatchSize annotation on the relationship tells Hibernate to fetch batchSize number of collections in a single query using an IN clause, reducing the number of queries from N to N/batchSize (rounded up).

import org.hibernate.annotations.BatchSize;

@Entity
public class Author {
    // ...
    @OneToMany(mappedBy = "author", cascade = CascadeType.ALL, fetch = FetchType.LAZY)
    @BatchSize(size = 10)
    private List<Book> books = new ArrayList<>();
    // ...
}

Summary

The N+1 select problem is a critical performance bottleneck in JPA applications that can be easily overlooked. Understanding its causes and applying appropriate fetching strategies like JOIN FETCH, EntityGraph, or BatchSize is essential for developing performant and scalable data-driven applications.