Spring Boot N+1 Query Problem: Root Cause and Solutions

N+1 queries are one of the most common performance bugs in Spring Boot applications. A single database query intended to fetch users becomes 1 + N queries — one to fetch all users, then one query per user for related data.

Result: a request that should take 10ms takes 2000ms. And as your dataset grows, response time grows linearly. This guide shows you how to spot N+1 patterns, understand why they happen, and fix them permanently.

The N+1 Problem Explained

The Classic Scenario

@Entity
public class User {
    @Id
    private Long id;
    private String name;
    
    @OneToMany(mappedBy = "user", fetch = FetchType.LAZY)  // Default: LAZY
    private List<Order> orders;
}

@Entity
public class Order {
    @Id
    private Long id;
    private String product;
    
    @ManyToOne
    @JoinColumn(name = "user_id")
    private User user;
}

@Service
public class UserService {
    @Autowired
    private UserRepository userRepository;
    
    public List<UserDto> getAllUsersWithOrders() {
        List<User> users = userRepository.findAll();  // Query 1: SELECT * FROM users
        
        return users.stream().map(user -> new UserDto(
            user.getId(),
            user.getName(),
            user.getOrders().size()  // N queries: SELECT * FROM orders WHERE user_id = ?
        )).toList();
    }
}

Execution:

1 query: SELECT * FROM users  (returns 100 rows)
100 queries: SELECT * FROM orders WHERE user_id = ? (one per user)
Total: 101 queries

Network round-trips scale with your data. 1000 users = 1001 queries.

Why This Happens: Lazy Loading

By default, @OneToMany, @ManyToOne, and @ManyToMany relationships use lazy loading. The relationship is not fetched with the parent object.

When you access user.getOrders(), Hibernate/JPA realizes the orders weren't loaded and fires a query to fetch them.

If you access this relationship inside a loop:

for (User user : users) {
    System.out.println(user.getOrders().size());  // Query fired for each user
}

Each iteration triggers a query.

Detection: How to Spot N+1 Queries

1. Enable SQL Logging

spring:
  jpa:
    show-sql: true
    properties:
      hibernate:
        format_sql: true
        use_sql_comments: true

Or with Logback:

<logger name="org.hibernate.SQL" level="DEBUG" />

Run your endpoint. If you see 101 queries for 100 users, you have N+1.

2. Count Query Execution Time

Monitor request time vs database time:

@Component
@Aspect
public class QueryCountingAspect {
    
    @Around("execution(public * com.example..*Service.*(..))")
    public Object measureQueryCount(ProceedingJoinPoint pjp) throws Throwable {
        int beforeCount = getQueryCount();
        long startTime = System.nanoTime();
        
        Object result = pjp.proceed();
        
        long duration = System.nanoTime() - startTime;
        int queries = getQueryCount() - beforeCount;
        
        if (queries > 10) {
            log.warn("{}: {} queries in {} ms", pjp.getSignature(), queries, duration / 1_000_000);
        }
        
        return result;
    }
    
    private int getQueryCount() {
        // Use DataSource proxy or Hibernate statistics
        return HibernateProxyHelper.getQueryExecutionStats();
    }
}

3. Use p6spy or DataSourceProxy

Wrap your DataSource to log every query:

<dependency>
    <groupId>p6spy</groupId>
    <artifactId>p6spy</artifactId>
</dependency>

Enable in application.properties:

spring.datasource.driver-class-name=com.p6spy.engine.spy.P6SpyDriver

Now P6spy logs all queries with execution time. You'll immediately see duplicate queries.

Solution 1: JOIN FETCH

The simplest and most direct fix: explicitly fetch the relationship in the query.

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @Query("SELECT DISTINCT u FROM User u " +
           "LEFT JOIN FETCH u.orders " +
           "WHERE u.id IN :ids")
    List<User> findUsersWithOrders(@Param("ids") List<Long> ids);
}

@Service
public class UserService {
    public List<UserDto> getAllUsersWithOrders() {
        // 1 query: SELECT ... FROM users u LEFT JOIN orders o ON ...
        List<User> users = userRepository.findAll();
        // Wait, this is still the old findAll(). Use the custom query:
        List<User> users = userRepository.findUsersWithOrders(...);
        
        return users.stream().map(user -> new UserDto(
            user.getId(),
            user.getName(),
            user.getOrders().size()  // No query — already loaded
        )).toList();
    }
}

Note: When using JOIN FETCH with @OneToMany, use DISTINCT to avoid duplicate rows (one row per order).

JOIN FETCH with Pagination

@Query("SELECT DISTINCT u FROM User u " +
       "LEFT JOIN FETCH u.orders " +
       "ORDER BY u.id")
Page<User> findAllWithOrders(Pageable pageable);

Warning: Pagination with JOIN FETCH is tricky. Hibernate may fetch all results and paginate in memory. For large datasets, this is slow. Use a two-query approach instead.

Solution 2: @EntityGraph

More declarative than @Query + JOIN FETCH. Define a graph and reuse it across queries.

@Entity
@NamedEntityGraph(
    name = "User.withOrders",
    attributeNodes = {
        @NamedAttributeNode("orders")
    }
)
public class User {
    @Id
    private Long id;
    private String name;
    
    @OneToMany(mappedBy = "user")
    private List<Order> orders;
}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @EntityGraph("User.withOrders")
    List<User> findAll();
    
    @EntityGraph(attributePaths = {"orders"})
    User findById(Long id);
}

Advantage: Spring Data can apply the graph to any repository method without rewriting @Query.

Solution 3: Change to Eager Loading

Make the relationship eager globally:

@Entity
public class User {
    @OneToMany(mappedBy = "user", fetch = FetchType.EAGER)  // Eager by default
    private List<Order> orders;
}

Caution: Eager loading fetches related data for every query, even if you don't use it. This can bloat your object graph and slow down queries that don't need the relationship.

Better approach: use eager loading sparingly and only for relationships you always need.

Solution 4: Two-Query Approach (For Complex Cases)

Sometimes a single JOIN FETCH query becomes complex. Split into two:

@Service
public class UserService {
    
    public List<UserDto> getAllUsersWithOrders() {
        // Query 1: Fetch users
        List<User> users = userRepository.findAll();
        
        // Query 2: Fetch all orders for all users in one go
        List<Long> userIds = users.stream().map(User::getId).toList();
        Map<Long, List<Order>> ordersByUserId = 
            orderRepository.findAllByUserIdIn(userIds)
                          .stream()
                          .collect(groupingBy(Order::getUserId));
        
        // Combine in memory — no more N queries
        return users.stream().map(user -> new UserDto(
            user.getId(),
            user.getName(),
            ordersByUserId.getOrDefault(user.getId(), List.of()).size()
        )).toList();
    }
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    @Query("SELECT o FROM Order o WHERE o.user.id IN :userIds")
    List<Order> findAllByUserIdIn(@Param("userIds") List<Long> userIds);
}

Total: 2 queries. No matter how many users, only 2 queries.

Solution 5: Use a DTO Projection

Fetch exactly what you need, not entire entities.

public record UserOrderCountDto(Long userId, String name, Long orderCount) {}

@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    @Query("SELECT new com.example.UserOrderCountDto(" +
           "u.id, u.name, COUNT(o)) " +
           "FROM User u " +
           "LEFT JOIN u.orders o " +
           "GROUP BY u.id, u.name")
    List<UserOrderCountDto> getUserOrderCounts();
}

Result: 1 query, returns exactly the data you need (userId, name, count), no object hydration overhead.

DTO projections are faster than entity mappings because the database does the aggregation.

Prevention Checklist

Enable SQL logging in development — catch N+1 immediately
Use @EntityGraph or JOIN FETCH for relationships you access
Prefer explicit queries over implicit lazy loading
Audit your service methods — are there loops accessing relationships?
Use DTO projections for read-only queries
Consider two-query approach for complex cases
Test with realistic data — N+1 hides when you have 10 rows of test data
Monitor production — use New Relic or Datadog to catch slow endpoints

Summary

N+1 queries happen because lazy loading triggers a query for each relationship access inside a loop. 1 query to fetch users, N queries to fetch their orders = N+1 total.

Fix with JOIN FETCH, @EntityGraph, or a two-query approach. Always log SQL in development. Use DTO projections for read-only queries.

Detect More Performance Issues with JOptimize

N+1 queries are just one type of database performance bug. Missing indexes, unbounded queries, and eager fetching also slow down applications.

JOptimize detects N+1 patterns and other Hibernate anti-patterns in your code:

IntelliJ Plugin — real-time N+1 detection while you code: Install JOptimize
Web Dashboard — full database and Hibernate audit: Analyze your project free →

Catch N+1 queries before they hit production.