N+1 query problem in Spring Boot - Step-by-Step Execution

Concept Flow - N+1 query problem

Start: Fetch list of parent entities

↓

For each parent entity

↓

Execute separate query to fetch child entities

↓

Combine parent with fetched children

↓

Return combined results

↓

End

The N+1 query problem happens when fetching a list of entities causes one query for the list, then one query per entity to fetch related data, causing many queries.

Execution Sample

Spring Boot

List<Author> authors = authorRepository.findAll();
for (Author author : authors) {
  List<Book> books = bookRepository.findByAuthorId(author.getId());
  author.setBooks(books);
}

Fetch all authors, then for each author fetch their books separately, causing many queries.

Execution Table

Step	Action	Query Executed	Result	Notes
1	Fetch all authors	SELECT * FROM authors	List of 3 authors	One query to get authors
2	Fetch books for author 1	SELECT * FROM books WHERE author_id=1	List of books for author 1	One query per author
3	Fetch books for author 2	SELECT * FROM books WHERE author_id=2	List of books for author 2	Repeated query pattern
4	Fetch books for author 3	SELECT * FROM books WHERE author_id=3	List of books for author 3	Repeated query pattern
5	Combine authors with books	No query	Authors with their books attached	Final combined data
6	End	No query	Return combined list	N+1 queries caused by loop

💡 After fetching authors, loop causes one query per author to fetch books, leading to N+1 queries total.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
authors	empty	3 authors loaded	3 authors loaded	3 authors loaded	3 authors loaded	3 authors with books attached
books	empty	empty	books for author 1	books for author 2	books for author 3	last fetched books list

Key Moments - 3 Insights

Why does the number of queries increase with the number of authors?

Because for each author, a separate query fetches their books (see execution_table rows 2-4), causing one query per author plus the initial authors query.

Is the initial query enough to get all authors and their books?

No, the initial query only fetches authors (row 1). Books are fetched separately in the loop (rows 2-4), causing multiple queries.

How can this problem be avoided?

By fetching authors and their books in a single query using a join or fetch strategy, avoiding the loop of queries (not shown here but important).

Visual Quiz - 3 Questions

Look at the execution_table, how many queries are executed in total for 3 authors?

A 3 queries

B 4 queries

C 1 query

D 6 queries

💡 Hint

Check rows 1 to 4 in execution_table: 1 query for authors + 3 queries for books.

At which step are the books for the second author fetched?

A Step 3

B Step 2

C Step 4

D Step 1

💡 Hint

Look at execution_table row 3 for books of author 2.

If there were 5 authors instead of 3, how many queries would be executed?

A 5 queries

B 1 query

C 6 queries

D 10 queries

💡 Hint

Total queries = 1 for authors + N for each author’s books (see exit_note).

Concept Snapshot

N+1 Query Problem in Spring Boot:
- Happens when fetching a list triggers 1 query + N queries for related data
- Example: findAll authors + find books per author in loop
- Causes performance issues with many queries
- Avoid by using fetch joins or batch fetching
- Key: reduce queries by fetching related data together

Full Transcript

The N+1 query problem occurs when an application fetches a list of parent entities with one query, then for each parent entity executes another query to fetch related child entities. In Spring Boot, this often happens when calling findAll() on a repository to get authors, then looping over authors to fetch their books separately. This causes one query to get all authors plus one query per author to get books, leading to many queries and poor performance. The execution table shows step-by-step queries: first fetching authors, then fetching books for each author individually. Variables track authors and books lists as they update. Beginners often wonder why queries multiply; it is because of the loop fetching books separately. To fix this, use fetch joins or batch fetching to get authors and books in fewer queries. This visual helps understand how the N+1 problem arises and why it should be avoided.

Practice

1. What is the N+1 query problem in Spring Boot applications?

easy

A. Not using any database queries at all

B. Making only one query to fetch all data including related entities

C. Using incorrect SQL syntax in queries

D. Making one query to fetch a list, then one query per item to fetch related data

Solution

1. Step 1: Understand the query pattern

   The N+1 problem occurs when the app first fetches a list (1 query), then fetches related data for each item separately (N queries).

2. Step 2: Identify the problem impact

   This causes many queries, slowing down the app and wasting resources.

3. Final Answer:

   Making one query to fetch a list, then one query per item to fetch related data -> Option D

4. Quick Check:

   N+1 query problem = multiple queries instead of one [OK]

Hint: N+1 means 1 query + N queries for related data [OK]

Common Mistakes:

• Thinking N+1 means only one query is made
• Confusing it with syntax errors
• Assuming it is about missing queries

2. Which of the following is the correct way to use JOIN FETCH in a Spring Data JPA query to avoid the N+1 problem?

easy

A. @Query("SELECT o FROM Order o JOIN FETCH o.items")

B. @Query("SELECT o FROM Order o JOIN o.items")

C. @Query("SELECT o FROM Order o LEFT JOIN o.items")

D. @Query("SELECT o FROM Order o WHERE o.items IS NOT NULL")

Solution

1. Step 1: Understand JOIN FETCH usage

   JOIN FETCH tells JPA to fetch related entities eagerly in one query, avoiding multiple queries.

2. Step 2: Identify correct syntax

   @Query("SELECT o FROM Order o JOIN FETCH o.items") uses JOIN FETCH correctly to fetch orders with their items in one query.

3. Final Answer:

   @Query("SELECT o FROM Order o JOIN FETCH o.items") -> Option A

4. Quick Check:

   JOIN FETCH = eager fetch to avoid N+1 [OK]

Hint: Use JOIN FETCH to load related data in one query [OK]

Common Mistakes:

• Using JOIN without FETCH causes lazy loading
• Using WHERE instead of JOIN FETCH
• Missing FETCH keyword

3. Given this Spring Data JPA repository method:

@Query("SELECT c FROM Customer c")
List<Customer> findAllCustomers();

And assuming Customer has a lazy-loaded orders collection, what happens when you call findAllCustomers() and then access orders for each customer?

medium

A. One query to get customers, then one query per customer to get orders (N+1 problem)

B. One query to get customers and all orders in one go

C. No queries are made until orders are accessed

D. An error occurs because orders are not fetched

Solution

1. Step 1: Analyze the query and lazy loading

   The query fetches customers only; orders are lazy-loaded, so not fetched initially.

2. Step 2: Accessing orders triggers queries

   Accessing orders for each customer triggers one query per customer, causing N+1 queries total.

3. Final Answer:

   One query to get customers, then one query per customer to get orders (N+1 problem) -> Option A

4. Quick Check:

   Lazy loading causes N+1 queries [OK]

Hint: Lazy loading causes one query per item when accessed [OK]

Common Mistakes:

• Assuming all data loads in one query
• Thinking no queries run until orders accessed
• Confusing lazy and eager loading

4. You have this code snippet causing N+1 queries:

List<Author> authors = authorRepository.findAll();
for (Author a : authors) {
    System.out.println(a.getBooks().size());
}

How can you fix it to avoid the N+1 problem?

medium

A. Add @Transactional annotation to the method

B. Call getBooks() inside a separate thread

C. Change repository method to use @Query("SELECT a FROM Author a JOIN FETCH a.books")

D. Remove the loop and print authors only

Solution

1. Step 1: Identify cause of N+1

   Calling getBooks() inside loop triggers one query per author due to lazy loading.

2. Step 2: Use JOIN FETCH to load books eagerly

   Changing repository query to use JOIN FETCH loads authors and books in one query, avoiding N+1.

3. Final Answer:

   Change repository method to use @Query("SELECT a FROM Author a JOIN FETCH a.books") -> Option C

4. Quick Check:

   JOIN FETCH fixes N+1 by eager loading [OK]

Hint: Use JOIN FETCH in query to load related data eagerly [OK]

Common Mistakes:

• Adding @Transactional does not fix N+1
• Using threads does not solve query count
• Removing loop hides problem but does not fix it

5. You have entities Post and Comment with a one-to-many lazy relationship. You want to fetch all posts with their comments efficiently. Which approach best avoids the N+1 problem and handles posts with no comments?

hard

A. Use native SQL without JOIN FETCH and map manually

B. @Query("SELECT p FROM Post p LEFT JOIN FETCH p.comments") to fetch posts and comments in one query

C. Fetch posts first, then fetch comments in a separate query for each post

D. Fetch posts only and ignore comments to reduce queries

Solution

1. Step 1: Understand lazy loading and N+1

   Lazy loading comments causes one query per post when accessed, causing N+1 problem.

2. Step 2: Use LEFT JOIN FETCH to include posts without comments

   LEFT JOIN FETCH fetches posts and their comments in one query, including posts with no comments.

3. Final Answer:

   @Query("SELECT p FROM Post p LEFT JOIN FETCH p.comments") to fetch posts and comments in one query -> Option B

4. Quick Check:

   LEFT JOIN FETCH avoids N+1 and includes empty collections [OK]

Hint: Use LEFT JOIN FETCH to include all posts and comments [OK]

Common Mistakes:

• Using INNER JOIN FETCH excludes posts without comments
• Fetching comments separately causes N+1
• Ignoring comments loses needed data