The N+1 Select Problem in Spring Data JPA

The N+1 select problem is one of the most common performance problems in Spring Data JPA. It is also one of the easiest to accidentally introduce because the code often looks perfectly innocent. Software has a remarkable talent for looking elegant while quietly asking the database to run a marathon.

What is the N+1 select problem?

Suppose you want to load 100 orders.

Spring Data JPA runs:

SELECT * FROM orders;

So far, so good. That's 1 query.

Later, your code accesses the customer for each order:

for (Order order : orders) {
    System.out.println(order.getCustomer().getName());
}

Hibernate (the library that translates Java objects into database queries) now executes:

SELECT * FROM customer WHERE id = 1;
SELECT * FROM customer WHERE id = 2;
SELECT * FROM customer WHERE id = 3;
...

One query is executed for every order.

Total:

• 1 query for all orders
• 100 additional queries for customers

That is 101 queries, often written as N+1.

Why does it happen?

Spring Data JPA loads only the main object first.

Related objects (for example, Customer, Items, or Address) are often loaded lazily (only when actually used).

This saves work if you never need the related data.

However, if you eventually access every related object, Hibernate issues another SQL query for each one.

The result is many small database calls instead of one efficient query.

Why is it bad?

The database usually executes individual queries quickly.

The expensive part is the repeated communication between the application and the database.

For example:

• 1 query might take 10 ms.
• 101 queries might take 400 ms or more.

The database isn't necessarily slow.

Your application simply keeps knocking on its door hundreds of times.

How do you detect it in production?

The hardest part about N+1 problems is that they rarely cause errors.

Everything works.

It just becomes slower as more data appears.

Good production detection usually combines several techniques.

1. Monitor SQL query counts

For a single HTTP request (the work done to handle one API call), check how many SQL statements were executed.

If one endpoint suddenly executes:

• 5 queries yesterday
• 250 queries today

there is a good chance an N+1 problem was introduced.

2. Use Application Performance Monitoring (APM)

APM (software that monitors application performance) tools like New Relic, Datadog, Dynatrace, or Elastic APM can show:

• slow requests
• database time
• number of SQL queries
• repeated SQL statements

Repeated queries such as:

SELECT * FROM customer WHERE id = ?

executed hundreds of times during one request are a classic N+1 pattern.

3. Enable Hibernate SQL logging temporarily

For debugging, enable SQL logging.

Instead of seeing:

SELECT * FROM orders;

you'll see:

SELECT * FROM orders;

SELECT * FROM customer WHERE id=?;

SELECT * FROM customer WHERE id=?;

SELECT * FROM customer WHERE id=?;

The repeated statements become obvious.

Avoid leaving verbose SQL logging enabled permanently in production because it creates large logs and adds overhead (extra work).

4. Watch database metrics

Database monitoring can reveal:

• unusually high query counts
• increased network traffic
• high database CPU usage
• many nearly identical SQL statements

Often the database is telling you something is wrong before users complain.

How do you fix it?

There is no single solution.

Choose the one that matches the use case.

1. Fetch Join (usually the best solution)

Instead of loading orders first and customers later, load both together.

@Query("""
SELECT o
FROM Order o
JOIN FETCH o.customer
""")
List<Order> findAllWithCustomer();

Hibernate generates one SQL query with a SQL JOIN.

Instead of 101 queries, only one query is executed.

Use this when you know you'll need the related data.

2. Use @EntityGraph

Spring Data JPA lets you specify which relationships should be loaded.

@EntityGraph(attributePaths = "customer")
List<Order> findAll();

This keeps repository methods cleaner than writing custom JPQL (Java Persistence Query Language, a SQL-like language for JPA) everywhere.

3. Return DTOs

DTO (Data Transfer Object, an object containing only the data you want to send) is often the best choice for read-only APIs.

Instead of loading full entities (Java objects mapped to database tables):

Order
 ├── Customer
 ├── Items
 ├── Payments
 └── Address

return only:

OrderSummary

containing:

• order id
• customer name
• total

Only the required columns are queried.

Less data is loaded.

No accidental lazy loading occurs.

4. Batch fetching

Sometimes one huge JOIN is not ideal.

Hibernate supports batch fetching, where multiple related entities are loaded together using an IN (...) query instead of one query per entity.

Instead of:

100 queries

you might get:

5 queries

This is useful when fetch joins would produce very large result sets because of collections.

Best practices

• Keep associations LAZY by default.
• Use fetch joins for specific queries.
• Use @EntityGraph when it makes repository methods clearer.
• Use DTO projections (returning only the needed fields) for read-heavy APIs.
• Monitor SQL query counts in production.
• Review slow endpoints regularly instead of waiting for users to discover them.

Summary

The N+1 select problem occurs when one query loads the main data and an additional query is executed for each related object.

It usually doesn't break your application. It slowly strangles performance as data grows, which is arguably a more sophisticated form of sabotage.

The best defense is to monitor query counts, inspect repeated SQL statements, and deliberately choose how related data is loaded. In most real applications, a combination of fetch joins, @EntityGraph, DTO projections, and batch fetching provides the best balance between performance and maintainability (how easy the code is to understand and change).

References

• Vlad Mihalcea: N+1 Query Problem with JPA and Hibernate (Widely respected Hibernate expert; detailed explanation and best practices.)

• Baeldung: N+1 Problem in Hibernate and Spring Data JPA (Well-reviewed tutorial with practical Spring examples.)

• Vlad Mihalcea: Detecting the Hibernate N+1 Query Problem During Testing (Explains techniques for detecting N+1 issues before they reach production.)