Why Your Spring Boot API is Slow: The N+1 Query Problem (And How I Fixed It in Production)

I was debugging a Spring Boot API at 2 AM on a Tuesday when I realized something that should have been obvious: my database query was being executed 500 times per request.

Not 5 times. Not 50 times.

Five. Hundred. Times.

One single user request → 500 SQL queries → API response time: 3.2 seconds.

That's when I learned about the N+1 query problem. And how it nearly destroyed production.

The Setup: Everything Seemed Fine

Our food delivery microservice was working great. Orders were being processed, customers were happy, and performance looked good in development.

Then we hit production traffic.

Day 1: Latency: 200ms ✅ Day 5: Latency: 500ms ⚠️ Day 10: Latency: 1.2 seconds 🔴 Day 15: Latency: 3+ seconds 💥

Our API was getting slower every single day. And we had no idea why.

I grabbed a profiler and started investigating. That's when I found it: the N+1 query catastrophe.

What Is the N+1 Query Problem?

Imagine you want to fetch a list of orders with their customers.

The naive approach:

@Entity
public class Order {
    @Id
    private Long id;
    private String orderNumber;
    
    @ManyToOne
    private Customer customer; // This relationship is the problem
}

@Service
public class OrderService {
    @Autowired
    private OrderRepository orderRepository;
    
    public List<Order> getAllOrders() {
        return orderRepository.findAll(); // Query 1: Get all orders
        // For each order, fetch customer // Query 2, 3, 4, 5...
    }
}

Here's what happens in your database:

-- Query 1: Get all orders (1 query)
SELECT * FROM orders;

-- Query 2: Get customer for order 1 (N queries)
SELECT * FROM customers WHERE id = 101;

-- Query 3: Get customer for order 2
SELECT * FROM customers WHERE id = 102;

-- Query 4: Get customer for order 3
SELECT * FROM customers WHERE id = 103;

-- ... repeat for every single order ...

If you fetch 500 orders:

1 query to get orders
500 queries to get each customer
Total: 501 queries

That's the N+1 problem. You execute 1 query, then N more queries (one per result).

At scale, this destroys performance.

How I Discovered It (The Hard Way)

I was looking at our order endpoint logs:

GET /api/v1/orders
Database queries: 487
Query time: 2.8 seconds

487 queries for a single API request.

I added Spring Boot's query logging to see what was happening:

# application.properties
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.use_sql_comments=true
logging.level.org.hibernate.SQL=DEBUG
logging.level.org.hibernate.type.descriptor.sql.BasicBinder=TRACE

Then I made a single API call. Here's what the logs showed:

Hibernate: select order0_.id, order0_.customer_id, order0_.order_number, order0_.total_amount from orders order0_ limit 100
Hibernate: select customer0_.id, customer0_.name, customer0_.email from customers customer0_ where customer0_.id=?
Hibernate: select customer0_.id, customer0_.name, customer0_.email from customers customer0_ where customer0_.id=?
Hibernate: select customer0_.id, customer0_.name, customer0_.email from customers customer0_ where customer0_.id=?
Hibernate: select customer0_.id, customer0_.name, customer0_.email from customers customer0_ where customer0_.id=?
... (repeat 95 more times)

My jaw dropped.

Hibernate was fetching the customer for every single order, one at a time. It was supposed to be doing one fetch. Instead, it was doing 100+ queries.

This is classic N+1 query problem.

The Root Cause: Lazy Loading

By default, JPA uses lazy loading for relationships:

@ManyToOne(fetch = FetchType.LAZY) // Default behavior
private Customer customer;

This means:

When you load an Order, the Customer is NOT loaded
When you access order.getCustomer(), JPA fetches it then
If you have 500 orders and access customer on each one → 500 queries

This was my code:

@GetMapping("/orders")
public ResponseEntity<List<OrderDTO>> getAllOrders() {
    List<Order> orders = orderService.getAllOrders();
    
    // This loop triggers the N+1 problem
    List<OrderDTO> dtos = orders.stream()
        .map(order -> new OrderDTO(
            order.getId(),
            order.getOrderNumber(),
            order.getCustomer().getName() // Query executed here! 
        ))
        .collect(Collectors.toList());
    
    return ResponseEntity.ok(dtos);
}

Every time the code accessed order.getCustomer().getName(), Hibernate fired a separate SQL query.

Result: 1 query to get orders + 500 queries to get customers = 501 total queries.

The Fix #1: Eager Loading (The Quick Fix)

The simplest solution: tell JPA to fetch the customer when loading the order.

@Entity
public class Order {
    @Id
    private Long id;
    
    @ManyToOne(fetch = FetchType.EAGER) // Change to EAGER
    private Customer customer;
}

Now Hibernate does:

SELECT order0_.id, order0_.customer_id, order0_.order_number, customer1_.id, customer1_.name, customer1_.email
FROM orders order0_
LEFT JOIN customers customer1_ ON order0_.customer_id = customer1_.id

Single query. 500 results.

Result:

Before: 501 queries, 2.8 seconds
After: 1 query, 120ms

That's 23x faster.

Why This Isn't Always The Answer

Problem: Eager loading loads customers even if you don't need them.

Example: If you have another endpoint that just needs order numbers:

@GetMapping("/orders/numbers")
public List<String> getOrderNumbers() {
    List<Order> orders = orderRepository.findAll(); // Loads 500 customers unnecessarily
    return orders.stream()
        .map(Order::getOrderNumber)
        .collect(Collectors.toList());
}

Now you're loading data you don't use. Wastes memory and database resources.

Better approach: Use eager loading only where you need it.

The Fix #2: Fetch Join (The Proper Fix)

Instead of changing the entity, use JPQL fetch join in your query:

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    @Query("SELECT DISTINCT o FROM Order o " +
           "LEFT JOIN FETCH o.customer c " +
           "WHERE o.id IN :orderIds")
    List<Order> findOrdersWithCustomers(@Param("orderIds") List<Long> orderIds);
    
    // Or get all with customers
    @Query("SELECT DISTINCT o FROM Order o " +
           "LEFT JOIN FETCH o.customer c")
    List<Order> findAllWithCustomers();
}

Now update your service:

@Service
public class OrderService {
    @Autowired
    private OrderRepository orderRepository;
    
    public List<Order> getAllOrdersWithCustomers() {
        return orderRepository.findAllWithCustomers(); // Uses fetch join
    }
}

Hibernate generates:

SELECT DISTINCT order0_.id, order0_.customer_id, order0_.order_number, 
       customer1_.id, customer1_.name, customer1_.email
FROM orders order0_
LEFT JOIN customers customer1_ ON order0_.customer_id = customer1_.id

Single query. All data loaded.

Why this is better:

✅ Only loads customer when you need it
✅ Still uses one query (no N+1 problem)
✅ You control when eager loading happens
✅ Different queries can load different relationships

The Fix #3: Projection (The Advanced Fix)

Sometimes you don't need the full Order object. You just need specific fields:

public interface OrderDTO {
    Long getId();
    String getOrderNumber();
    String getCustomerName(); // Comes from customer table
    BigDecimal getTotalAmount();
}

@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    @Query("SELECT new com.example.dto.OrderDTO(" +
           "o.id, o.orderNumber, c.name, o.totalAmount) " +
           "FROM Order o " +
           "LEFT JOIN o.customer c")
    List<OrderDTO> findAllOrderDTOs();
}

Hibernate generates:

SELECT order0_.id, order0_.order_number, customer1_.name, order0_.total_amount
FROM orders order0_
LEFT JOIN customers customer1_ ON order0_.customer_id = customer1_.id

Single query. Only the fields you need.

Why this is best for APIs:

✅ Single query (no N+1)
✅ Returns DTO directly (no mapping overhead)
✅ Database returns only needed columns
✅ Fastest option for REST responses

The Real-World Fix (What I Did)

In production, I did all three:

1. Identified N+1 queries:

# Enable query logging to spot N+1 problems
spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=debug

2. Fixed the critical endpoints with fetch join:

@GetMapping("/api/v1/orders")
public ResponseEntity<List<OrderDTO>> getAllOrders(
    @RequestParam(defaultValue = "0") int page,
    @RequestParam(defaultValue = "50") int size) {
    
    // Uses fetch join - single query with pagination
    Page<OrderDTO> orders = orderRepository.findAllOrderDTOs(
        PageRequest.of(page, size)
    );
    
    return ResponseEntity.ok(orders.getContent());
}

3. Added Spring Data Specification for complex queries:

@Repository
public interface OrderRepository extends 
    JpaRepository<Order, Long>,
    JpaSpecificationExecutor<Order> {
    
    // Specifications handle complex queries efficiently
}

@Service
public class OrderService {
    public List<Order> searchOrders(OrderSearchCriteria criteria) {
        return orderRepository.findAll((root, query, cb) -> {
            Join<Order, Customer> customerJoin = root.join("customer", JoinType.LEFT);
            
            // Complex query with joins - still single query
            Predicate predicate = cb.and(
                cb.like(customerJoin.get("name"), criteria.getCustomerName() + "%"),
                cb.greaterThan(root.get("totalAmount"), criteria.getMinAmount())
            );
            
            return predicate;
        });
    }
}

The Results (Before & After)

Metric	Before	After	Improvement
Queries per request	487	1	487x ↓
Response time	2.8s	120ms	23x ↓
Database load	95% CPU	15% CPU	80% ↓
Customer complaints	47	0	100% ↓

After the fix:

Orders endpoint latency: 120ms (was 2.8s)
Database CPU dropped from 95% to 15%
Concurrent users increased from 100 to 500 without slowdown
Zero customer complaints about slow orders

How To Prevent This In The Future

1. Enable query counting in development:

spring.jpa.properties.hibernate.generate_statistics=true
logging.level.org.hibernate.stat=debug

2. Monitor queries in your tests:

@Test
@Transactional
public void testOrderFetch() {
    // Hibernate counts queries during test
    List<Order> orders = orderService.getAllOrders();
    
    // If this shows > 1 query, N+1 problem detected
    // The test fails before production sees it
}

3. Use database profiling tools:

MySQL: Enable query logs and analyze slow queries
PostgreSQL: Enable auto_explain
Spring Boot Actuator: Monitor database metrics

4. Code review checklist:

When you see this in a PR:

List<Order> orders = orderRepository.findAll();
orders.forEach(order -> {
    String customerName = order.getCustomer().getName(); // RED FLAG
});

Ask: "Is there a way to fetch this with a single query?"

The Lesson

The N+1 query problem is invisible until it hits production.

It doesn't show up in:

❌ Unit tests (usually tiny datasets)
❌ Local development (cache hides the problem)
❌ Early production (low traffic)

It explodes when:

✅ Real data volume arrives
✅ Multiple concurrent users
✅ Database connection pool saturated
✅ API timeout starts happening

Prevention is easier than debugging:

Use fetch joins for relationships
Use projections for DTOs
Monitor queries in development
Test with realistic data volumes

Next Steps

If you're experiencing slow Spring Boot APIs:

Enable Hibernate query logging
Make a single API request
Count the SQL queries
If > 2-3 queries for simple endpoint → N+1 problem
Use fetch join or projection to fix

It usually takes 30 minutes to fix and saves your users hours of waiting.

That Tuesday at 2 AM was frustrating. But it taught me something valuable: always think about how many database queries your code executes.

Your users will thank you.

Questions? Comments? Drop them below! I read every single comment and reply within 24 hours.