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.