Introduction

The N+1 problem in Hibernate is a common performance issue that occurs when an application executes excessive database queries due to improper handling of relationships. This can severely degrade application performance, especially when dealing with large datasets.

Understanding the N+1 Problem

What is the N+1 Problem?

The N+1 problem occurs when Hibernate retrieves a list of parent entities, but then issues an additional query for each related child entity. This results in N+1 queries, where N is the number of parent entities.

Example Scenario

Consider two entities: Author and Book. Each Author has multiple Book entries.

@Entity
@Table(name = "authors")
data class Author(
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Int,

    val name: String,

    @OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
    val books: List<Book> = emptyList()
)

@Entity
@Table(name = "books")
data class Book(
    @Id @GeneratedValue(strategy = GenerationType.IDENTITY)
    val id: Int,

    val title: String,

    @ManyToOne
    @JoinColumn(name = "author_id")
    val author: Author
)

How the N+1 Problem Occurs

If we retrieve all authors using Hibernate:

val authors = entityManager.createQuery("SELECT a FROM Author a", Author::class.java).resultList

for (author in authors) {
    println("Author: ${author.name}, Books: ${author.books.size}")
}

Here’s what happens:

• Hibernate executes one query to fetch all authors:

SELECT * FROM authors;

• For each author, it runs an additional query to fetch their books:

SELECT * FROM books WHERE author_id = ?;

This happens N times (once for each author), leading to N+1 queries.

Solutions to the N+1 Problem

Using JOIN FETCH (Recommended Approach)

We can use JOIN FETCH to fetch both authors and books in a single query:

val authors = entityManager.createQuery("SELECT a FROM Author a JOIN FETCH a.books", Author::class.java).resultList

Generated SQL:

SELECT a.*, b.* FROM authors a JOIN books b ON a.id = b.author_id;

This eliminates the N+1 problem by loading the related entities in a single query.

Using EntityGraph (Dynamic Fetching)

EntityGraph allows selective fetching of relationships at runtime:

val entityGraph = entityManager.createEntityGraph(Author::class.java)
entityGraph.addSubgraph("books")

val query = entityManager.createQuery("SELECT a FROM Author a", Author::class.java)
query.setHint("javax.persistence.fetchgraph", entityGraph)

val authors = query.resultList

Batch Size Optimization with @BatchSize

Another approach is to use @BatchSize, which loads related entities in batches:

@OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
@BatchSize(size = 10)
val books: List<Book> = emptyList()

This reduces the number of queries by batching related entities together.

Using @Fetch(FetchMode.SUBSELECT)

For OneToMany relationships, FetchMode.SUBSELECT loads related entities in a single query per batch:

@OneToMany(mappedBy = "author", fetch = FetchType.LAZY)
@Fetch(FetchMode.SUBSELECT)
val books: List<Book> = emptyList()

This generates one query per batch instead of N queries.