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.