N+1 query problem in Spring Boot - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ component_behavior

intermediate

What is the output count of queries executed?

Given a Spring Boot JPA repository fetching a list of 5 authors, each with 3 books, and the code uses a simple findAll() on authors without fetch join, how many SQL queries will be executed when accessing each author's books in a loop?

Spring Boot

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

A 1 query

B 5 queries

C 6 queries

D 15 queries

💡 Hint

Think about how JPA loads collections lazily by default and when queries are triggered.

The initial findAll() triggers 1 query to load all authors. Then, accessing each author's books triggers 1 query per author due to lazy loading, totaling 5 more queries. So, 6 queries in total.

📝 Syntax

intermediate

Which JPQL query avoids the N+1 problem by fetching books eagerly?

Select the JPQL query that fetches authors and their books in a single query to avoid the N+1 problem.

A SELECT a FROM Author a JOIN FETCH a.books

B SELECT a FROM Author a JOIN a.books

C SELECT a FROM Author a LEFT JOIN a.books

D SELECT a FROM Author a WHERE a.books IS NOT NULL

💡 Hint

Look for the keyword that forces eager loading of the collection.

JOIN FETCH tells JPA to load the association eagerly in the same query, preventing multiple queries.

🔧 Debug

advanced

Why does this code cause an N+1 problem despite using @EntityGraph?

Consider this repository method: @EntityGraph(attributePaths = {"books"}) List findAll(); Why might this still cause multiple queries when accessing books?

A The @EntityGraph only works with native queries, not JPQL

B The @EntityGraph is not applied because the method is overridden elsewhere

C The @EntityGraph is ignored because the fetch type of books is EAGER

D The @EntityGraph is applied but the transaction is closed before accessing books

💡 Hint

Think about when lazy loading triggers queries and transaction scope.

If the transaction ends before accessing the collection, lazy loading triggers separate queries outside the transaction, causing N+1.

🧠 Conceptual

advanced

What is the main cause of the N+1 query problem in ORM frameworks?

Choose the best explanation for why the N+1 query problem occurs in ORM frameworks like JPA.

A Because lazy loading triggers a separate query for each related entity when accessed

B Because ORM frameworks always load all related entities eagerly by default

C Because ORM frameworks do not support joins in queries

D Because the database does not support complex queries

💡 Hint

Consider how lazy loading works when accessing related data.

Lazy loading defers loading related entities until accessed, causing one query per related entity, leading to N+1 queries.

❓ state_output

expert

What is the number of SQL queries executed with this code using batch fetching?

Given this Spring Boot JPA configuration with batch fetching enabled for books, and fetching 10 authors each with 5 books, how many SQL queries are executed when accessing all books of all authors? Assume batch size is 5. Code: List authors = authorRepository.findAll(); for (Author author : authors) { author.getBooks().size(); }

A 1 query

B 3 queries

C 11 queries

D 50 queries

💡 Hint

Batch fetching loads collections in groups instead of one by one.

The first query loads all authors (1 query). Batch fetching loads books in batches of 5 authors, so 10 authors require 2 batches (2 queries). Total queries = 3.

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