The N+1 Query Trap That Slowed Our Spring Boot API to a Crawl

Everything worked smoothly in our local setup. Endpoints returned data in under 50 milliseconds, unit tests passed, and memory usage was baseline clean.

Then came the staging environment with real data volumes.

A simple GET request to fetch a list of customer orders suddenly took 11.8 seconds to respond. Database CPU usage spiked to 95%, yet our application code had no complex calculations or heavy loops.

Here is the exact breakdown of how a single Hibernate annotation caused an invisible performance cliff, how we diagnosed it using SQL logs, and the 3-line fix that reduced API response times by 98%.

The Setup: A Classic Entity Relationship

Consider a standard relational setup: an Author who has multiple Book records.

Java

@Entity

@Table(name = "authors")

public class Author {

    @Id

    @GeneratedValue(strategy = GenerationType.IDENTITY)

    private Long id;

    private String name;

    // FetchType.LAZY is standard best practice, right?

    @OneToMany(mappedBy = "author", fetch = FetchType.LAZY)

    private List<Book> books = new ArrayList<>();

    // getters and setters...

}

In our service layer, we fetched all authors to display on a summary page:

Java

@Service

public class PublishingService {

    @Autowired

    private AuthorRepository authorRepository;

    @Transactional(readOnly = true)

    public List<AuthorDTO> getAllAuthorsWithBooks() {

        List<Author> authors = authorRepository.findAll();

        return authors.stream().map(author -> {

            AuthorDTO dto = new AuthorDTO();

            dto.setName(author.getName());

            // Accessing the lazy-loaded collection triggers extra DB calls!

            dto.setBookTitles(author.getBooks().stream()

                                    .map(Book::getTitle)

                                    .collect(Collectors.toList()));

            return dto;

        }).collect(Collectors.toList());

    }

}

The Diagnosis: Examining the Raw SQL Logs

To see what Hibernate was doing under the hood, we turned on SQL logging in application.properties:

Properties

spring.jpa.show-sql=true

spring.jpa.properties.hibernate.format_sql=true

When we hit the /api/authors endpoint with 1,000 authors in the database, our console erupted with logs:

SQL

-- 1st Query: Fetch all authors (The "1" in N+1)

Hibernate:

    select

        a1_0.id,

        a1_0.name

    from

        authors a1_0;

-- 2nd Query: Fetch books for Author 1

Hibernate:

    select

        b1_0.author_id,

        b1_0.id,

        b1_0.title

    from

        books b1_0

    where

        b1_0.author_id=1;

-- 3rd Query: Fetch books for Author 2

Hibernate:

    select

        b1_0.author_id,

        b1_0.id,

        b1_0.title

    from

        books b1_0

    where

        b1_0.author_id=2;

-- ... Repeated 998 MORE TIMES!

What Happened?

1. Query 1: findAll() executed 1 query to get $N$ authors (1,000 rows).
2. Queries 2 to 1001: As our service looped through each author and called author.getBooks(), Hibernate realized the lazy-loaded books collection wasn't loaded yet. It executed 1 individual SQL query for every single author to load their books.

Instead of 1 efficient database query, our application made 1,001 network round-trips to the database server for a single HTTP request.

The Fix: 3 Ways to Eliminate N+1 Queries

Solution 1: Use JOIN FETCH in JPQL (Recommended)

Tell Hibernate to fetch both the parent entity and child collection in a single SQL JOIN query.

Java

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @Query("SELECT DISTINCT a FROM Author a LEFT JOIN FETCH a.books")

    List<Author> findAllWithBooks();

}

Generated SQL:

SQL

Hibernate:

    select

        distinct a1_0.id,

        a1_0.name,

        b1_0.author_id,

        b1_0.id,

        b1_0.title

    from

        authors a1_0

    left outer join

        books b1_0

            on a1_0.id=b1_0.author_id;

Result: Exactly 1 query executed. Response time dropped from 11.8s to 140ms.

Solution 2: Use Spring Data JPA @EntityGraph

If you prefer avoiding explicit JPQL queries, use @EntityGraph to dynamically change the fetch plan for a specific repository method:

Java

public interface AuthorRepository extends JpaRepository<Author, Long> {

    @EntityGraph(attributePaths = {"books"})

    @Override

    List<Author> findAll();

}

Solution 3: DTO Projections (Best for Read-Only APIs)

If you only need specific fields for a REST response, bypass entity mapping altogether using Interface Projections:

Java

public interface AuthorSummaryProjection {

    String getName();

    List<BookTitleProjection> getBooks();

    interface BookTitleProjection {

        String getTitle();

    }

}

Hibernate optimizes DTO projections to fetch only the requested columns directly into lightweight objects.