Performance: fetch strategies, batching and reading the SQL

This lesson closes the JPA-in-depth module by pulling its threads into one practical skill: making the data layer fast, and — the recurring theme — seeing what it does. JPA's performance problems are almost never mysterious; they are a handful of recognisable patterns, all visible in the generated SQL. This lesson is those patterns and how to measure them, verified against a running Dakiya.

The core discipline: know your query count

The single habit that separates a competent Spring developer from a beginner is the same as in every ORM: you know how many SQL statements an operation runs, and why — measured, not guessed. JPA hides SQL behind method calls (getHub(), findAll(), dirty checking), so the only way to know is to look. The tools:

• spring.jpa.show-sql=true — logs every statement. A wall of near-identical SELECTs is N+1, visible.
• spring.jpa.properties.hibernate.generate_statistics=true — Hibernate reports exact counts (query count, cache hits, etc.). This is how every number in this module was measured (6 vs 1, 3, 1).
• A test asserting the count — the regression guard, so a reintroduced problem fails CI, not production.

If you cannot answer "how many queries does this endpoint run?", you are not in control of its performance. Turn on SQL logging in development and glance at it as you build.

The recurring performance patterns

Nearly every JPA performance problem is one of these, all met earlier in the course, collected here as the checklist:

• N+1 queries (the biggest by far) — a lazy relationship accessed in a loop runs 1 + N queries. Fix with a fetch join or @EntityGraph. Verified: 6 → 1. Check every loop that touches a relationship.
• Loading whole entities to read a few fields — fix with a projection (previous lesson), fetching only the needed columns.
• EAGER fetching loading data you do not use (and cascading) — keep relationships LAZY and fetch on demand.
• Missing database indexes on filtered/sorted columns — add an index (below). The ORM cannot index for you.
• Reading everything then filtering/paginating in Java — filter and paginate in the query (Pageable, a WHERE), not in memory.
• Read-modify-write in a loop instead of a bulk update — the concurrency module returns to this.

None of these need deep magic to fix; they need noticing, which is why measuring comes first.

Fetch strategy: LAZY plus deliberate fetching

The relationships lesson established: default everything LAZY, then fetch what a specific operation needs eagerly, on that query. The tools, in order of preference:

• Fetch join (join fetch in JPQL) — pull the relationship into one query for the operation that needs it. Verified to collapse N+1 from 6 to 1.
• @EntityGraph — the same, declaratively on a repository method.
• Batch fetching (hibernate.default_batch_fetch_size) — turns N lazy loads into a few IN (...) queries; good for collections a fetch join would multiply awkwardly.

The anti-pattern to avoid is "make it EAGER to fix N+1" — that loads the relationship on every query of that entity, even when unneeded, and can cascade or cause cartesian products when several collections are fetched at once. LAZY by default; fetch join / entity graph where you have seen a relationship is used in a loop.

Indexes: the ORM will not add them

An index is a database structure that turns a full-table scan into a fast lookup — and JPA/Hibernate does not create indexes for your query patterns automatically. You declare them on the entity:

@Entity
@Table(indexes = {
    @Index(name = "idx_parcel_status", columnList = "status"),
    @Index(name = "idx_parcel_dest_city", columnList = "destinationCity")
})
public class Parcel { ... }

(In a Flyway-managed schema, you write the CREATE INDEX in a migration instead — same effect, owned by the migration.) Index the columns you filter or sort on frequently — Dakiya filters parcels by status and destinationCity, so those are worth indexing; a column you never query is not. Primary keys and unique constraints are indexed automatically; foreign-key columns often should be. As in the Django course: index what you query, not everything (every index slows writes and costs storage), and add an index in response to a query you have seen is slow — confirmed with the database's EXPLAIN — not on a hunch.

The workflow: measure, fix, re-measure

Put it together as an evidence-driven routine, not guesswork:

1. Find the slow operation — the SQL log, statistics, or a profiler shows which endpoint runs too many or too-slow queries.
2. Identify the pattern — N+1? whole-entity-for-a-few-fields? missing index? full scan?
3. Apply the matching fix — fetch join / projection / index / query-side filter.
4. Re-measure — confirm the query count or time actually dropped (as this module confirmed 6 → 1).

And the discipline that ties the whole module together: do the work in the database, fetch the narrowest shape and fewest rows the operation needs, in as few queries as sensible — and look at the SQL to know you did. JPA's convenience is real, but it is a tool you must be able to see through. An engineer who reads the generated SQL ships a fast data layer by default; one who never looks ships endpoints that pass a demo and collapse on production data. That difference — and the habit of measuring behind it — is exactly what makes this the module people are hired for.

Check your work

Know your query count. Measure with show-sql, generate_statistics (how this module's numbers were measured), and count-asserting tests — you cannot tune what you cannot see.

The recurring patterns. N+1 (→ fetch join/@EntityGraph, verified 6→1), whole-entity-for-a-few-fields (→ projection), EAGER loading unused data (→ LAZY), missing index (→ add it), read-then-filter-in-Java (→ filter/paginate in the query), read-modify-write loops (→ bulk).

Fetch strategy. LAZY by default; fetch join / entity graph / batch fetching on the operation that needs the relationship — never "make it EAGER" (loads it always, cascades, cartesian products).

Indexes. The ORM does not add them; declare @Index (or a Flyway CREATE INDEX) on columns you filter/ sort on frequently — not everything (writes/storage cost); add in response to a seen slow query, confirmed with EXPLAIN.

The workflow. Find the slow operation → identify the pattern → apply the fix → re-measure. Evidence, not hunches.

Practice

1. Turn on show-sql and generate_statistics; for one endpoint, state its exact query count and why.
2. Reproduce N+1 (6 queries) and fix it with a fetch join (1); confirm with statistics.
3. Replace a full-entity list query with a projection and count the columns each selects.
4. Add an @Index (or Flyway CREATE INDEX) on status; use the database's EXPLAIN on a WHERE status = ? query to confirm it uses the index.
5. Write a test asserting an endpoint runs at most k queries; break it by removing a fetch join and watch it fail.
6. Take a genuinely slow operation and walk the four-step workflow, re-measuring after the fix.

Official documentation

• Hibernate — Performance and fetching — Fetch strategies, batching, statistics.
• Spring Data — @EntityGraph and paging — Eager fetching per query.
• Jakarta Persistence — @Index / @Table — Declaring indexes on entities.

Next: Spring Security — the filter chain.