How JOIN FETCH Reduced Database Load by 94%: A Real-World Case Study

Source: Dev.to

Introduction

The N+1 problem is one of the most common causes of high database load in Spring Boot applications. In this article, I’ll show you how to systematically solve this problem using a real‑world financial system project.

What is the N+1 Problem?

The N+1 problem occurs when an ORM generates additional SELECT queries to load related entities.

Example of the Problem

@Entity
public class ExternalExchangeRate {
    @ManyToOne(fetch = FetchType.LAZY)
    private Currency currencyFrom;

    @ManyToOne(fetch = FetchType.LAZY)
    private Currency currencyTo;
}

View ExternalExchangeRate code →

When executing:

List rates = repository.findByExchangeDateAndCurrencyFromId(date, currencyFromId);
for (ExternalExchangeRate rate : rates) {
    System.out.println(rate.getCurrencyFrom().getCode()); // N queries!
    System.out.println(rate.getCurrencyTo().getCode());   // N more queries!
}

Result: 1 + N*2 queries to the database.
For example, with 15 currency rates for a day:

• 1 SELECT for rates
• 15 SELECT for currencyFrom
• 15 SELECT for currencyTo

Total: 31 queries! 😱

Detecting the N+1 Problem

Configuration for Development Mode

# application-dev.properties
spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
spring.jpa.properties.hibernate.generate_statistics=true
spring.jpa.properties.hibernate.use_sql_comments=true
logging.level.org.hibernate.SQL=DEBUG

View application-dev.properties →

Debug Endpoint for Monitoring

@RestController
@RequestMapping("/api/debug")
public class DebugController {

    @Autowired
    private EntityManagerFactory emf;

    @GetMapping("/hibernate-stats")
    public Map getHibernateStats() {
        Statistics stats = emf.unwrap(SessionFactory.class)
                              .getStatistics();

        return Map.of(
            "queriesExecuted", stats.getQueryExecutionCount(),
            "prepareStatementCount", stats.getPrepareStatementCount(),
            "entitiesLoaded", stats.getEntityLoadCount(),
            "entitiesFetched", stats.getEntityFetchCount()
        );
    }
}

View DebugController code →

Measurements BEFORE Optimization

GET /api/external-exchange-rates/latest?date=2024-11-24&currencyFromId=2

Result:
- Time: 48ms
- prepareStatementCount: 33  ← Real SQL statements executed!
- queriesExecuted: 2          ← HQL/JPQL query types
- entitiesLoaded: 131
- entitiesFetched: 31         ← Additional lazy fetches!

Understanding the metrics

• queriesExecuted = number of distinct HQL/JPQL queries
• prepareStatementCount = actual JDBC statements (33)
• entitiesFetched = lazy entity fetches (31 Currency entities)

SQL log excerpt:

-- 1 query for exchange rates
SELECT * FROM external_exchange_rates 
WHERE exchange_date = '2024-11-24' 
  AND currency_from_id = 2;

-- 31 additional queries for currencies
SELECT * FROM currencies WHERE id = 1;
SELECT * FROM currencies WHERE id = 3;
SELECT * FROM currencies WHERE id = 4;
-- ... 28 more queries

When JOIN FETCH Works Perfectly

Flat Data Structure (@ManyToOne)

JOIN FETCH is ideal for flat data structures where an entity references only one entity from another table.

@Entity
public class ExternalExchangeRate {
    @Id
    private Long id;

    // Each rate references ONE currency FROM
    @ManyToOne(fetch = FetchType.LAZY)
    private Currency currencyFrom;

    // Each rate references ONE currency TO
    @ManyToOne(fetch = FetchType.LAZY)
    private Currency currencyTo;

    private BigDecimal rate;
}

Why this works perfectly

• 1 ExternalExchangeRate → 1 Currency (from)
• 1 ExternalExchangeRate → 1 Currency (to)
• No multiple relationships (@OneToMany)
• Pagination works correctly ✅
• COUNT query is accurate ✅

When JOIN FETCH Is NOT Suitable

Table Parts and Collections (@OneToMany)

JOIN FETCH is not optimal when an entity references a collection of data (e.g., document line items).

@Entity
public class Invoice {
    @Id
    private Long id;

    @ManyToOne
    private Customer customer;

    // ⚠️ PROBLEM: Collection of items!
    @OneToMany(mappedBy = "invoice")
    private List items;  // Could be 1, 10, 100+ items!
}

The Problem with Pagination

Wrong approach

@Query("SELECT DISTINCT i FROM Invoice i " +
       "LEFT JOIN FETCH i.items")  // ⚠️ PROBLEM!
Page<Invoice> findAll(Pageable pageable);

Why this fails

• Cartesian product: 1 Invoice with 10 items → 10 rows.
• Pagination breaks: Page size 20 might return only 2 invoices.
• COUNT inaccurate: Counts rows after the join, not invoices.

Hibernate Warning

HHH000104: firstResult/maxResults specified with collection fetch; applying in memory!

Hibernate loads all data and applies pagination in memory, negating any performance benefit.

Alternatives for Collections

• @EntityGraph – lets Hibernate fetch associations efficiently.
• Two separate queries – full control over each fetch.
• DTO projection – fetch only needed fields.
• Batch fetching – reduces the number of queries for collections.

Note: I’ll cover @EntityGraph in detail in a future article with a real document + line items example.

Comparison Table

Solution: JOIN FETCH for Flat Structures

Step 1: Creating Optimized Repository Methods

@Repository
public interface ExternalExchangeRateRepository 
        extends JpaRepository<ExternalExchangeRate, Long> {

    /**
     * Find latest rates by date and currencyFrom with currencies loaded.
     * Uses JOIN FETCH to prevent N+1 queries.
     */
    @Query("SELECT e FROM ExternalExchangeRate e " +
           "LEFT JOIN FETCH e.currencyFrom " +
           "LEFT JOIN FETCH e.currencyTo " +
           "WHERE e.exchangeDate = :date " +
           "AND e.currencyFrom.id = :currencyFromId")
    List<ExternalExchangeRate> findLatestRatesByCurrencyFromWithCurrencies(
            @Param("date") LocalDate date,
            @Param("currencyFromId") Long currencyFromId);

    /**
     * Find all exchange rates with currencies loaded in a single query.
     */
    @Query(value = "SELECT DISTINCT e FROM ExternalExchangeRate e " +
           "LEFT JOIN FETCH e.currencyFrom " +
           "LEFT JOIN FETCH e.currencyTo " +
           "ORDER BY e.exchangeDate DESC, e.id DESC",
           countQuery = "SELECT COUNT(e) FROM ExternalExchangeRate e")
    Page<ExternalExchangeRate> findAllWithCurrencies(Pageable pageable);
}