Optimizing Queries in Spring Data JPA

#java #springboot #performance #database

Spring Data JPA makes data access effortless — until a page that felt instant in development starts firing hundreds of queries in production. Here are the techniques I reach for most to keep queries fast and predictable.

1. Find and kill the N+1 problem

The classic culprit. You load a list of entities, then touch a lazy association in a loop — and each iteration triggers another query. Ten orders with their line items become 1 + 10 queries. At scale, that's what turns a 50 ms endpoint into a 2-second one.

The first step is simply to see it. Turn on SQL logging in development:

spring.jpa.show-sql=true
spring.jpa.properties.hibernate.format_sql=true
logging.level.org.hibernate.SQL=DEBUG

If you see the same query repeating with different IDs, you've found an N+1.

2. Fetch joins — load associations in one query

A JOIN FETCH tells Hibernate to pull the association in the same query instead of lazily loading it later:

@Query("SELECT o FROM Order o JOIN FETCH o.items WHERE o.status = :status")
List<Order> findByStatusWithItems(@Param("status") Status status);

One query, no surprises. Just be careful combining multiple collection fetches — it can produce a Cartesian product. For that, fetch one collection per query or use a @BatchSize hint.

3. Entity graphs — reuse without hand-writing JPQL

When you want the same eager-loading on a derived query, an @EntityGraph keeps it declarative:

@EntityGraph(attributePaths = {"items", "customer"})
List<Order> findByStatus(Status status);

4. Don't fetch entities you only read

For read-only endpoints (lists, reports, dropdowns) you rarely need full managed entities. A DTO projection selects only the columns you use, skips the persistence context, and returns less data:

public interface OrderSummary {
    Long getId();
    String getCustomerName();
    BigDecimal getTotal();
}

List<OrderSummary> findByStatus(Status status);

This is one of the highest-leverage changes for reporting screens — exactly the kind of high-concurrency workload where every skipped column adds up.

5. Always paginate large result sets

Returning an unbounded list is a latent outage. Let Spring Data page for you:

Page<OrderSummary> findByStatus(Status status, Pageable pageable);

For deep pagination on huge tables, prefer keyset (cursor) pagination over large OFFSET values, which force the database to scan and discard rows.

6. Push work to the database — and index it

Aggregations, filtering, and sorting belong in SQL, not in Java memory. And no query plan survives a missing index: make sure the columns in your WHERE and JOIN clauses are indexed, then confirm with EXPLAIN.

Rule of thumb: measure first. Turn on SQL logging, count the queries, then optimize the one that actually hurts — not the one you assume is slow.

Originally published at armfahim.com.

DEV Community