Why might filling owner collections with a few extra batched statements beat one joined fetch, and what does each cost?
answer
- same graph, different wire cost
- a join repeats the owner columns
- secondary statements repeat the waiting
- fan-out and row width decide it
- latency flips the answer
basics
~20 sA joined fetch buys one round trip and pays by repeating every owner column once per child row. Batched secondary statements send each row once, keeping bytes near owners plus children, and pay in extra round trips.
solid answer
~50 sThe two shapes trade the same currency in opposite directions. One joined statement crosses owners with children, so each owner's columns travel once per matching child; wide owners with many children turn a small logical result into a large physical one, and the driving query's own shape changes because the result is no longer one row per owner. Batched secondary statements leave the driving query exactly as written and send each owner row once and each child row once, which is close to the minimum data possible — but every batch is another round trip, so on a high-latency link a handful of statements can outweigh the bytes they save. The honest rule is that joins win where latency dominates and the fan-out is small; secondary statements win where the fan-out is large, the owner rows are wide, or the driving query's shape has to be preserved.
go deeper
Recall that both approaches end with the same objects loaded, and that a join repeats the owner's columns for every child row while separate statements send each row only once.
Explain the two currencies — bytes and round trips — and name what pushes the answer each way: fan-out, owner row width, and network latency.
Show that you would measure rather than assert: fan-out distribution, the wide columns on the owner, link latency, and statements per request before and after the change.
Frame it as a policy per access path rather than a house style, and account for the consistency difference and for what happens as fan-out grows with the data.
## Two ways to end up with the same object graph You have owners, and each owner has a collection. Either you ask for them together in one statement that joins them, or you ask for the owners and then ask for the children in a small number of follow-up statements, batched by key or by re-running the predicate. Both end with the same objects in memory. They differ in **what crosses the wire, how many times you wait, and what the driving query's result looks like on the way**. ## What the joined shape costs A join produces one row per owner-child pair. The owner's columns are therefore transmitted once for each child it has. Three effects follow: - **Bytes grow with the product, not the sum.** 50 owners with 20 children each is 1000 rows, each carrying a full copy of its owner's columns. Send the same data as owners plus children and it is 1050 rows in total, each sent once. - **Wide owners amplify it.** The penalty is proportional to how wide the repeated side is. A narrow owner row repeated twenty times is cheap; an owner carrying long text columns repeated twenty times is the whole problem. - **The result shape changes.** The driving query no longer returns one row per owner, which is why joined fetching interacts awkwardly with anything written against the assumption that it does. The duplicate-parent handling that follows from this is its own subject. What it buys is real and often decisive: **one round trip**, one plan, one consistent read of both sides at the same instant. ## What the secondary shape costs Batched follow-up statements send each row exactly once. There is no repetition, so bytes are close to the theoretical minimum for the objects you asked for. The costs sit elsewhere: - **Latency multiplies by statement count.** Every statement is a wait. On a link with a 1 ms round trip, ten batches are 10 ms of pure waiting; at 20 ms, they are 200 ms, and the bytes saved stop mattering. - **The owner set is located more than once.** A key list has to be looked up by key; a re-run predicate evaluates the owner predicate a second time. Either way, the engine does work it already did. - **Cold caches punish the extra statements more.** The first execution of each shape does work that later executions reuse, so a rarely exercised path pays a setup cost per statement rather than once. - **The two sides are read at different instants** unless the surrounding transaction gives them a single snapshot, so concurrent writes can show up in one and not the other. ## Putting the trade in one table | | One joined statement | Batched secondary statements | |---|---|---| | Round trips | One | One per batch, plus the driving query | | Bytes on the wire | Owners repeated per child | Each row once | | Driving query's result shape | Changed by the fan-out | Untouched | | Consistency between the sides | One read | Separate reads unless a snapshot spans them | | Sensitivity to owner width | High | None | | Sensitivity to network latency | Low | High | ## Choosing, with evidence rather than instinct The measurements that settle it are cheap to take: 1. **Fan-out** — average and worst-case children per owner. Two is nothing; two hundred changes everything. 2. **Owner row width** — specifically the wide columns, since they are what the join repeats. 3. **Round-trip latency** to the database. A same-host connection and a cross-zone connection give opposite answers to the same question. 4. **Statements per request** on the path today, so the change is visible as a number afterwards. A second collection on the same owners is where the joined shape stops being an option at all, because two collections joined at once multiply against each other; the mechanics of that multiplication belong to their own subject, but knowing that the boundary exists is part of choosing sensibly here. ## The answer an interviewer wants Not "joins are faster" or "avoid N+1 by joining", but the trade stated in both directions: a join collapses waits and pays in repeated owner columns; secondary statements minimise bytes and pay in waits. Then the conditions — fan-out, owner width, latency — and how you would measure them on the path in front of you.
- Why does owner row width matter so much to the joined shape?Because the join repeats the owner side once per child row. The cost of the repetition is the width of what is repeated, so a narrow key-and-name owner is barely affected while an owner carrying long text or many columns turns a modest fan-out into a large transfer. Projecting fewer owner columns shrinks the penalty directly.
- When does saving bytes stop being worth the extra statements?When round-trip latency times statement count exceeds the transfer time saved. Across a high-latency link with a small fan-out, one joined statement can finish before the second batch has even been acknowledged. Measure the link before assuming either shape wins.
- Do the two shapes see the same data?Only if a single snapshot spans them. One joined statement reads both sides at one instant; secondary statements are separate reads, so a concurrent insert can appear in the children but not in the owners you already hold. Where that matters, the surrounding transaction's isolation has to provide the guarantee.
One delivery van carrying a copy of the manifest stapled to every parcel, against several vans each carrying its load once.
saying these in an interview costs you the question
- Says one statement is always cheaper than several
- Ignores that a join repeats owner columns once per child row
- Forgets round-trip latency when counting the cost of extra statements
- Thinks secondary statements change the driving query's result shape
- Assumes both shapes read the data at the same instant
- Treats fan-out as irrelevant to the choice