Solving the N+1 Query Problem in REST APIs: A Real-World Banking Example (Made Easy for Spring Boot Developers)

If you're using Spring Boot with Hibernate (JPA) and a relational database like MySQL/PostgreSQL, the N+1 Query Problem can silently hurt your API performance.

Let’s understand this issue in simple steps using a real-world banking project example, and how to fix it using Spring Boot tricks.

---

📌 What is the N+1 Problem?

The N+1 problem occurs when:

• 1 SQL query fetches the main (parent) data — for example, bank accounts.

• Then N additional SQL queries are executed for related (child) data — like each account’s transactions.

✅ Expected: 1 smart query to fetch everything. ❌ Actual: 1 query for accounts + N queries for transactions = N+1 total queries.

This increases database load and slows down your REST APIs.

---

🏦 Spring Boot Banking API Example

Say you build a banking dashboard endpoint:

@GetMapping("/api/accounts")

public List<AccountDTO> getAllAccounts() {

    List<Account> accounts = accountRepository.findAll();

    return accounts.stream()

        .map(account -> new AccountDTO(account.getId(), account.getTransactions()))

        .collect(Collectors.toList());

}

Behind the scenes:

• findAll() runs: SELECT * FROM account;

• Then for each account, Hibernate runs: SELECT * FROM transaction WHERE account_id = ?;

➡️ With 100 accounts, this results in 101 queries. That’s the N+1 problem.

---

❗ Why Is It a Big Deal?

• 🚫 Bad for performance: 100 queries slow down response time.

• 🔥 Heavy DB load: Affects scaling when users grow.

• 🧪 Hard to debug: Looks fine in code, but performs poorly in production.

---

✅ How to Fix N+1 in Spring Boot (Step-by-Step)

✅ 1. Use JOIN FETCH in JPA Query

@Query("SELECT a FROM Account a JOIN FETCH a.transactions")
List<Account> findAllWithTransactions();

✅ Fetches everything in 1 query.

✅ 2. Use JPA Entity Graphs

@EntityGraph(attributePaths = {"transactions"})
List<Account> findAll();

✅ Cleaner and better for dynamic loading.

✅ 3. Use DTO Projection (Recommended for REST)

@Query("SELECT new com.bank.dto.AccountDTO(a.id, t) 

FROM Account a JOIN a.transactions t")

List<AccountDTO> getAccountDTOs();

✅ Loads only the required data — no extra queries.

✅ 4. Use Batch Fetching (Optional)

In application.properties:

spring.jpa.properties.hibernate.default_batch_fetch_size=100

✅ Tells Hibernate to fetch child records in batches.

---

💡 Best Practices & Tricky Interview Points

• 🧵 Lazy vs Eager:

  • Use LAZY loading to avoid unnecessary data.

  • Use JOIN FETCH when you really need the related data.

• 🛡️ Avoid LazyInitializationException:

  • Use @Transactional in service layer if accessing lazy fields.

• 🧪 Testing:

  • Use Hibernate query counters in tests to catch N+1 bugs.

  • Enable SQL logging: spring.jpa.show-sql=true

• 🧠 Interview Tip:

  • Be ready to explain how you identify and fix N+1 using Spring Boot tools.

---

🚀 Summary: Fixing N+1 in Spring Boot

Problem	Fix
Too many queries	Use JOIN FETCH
Lazy loading issues	Use EntityGraph or @Transactional
Poor API performance	Use DTO projections
Query explosion	Use batch fetch settings

---

🧠 Final Thought

"The fastest code isn’t the one that runs first — it’s the one that hits the DB the least.”

If you're building REST APIs for enterprise, finance, or dashboards — fixing N+1 makes your system faster, lighter, and scalable.

Happy Learning :)