You inherit a large service where profiling shows dozens of read paths issue one database query per returned row. You cannot fix them all this quarter. How do you decide which ones to address, and what do you put in place so the pattern stops reappearing?

principalnice to knowfreq 33%

answer

rank by rows x call rate x round-trip, weight by criticality
unbounded N = latent outage, promote regardless of traffic
projections for read paths; fetch plans for graphs; batching for the tail
guardrails: lazy defaults + CI statement budgets + per-request metric
caching first = hiding the symptom

basics

~20 s

Rank by impact: rows returned x call rate x round-trip latency, weighted by user-facing criticality and connection-pool pressure. Fix the top few properly, then prevent recurrence with lazy-by-default mappings, use-case fetch plans or projections, and statement budgets enforced in CI.

solid answer

~60 s

Triage by measured impact, not by count of occurrences. For each path estimate rows x calls-per-minute x per-statement round-trip cost, then weight by whether it is on a user-facing critical path and whether it is contributing to connection-pool saturation. A rarely-called admin export returning 20 rows is noise; a 300-row list on the home screen called 50 times a second is the whole problem. Fix the top handful and re-measure — the distribution is almost always heavily skewed.

Choose the remedy per path. Read-only list and report endpoints usually want DTO projections that never build entities. Paths that genuinely need managed graphs want explicit fetch plans. Batch fetching is a broad, low-effort mitigation for the long tail you are not going to rewrite.

Then close the door. Make to-one mappings lazy so no query pays implicitly; put statement budgets on the important read paths in CI; expose statements-per-request as a metric with alerting; and make fetch cost part of code review for new endpoints.

Avoid the two failure modes: a global "fix" applied blindly, and a one-off cleanup with nothing preventing regression.

**Triage by measured impact, not by count of occurrences.** For each path estimate `rows x calls-per-minute x per-statement round-trip cost`, then weight by whether it is on a user-facing critical path and whether it is contributing to connection-pool saturation. A rarely-called admin export returning 20 rows is noise; a 300-row list on the home screen called 50 times a second is the whole problem. Fix the top handful and re-measure — the distribution is almost always heavily skewed. **Choose the remedy per path.** Read-only list and report endpoints usually want DTO projections that never build entities. Paths that genuinely need managed graphs want explicit fetch plans. Batch fetching is a broad, low-effort mitigation for the long tail you are not going to rewrite. **Then close the door.** Make to-one mappings lazy so no query pays implicitly; put statement budgets on the important read paths in CI; expose statements-per-request as a metric with alerting; and make fetch cost part of code review for new endpoints. Avoid the two failure modes: a global "fix" applied blindly, and a one-off cleanup with nothing preventing regression.

code

java · 9 lines

java

record OrderRow(Long id, String customerName, BigDecimal total) {}

List<OrderRow> rows = em.createQuery("""
        select new com.acme.OrderRow(o.id, c.name, o.total)
        from Order o join o.customer c
        where o.status = :s
        """, OrderRow.class)
    .setParameter("s", Status.OPEN)
    .getResultList();   // one statement, no managed entities

go deeper

for a junior

Focus on the mechanics you know — how the pattern arises and how a query-level fetch plan or a projection removes it — and defer on prioritisation.

for a middle

Propose measuring the worst endpoints first and describe the concrete remedies, noting that a projection suits read-only list paths.

for a senior

Present a ranked plan with impact estimates including connection-pool pressure, choose remedies per path, and add regression tests with statement budgets.

for a principal

Own the whole policy: mapping defaults, use-case fetch plans, CI budgets, a production statements-per-request metric, review criteria for new read paths, and an explicit decision about which paths are deliberately left alone.

The real question

There is no shortage of ways to fix an individual per-row query pattern. At this scale the interesting decisions are which ones are worth engineering time, which remedy fits which path, and what stops the next one from shipping. A candidate who jumps straight to a fetch-plan syntax has answered a different question.

Step 1 — quantify before deciding

Build a ranked list. For each affected path collect:

N — typical and p99 rows returned. Unbounded pages are the dangerous ones.
Call rate — from access logs or APM.
Round-trip cost — measured, not assumed; a cross-AZ or cross-region database changes the answer by an order of magnitude.
Position — user-facing synchronous path, background job, or admin tool.
Pool pressure — how long the path holds a pooled connection. This is often the binding constraint: a path that holds a connection for 200 ms at 50 rps needs 10 connections on its own.

Rank by N x rate x round-trip, then adjust for position. The distribution is nearly always Pareto — a few endpoints account for most of the wasted round trips — so the plan should be "fix five things properly", not "touch fifty".

Also identify paths where N is unbounded even if traffic is low today. Those are latent outages, not performance issues, and deserve promotion in the ranking regardless of current volume.

Step 2 — match the remedy to the path

Different paths deserve different treatment:

Read-only lists, exports, reports — stop returning entities. A projection that selects exactly the needed columns into a DTO removes the fetch plan from the picture entirely, avoids populating the persistence context with thousands of managed objects, and is usually the largest single win. It also decouples the response shape from the entity model.
Paths that genuinely mutate or need the graph — express the fetch plan on the query so the cost is visible where the use case lives.
The long tail you will not rewrite — batch fetching converts N statements into N/size statements with no code change. It is a mitigation rather than a cure (cost still grows with N), but it is cheap and broad, which is exactly right for the tail.
Reference data read on every request — a cache may be legitimate here, but only for genuinely static data, and with explicit invalidation. Reaching for caching first is the classic mistake: it hides the symptom, adds an invalidation problem, and returns after every deploy.

Be explicit that a global toggle is not a strategy. Making everything eager, join-fetching everywhere, or setting one batch size across the model each trade one pathology for another (Cartesian products, over-fetching, memory pressure).

Step 3 — prevent recurrence

A cleanup with no guardrail regresses within two quarters. Three layers:

Defaults. Every to-one association mapped lazy, so no query pays for data it did not ask for. This is a one-time mechanical change with a real risk of surfacing latent lazy-access bugs, so it is scheduled work, not a drive-by.

Automated detection. Statement budgets asserted in tests on the important read paths, failing the build when the count moves; a request-scoped statement counter in production that logs or emits a metric above a threshold; and statements-per-request tracked per endpoint with alerting. The production signal catches what tests cannot — real cardinality and real data growth.

Process. Add "what is the statement count for this endpoint at p99 rows?" to the review checklist for new read paths. Make the expected count part of the endpoint's definition of done. Cheap, and it shifts the cost from detection to design.

Step 4 — sequence and communicate

Fix the top items, measure the delta, and publish it — statements per request and p99 latency before and after. That evidence is what buys time for the guardrail work, which otherwise looks like unfunded engineering hygiene. Then let the long tail be handled by batching plus the CI budget, and revisit the ranking with fresh production data rather than the original list.

Judgement calls worth voicing

Not everything must be fixed. An internal report run twice a day at 40 rows can stay as it is, forever, and saying so demonstrates prioritisation.
Latency budget, not query count, is the goal. If the round trip to the database is 100 microseconds and N is 20, the pattern is nearly free; the same code across a region boundary is a 200 ms outage. The environment is part of the decision.
Beware of trading round trips for memory. Aggressive join fetching of multiple collections produces row multiplication and heap pressure; the fix for one pathology should not create another.

## The real question There is no shortage of ways to fix an individual per-row query pattern. At this scale the interesting decisions are **which ones are worth engineering time**, **which remedy fits which path**, and **what stops the next one from shipping**. A candidate who jumps straight to a fetch-plan syntax has answered a different question. ## Step 1 — quantify before deciding Build a ranked list. For each affected path collect: - **N** — typical and p99 rows returned. Unbounded pages are the dangerous ones. - **Call rate** — from access logs or APM. - **Round-trip cost** — measured, not assumed; a cross-AZ or cross-region database changes the answer by an order of magnitude. - **Position** — user-facing synchronous path, background job, or admin tool. - **Pool pressure** — how long the path holds a pooled connection. This is often the binding constraint: a path that holds a connection for 200 ms at 50 rps needs 10 connections on its own. Rank by `N x rate x round-trip`, then adjust for position. The distribution is nearly always Pareto — a few endpoints account for most of the wasted round trips — so the plan should be "fix five things properly", not "touch fifty". Also identify paths where N is **unbounded** even if traffic is low today. Those are latent outages, not performance issues, and deserve promotion in the ranking regardless of current volume. ## Step 2 — match the remedy to the path Different paths deserve different treatment: - **Read-only lists, exports, reports** — stop returning entities. A projection that selects exactly the needed columns into a DTO removes the fetch plan from the picture entirely, avoids populating the persistence context with thousands of managed objects, and is usually the largest single win. It also decouples the response shape from the entity model. - **Paths that genuinely mutate or need the graph** — express the fetch plan on the query so the cost is visible where the use case lives. - **The long tail you will not rewrite** — batch fetching converts N statements into N/size statements with no code change. It is a mitigation rather than a cure (cost still grows with N), but it is cheap and broad, which is exactly right for the tail. - **Reference data read on every request** — a cache may be legitimate here, but only for genuinely static data, and with explicit invalidation. Reaching for caching first is the classic mistake: it hides the symptom, adds an invalidation problem, and returns after every deploy. Be explicit that a global toggle is not a strategy. Making everything eager, join-fetching everywhere, or setting one batch size across the model each trade one pathology for another (Cartesian products, over-fetching, memory pressure). ## Step 3 — prevent recurrence A cleanup with no guardrail regresses within two quarters. Three layers: **Defaults.** Every to-one association mapped lazy, so no query pays for data it did not ask for. This is a one-time mechanical change with a real risk of surfacing latent lazy-access bugs, so it is scheduled work, not a drive-by. **Automated detection.** Statement budgets asserted in tests on the important read paths, failing the build when the count moves; a request-scoped statement counter in production that logs or emits a metric above a threshold; and statements-per-request tracked per endpoint with alerting. The production signal catches what tests cannot — real cardinality and real data growth. **Process.** Add "what is the statement count for this endpoint at p99 rows?" to the review checklist for new read paths. Make the expected count part of the endpoint's definition of done. Cheap, and it shifts the cost from detection to design. ## Step 4 — sequence and communicate Fix the top items, measure the delta, and publish it — statements per request and p99 latency before and after. That evidence is what buys time for the guardrail work, which otherwise looks like unfunded engineering hygiene. Then let the long tail be handled by batching plus the CI budget, and revisit the ranking with fresh production data rather than the original list. ## Judgement calls worth voicing - **Not everything must be fixed.** An internal report run twice a day at 40 rows can stay as it is, forever, and saying so demonstrates prioritisation. - **Latency budget, not query count, is the goal.** If the round trip to the database is 100 microseconds and N is 20, the pattern is nearly free; the same code across a region boundary is a 200 ms outage. The environment is part of the decision. - **Beware of trading round trips for memory.** Aggressive join fetching of multiple collections produces row multiplication and heap pressure; the fix for one pathology should not create another.

How do you justify spending a quarter's capacity on this when nothing is currently on fire?
By showing the numbers rather than the principle: statements per request and connection-pool checkout time for the top endpoints, and the projection of where they land at expected data growth, since the cost scales with row counts rather than staying flat. Then fix two or three paths first and publish the measured latency and pool-utilisation delta. A demonstrated improvement on a real endpoint is a far stronger argument for the remaining work than any description of the pattern.
Why not simply enable batch fetching globally and consider the problem solved?
Because it reduces the constant factor without changing the shape — cost is still proportional to the number of rows, so an unbounded result set still degrades, just later. It also does nothing for over-fetching entities you never needed, and a single batch size across a whole model is wrong for most associations. It is the right tool for the tail of low-impact paths you have deliberately chosen not to rewrite, alongside proper fetch plans or projections on the paths that matter.

saying these in an interview costs you the question

Proposing to fix every occurrence with no prioritisation or measurement.
Reaching for a cache first, which hides the pattern and adds an invalidation problem.
Applying one global setting — eager everywhere, or a single batch size — as the strategy.
Doing a cleanup with no CI or production guardrail, guaranteeing regression.
Ignoring connection-pool occupancy and treating it purely as a latency issue.
Assuming the round-trip cost is the same in every deployment topology.

You inherit a large service where profiling shows dozens of read paths issue one database query per returned row. You cannot fix them all this quarter. How do you decide which ones to address, and what do you put in place so the pattern stops reappearing?

§ answer

basics

solid answer

code

§ go deeper

▾level notes4 levels

▾deep dive~4 min

The real question

Step 1 — quantify before deciding

Step 2 — match the remedy to the path

Step 3 — prevent recurrence

Step 4 — sequence and communicate

Judgement calls worth voicing

▾common follow-ups2 questions

▾red flags6 flags

§ context

▾related questions4 questions

▾related topics3 topics

The real question

Step 1 — quantify before deciding

Step 2 — match the remedy to the path

Step 3 — prevent recurrence

Step 4 — sequence and communicate

Judgement calls worth voicing

answer

go deeper

context