Optimizing Queries in Spring Data JPA

Arm Fahim — Mon, 07 Sep 2026 15:53:25 +0000

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.

Maintainable Reports with JasperReports Subreports

Arm Fahim — Mon, 07 Sep 2026 15:52:33 +0000

A single 40-band Jasper template that does everything becomes unmaintainable fast. Subreports let you compose reports from small, reusable pieces — here's how to wire them up without the usual headaches.

Why subreports?

A subreport is just a compiled report embedded inside another. Reach for them when you need to:

Repeat a detailed block per record (e.g. an invoice with its line items).
Reuse the same component — a header, a signature block — across many reports.
Break one enormous template into pieces different people can own.

Passing parameters down

The parent passes values to the child through <subreportParameter>. Keep the parent's report parameters as the single source of truth and forward what the child needs:

<subreport>
  <reportElement x="0" y="0" width="555" height="80"/>
  <subreportParameter name="INVOICE_ID">
    <subreportParameterExpression>$F{id}</subreportParameterExpression>
  </subreportParameter>
  <subreportExpression>$P{ITEMS_SUBREPORT}</subreportExpression>
</subreport>

Give the subreport its own data

There are two common ways to feed a subreport:

Pass a data source from the parent via $P{REPORT_DATA_SOURCE} — best when the parent already has the data in memory.
Pass a connection and let the subreport run its own query — cleaner when each section maps to its own SQL.

Compile the child to a .jasper and pass it in as a parameter or a pre-loaded object:

JasperReport itemsSub = JasperCompileManager.compileReport("items.jrxml");
params.put("ITEMS_SUBREPORT", itemsSub);

Pitfalls I learned the hard way

Compile every subreport. A stale .jrxml/.jasper mismatch is the #1 cause of "why is it showing old data?".
Watch the width. A subreport wider than the parent band silently overflows or clips. Match widths deliberately.
Set "Print When Detail Overflows" / stretch so a growing subreport pushes content down instead of overlapping.
Cache compiled reports. Compiling on every request is expensive — compile once at startup and reuse.

Performance

Reports are read-heavy, so the same rule from query optimization applies: fetch only the columns you render, do aggregation in SQL, and avoid running a fresh query per row inside a subreport when a single joined query would do.

Treat a report like code: small composable pieces, compiled artifacts under version control, and data-fetching that respects the database.