JOIN FETCH May Not Save You

An 8-item shopping cart. One GET request. 33 SQL statements.

That's garden variety N+1

The victim is Shopizer 3.2.7, verified byte-identical to upstream by git diff. No planted bug, no stripped fetch graph. The cart read path resolves each item's SKU with a native query, loads the product by id through a wide fetch graph (descriptions, availabilities, prices, categories, images, attributes, variants), then loads the variant and the attribute by id. Four statement shapes, eight repetitions each, plus the cart load itself. On the path a store exists to serve.

The transcript came out of ExoBench's JPA N+1 detector, an MCP server your coding agent calls. The agent hands it a mapping and an access path. It compiles them, runs them against an ephemeral database on the real ORM, and returns the SQL that was actually prepared, plus the statement shapes it flagged. No build of the target app, no runtime, no JVM agent, no specialized instrumentation annotations, no production traffic. It runs Hibernate 5.6 through 7 and EclipseLink 2.7 through 5.0, and every probe below names the engine it ran on.

Three codebases went through it. Shopizer 3.2.7 on Hibernate 5.6.15. Apache Fineract, which is on EclipseLink 4.0.9 with static weaving, and that pairing matters below. Spring PetClinic, probed on Hibernate 6.6.

Every decent foundation model can find N+1 fan-outs reliably well, and the findings are real but ordinary. An LLM agent with no access to a real compiler is already good at it, because public codebases are full of exactly these mistakes, and that's what the models are trained on. Point one at a repository and it will hand you a plausible list of N+1 sites by pure reading.

The breakdown comes when you start asking your LLM to reason correctly about the right fixes. The same agent that finds the problem, will recommend a defunct folklore-based fix with the same confidence, because it was trained on folklore fixes! My agent made most of the same bad predictions that I would have, and the transcripts falsified us both. Seven measurements are below, and each contradicts something a competent JPA developer tells his juniors over a campfire.

In no particular order, here they are:

A JOIN FETCH in a second query heals nothing you already hold

The instinct: you loaded your loans, you see the installment-charge N+1, so you run a second query with JOIN FETCH on those collections and keep iterating the entities from the first query. Simplified, the pattern looks like this:

List<Loan> loans = em.createQuery("SELECT l FROM Loan l", Loan.class)
    .getResultList();

// The "fix": a second query that fetches the charge collections
em.createQuery("""
    SELECT DISTINCT i FROM LoanRepaymentScheduleInstallment i
    LEFT JOIN FETCH i.installmentCharges
    WHERE i.loan.id IN :loanIds""", LoanRepaymentScheduleInstallment.class)
    .setParameter("loanIds", loanIds)
    .getResultList();  // fetched result discarded

// ...then keep iterating the entities from the FIRST query
for (Loan loan : loans)
    for (var installment : loan.getRepaymentScheduleInstallments())
        installment.getInstallmentCharges().size();  // still one SELECT each

Measured on EclipseLink 4.0.9, in Apache Fineract's loan module: the N+1 stayed at 12. EclipseLink does not attach the second query's fetched collections onto instances you already hold. The fetch happened, the SQL for it ran, and the entities in your hands never noticed.

If you know anything about Functional Programming, you'd immediately suspect the right culprit. Also you'd know that the right Mont<T> in the right place would have made the problem brainlessly obvious. Care to take a guess at what it is?

The fix that works is to iterate the second query's result, or to root the query at the child entity in the first place. This was measured on EclipseLink; I have not run the same experiment on Hibernate, so I won't claim it either way.

Fetching the lazy parent collection removes exactly one level

Fineract's savings account transactions carry EAGER chargesPaid and taxDetails. This is the actual mapping:

// SavingsAccount, the collection you remembered to fetch
@OneToMany(mappedBy = "savingsAccount", fetch = FetchType.LAZY)
protected List<SavingsAccountTransaction> transactions;

// SavingsAccountTransaction, the grandchildren you didn't
@OneToMany(mappedBy = "savingsAccountTransaction", fetch = FetchType.EAGER)
private Set<SavingsAccountChargePaidBy> savingsAccountChargesPaid;

@OneToMany(mappedBy = "savingsAccountTransaction", fetch = FetchType.EAGER)
private List<SavingsAccountTransactionTaxDetails> taxDetails;

The obvious fix for the transaction fan-out is to fetch the collection at load time:

List<SavingsAccount> accounts = em.createQuery("""
    SELECT DISTINCT s FROM SavingsAccount s
    LEFT JOIN FETCH s.transactions""", SavingsAccount.class)
    .getResultList();

After that fix: 25 statements. The fetch removed the transaction selects and the EAGER grandchildren kept firing, one select per child per row. Same experiment on fixed-deposit product charts: still 19 statements after JOIN FETCH p.charts.

Fetching a LAZY parent collection removes exactly one level of the tree. EAGER associations below it survive. And no, EclipseLink bytecode 'weaving' does not rescue you either, a thing I believed for a while until the counts said otherwise. Weaving does not allow laziness to be projected, it makes declared-LAZY references actually lazy; nothing more.

setMaxResults keeps your page size as a suggestion

Shopizer's product listing combines a collection join fetch with setMaxResults. Simplified from ProductRepositoryImpl:

StringBuilder qs = new StringBuilder();
qs.append("select distinct p from Product as p ");
qs.append("join fetch p.availabilities pa ");  // a Collection<T> fetch
qs.append("join fetch p.descriptions pd ");
// ...15 more join fetch lines...

Query q = em.createQuery(qs.toString());
q.setFirstResult(first);
q.setMaxResults(max);  // your page size, as a suggestion

Hibernate cannot apply a SQL LIMIT there, because cutting rows would truncate collections mid-parent. It admits this in exactly one line of log output, Hibernate's in-memory pagination warning:

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

Then it does what the warning says: emits the SQL with no LIMIT at all, loads the entire filtered result set into memory, and paginates it there. The probe measured entityLoadCount=18 to serve a page of 2.

The same defect reproduced on Spring PetClinic under Hibernate 6.6, where the same warning is renumbered HHH90003004. Two codebases, two major versions, same silent behavior. Your page size is honored in the result you see and ignored in the work the database did to produce it.

The fix is to paginate ids first, then fetch collections for just those ids:

List<Long> ids = em.createQuery(
        "select p.id from Product p order by p.id", Long.class)
    .setFirstResult(first).setMaxResults(max)   // real LIMIT: only ids, no collections
    .getResultList();

List<Product> page = em.createQuery(
        "select distinct p from Product p join fetch p.availabilities where p.id in :ids",
        Product.class)
    .setParameter("ids", ids)
    .getResultList();

Measured on the same shape: the id query runs with a real offset ? rows fetch first ? rows only, the fetch query touches only the page's ids, no warning, and entityLoadCount drops from 18 to 6.

Caches have scope, and it might not be what you think

PetClinic sets spring.jpa.open-in-view=false, and its owner controller loads the same owner twice per request, once in a @ModelAttribute method and once in the handler:

@ModelAttribute("owner")  // Spring runs this before every handler in the class
public Owner findOwner(@PathVariable(required = false) Integer ownerId) {
    return ownerId == null ? new Owner()
        : this.owners.findById(ownerId).orElseThrow(...);  // load #1
}

@GetMapping("/owners/{ownerId}")
public ModelAndView showOwner(@PathVariable int ownerId) {
    Owner owner = this.owners.findById(ownerId).orElseThrow(...);  // load #2
    // ...
}

Every JPA developer's model says the second call hits the first-level cache. Same primary key, same request. Measured: 6 statements, the full graph loaded twice.

The model is wrong about where the cache lives. The first-level cache is not "the cache for this request", it lives inside the persistence context so it's only as wide as the context is. Nothing in JPA ties a persistence context to an HTTP request by itself. With "Open Session In View" (OSIV) off and no @Transactional spanning the controller, each Spring Data repository call opens its own persistence context, runs its one query, and closes it, destroying the cache it just populated. Two findById calls, two contexts, two full graph loads. The control run put both calls inside one session and got the identical instance back with 0 extra SQL:

With Open Session In View enabled, a Spring interceptor that opens one persistence context before the controller runs and holds it open through view rendering. Like many sane projects, PetClinic switches it off, for textbook reasons: OSIV pins a database connection across template rendering which means unless you're careful, your N+1s can move all the way out to the view-layer where you'll have to fight for every ounce of sanity trying to debug why your JSON serializer is suddenly causing thousands of database reads. Naturally you'll want to turn off OSIV the second you acquire some scars from seeing Jackson, Proxy dereferences, and javax.sql packages in the same stack-trace. The consequence you might not see is that your caches might suddenly stop working.

The simplest fix is just @Transactional in the right place. Problem is nobody audits transaction scope while reading a controller, and when I say 'nobody' I'm including your coding agent!

The Session Cache never saw your Secondary Key

The broader version of that belief: repeated lookups in one session are absorbed by the cache. That holds for session.get(id), and my agent confirmed it collapses repeats.

It holds for nothing else. Hibernate's first-level cache is keyed by entity id only. Shopizer looks products up by SKU, a secondary key:

// ReadableOrderProductPopulator, runs once per order line
product = productService.getBySku(source.getSku(), store, language);

// ProductServiceImpl.getBySku, always two statements
List<Object> products = productRepository.findBySku(productCode, merchant.getId());
BigInteger id = (BigInteger) products.get(0);
return productRepository.getById(id.longValue(), merchant, language);

The SKU query on the first line of getBySku is the one the cache can never absorb, so the checkout test measured 25 SKU selects for 5 distinct SKUs. The order probe made the point unmissable: a 5-order page with 2 lines each ran the identical SKU lookup 10 times, one deliberately repeated SKU accounting for 4 of them, while the by-id product loads sitting next to them in the same transcript collapsed from 10 calls to 5 statements, one per distinct product. Same session, same entities, and the only difference is which key the cache is built on.

Scale that to production shapes: a 25-order admin page averaging four lines is over 200 heavyweight statements per page view, and the session cache is not coming to help.

EclipseLink drops the JOIN FETCH you wrote through an alias

This one and the next came out of an agent audit of Fineract's working-capital loan module on Aug 26, on EclipseLink 4.0.9 with static weaving, the same configuration Fineract's build actually applies.

Fineract has repositories that look thoroughly defended, with nested fetches written through an alias, like this one:

@Query("""
    select paidBy from WorkingCapitalLoanChargePaidBy paidBy
    join fetch paidBy.wcLoanTransaction transaction
    join fetch paidBy.wcLoanCharge loanCharge
    join fetch loanCharge.charge
    where transaction.id in :transactionIds
    """)
List<WorkingCapitalLoanChargePaidBy> findByTransactionIdIn(/* ... */);

The last fetch, join fetch loanCharge.charge, reaches the second level through the alias loanCharge. Measured, that query's root SQL contains no join to m_charge at all. EclipseLink parsed the alias form, generated nothing for the second level, and moved on, no warning, no log line. At runtime each charge arrives by its own SELECT ID, name FROM m_charge WHERE (ID = ?): 7 statements for 6 rows. Rewrite the same fetch as a path from the root:

join fetch paidBy.wcLoanCharge
join fetch paidBy.wcLoanCharge.charge

and the root SQL joins m_charge, 5 statements, byte-identical output. Four Fineract repositories use the alias form, which means four repositories that read as batched and are not. The output being identical is exactly why nobody had noticed: nothing was ever wrong except the count.

Three @ManyToOne hiding inside an @Embeddable

Same module, the paged loan list: 52 SQL statements for a 6-row page. 18 of those 52 come from three LAZY @ManyToOne associations that live inside an @Embeddable:

@Entity
public class WorkingCapitalLoan {
    @Embedded
    private WorkingCapitalLoanProductRelatedDetails loanProductRelatedDetails;
}

@Embeddable
public class WorkingCapitalLoanProductRelatedDetails {
    @ManyToOne(fetch = FetchType.LAZY)
    private DelinquencyBucket delinquencyBucket;    // m_delinquency_bucket

    @ManyToOne(fetch = FetchType.LAZY)
    private WorkingCapitalBreach breach;            // m_wc_breach

    @ManyToOne(fetch = FetchType.LAZY)
    private WorkingCapitalNearBreach nearBreach;    // m_wc_near_breach
}

Six selects each against m_delinquency_bucket, m_wc_breach, and m_wc_near_breach, one per row.

A JOIN FETCH review has no chance here, because the associations aren't on the entity. They're inside a value object embedded in it, and no repository query in the module fetches through the embeddable. Every read path that touches these loans pays 3 selects per row.

The fix, eclipselink.join-fetch hints through the embeddable path plus a rewritten root query, folded the whole page into 5 statements, output byte-identical. 52 to 5 on the module's hottest read.

Where the tool was wrong

These are the places the process got it wrong, and they're going in the post rather than waiting to be found in the comments.

The detector has a known miss. Fineract's Office.children hierarchy walk is recursive: the driving query is WHERE parent_id = ? querying itself. The detector flags repeated statement shapes that differ from the driving query, so on this walk it stayed silent while the walk cost 10 statements at 3 regional offices. The workaround is to scale the input at N and 3N and read the raw statement count instead of the findings array. An empty findings array is evidence, not proof.

A simplified test entity changed the answer. Early on, "JOIN FETCH the transactions gives 1 statement" was measured on a stripped port that omitted the EAGER children. On the faithful mapping the identical fetch costs 25. Reproduction fidelity decides the result, which is an argument for porting the real mapping and also a warning about anyone's quick repro, including ours.

The agent without the tool found things on its own. We ran head-to-head audits, one agent reading code, one agent reading code plus measuring through the MCP server. On PetClinic the measuring agent missed the duplicate @ModelAttribute load entirely and the reading agent found it. On Fineract the reading agent uniquely caught three write-path loops the measuring agent never got to examining: a per-period delinquency tag query inside close-of-business classification, a reprocess replay with a saveAndFlush inside the loop, and per-transaction journal restatement. Each configuration ran once, so the whole experiment is worthwhile to repeat. I'll follow that up in a future post.

What a transcript proves, and what it doesn't

The probes are agent-ported reproductions of the real mappings and queries, and a port can be wrong, see above. The Fineract close-of-business numbers come from the sandbox reproducing the reader path, and that path has since been checked against the real thing. We ran Fineract itself against Postgres with EclipseLink's statement logging switched on, seeded loans with repayment schedules, and executed the actual close-of-business batch twice: once as-mapped, once with the JOIN FETCH patch. As-mapped, every loan the batch touched fired a separate repayment-schedule select, the per-loan extra statement the sandbox predicted. Patched, those standalone selects disappeared entirely; the schedule rode along in the loan query as a LEFT OUTER JOIN on m_loan_repayment_schedule. The sandbox database is H2: statement counts transfer to your database, timings don't, which is why the wall-clock pricing was done separately on Postgres, where the per-row pattern scales linearly and every real statement adds parse, plan, and a network round trip on top.

The Fineract loan-schedule finding is patched on a branch of the real repository with a passing unit test, verified with the exact JPQL that landed in the repository class, and confirmed live under the close-of-business batch as described above.

On positioning, since someone will ask: Hypersistence Optimizer inspects JPA metadata in a live session and catches mapping-shape problems at bootstrap. QuickPerf needs test annotations. APM needs production traffic. ExoBench needs the mapping. Different mechanisms, and genuinely different coverage, EclipseLink included. What's different is the fundamental capability: a static analyzer tells you a mapping looks wrong. It cannot tell you your fix will not work because it is a metadata analyzer. When you dig deep enough, that's basically the same as your coding agent: it has read more JPA than either of us and will point at your N+1s for free, but not much more than that. The finding half of this problem is solved. The verifying half is the ExoBench product.

Try to break it

I'm looking for 20 people with a real JPA codebase and a coding agent to try to break this.

The detector is an MCP tool. You connect your agent to it, the agent ports your mapping and access path, and the transcript comes back with the counts. If it flags something wrong in your codebase, or misses something you know is there, I want that transcript more than I want a testimonial. The known-miss section above exists because someone did exactly that to us.

One suggestion for the run: before each probe, have your agent write down what it expects: the statement count, and what the fix it's proposing will change. The prediction will be fluent, reasonable, and confidently argued. Keeping score of how often it survives the transcript is the fastest way to understand what this tool is for.

Setup is a few minutes with any MCP-capable agent, Claude, Cursor, or otherwise: getting started. For one of the 20 walkthrough slots, email me at nplus1@exobench.ai and bring the codebase.