skip to content

For a paged list read, how does link cardinality decide between a join fetch and a batch fetch?

level: middleimportance: must knowfreq 64%

answer

  1. what the join does to row count
  2. columns widen, rows multiply
  3. collections want a second statement
  4. two collections in one statement multiply
  5. page size picks keys or subquery

basics

~20 s

A to-one link only adds columns, so a join fetch usually fits. A to-many link adds rows and interacts badly with paging and with a second collection, so a bulk statement covering the whole page usually fits better.

solid answer

~40 s

Cardinality decides what a join does to the result set. Joining a **to-one** link widens each row by a few columns: the row count is unchanged, paging still works, and one round trip does everything. Joining a **to-many** link changes the row count — the parent's own columns come back once per child — so the payload grows with the average collection size, and pulling a second collection into the same statement multiplies the two together. Paging on top of a joined collection also stops meaning what it says, because a limit applies to result rows rather than parents. So collections are usually loaded by a second bulk statement covering the whole page: two round trips, no multiplication, and a limit that still counts parents. Page size and parent width move the boundary.

go deeper

for a junior

Remember the shape rule: a to-one link adds columns, a to-many link adds rows. That one sentence gets you most of the way through this question.

for a middle

Explain the consequences — repeated parent columns, a limit that counts result rows, two collections multiplying — and say which remedy you would reach for and why.

for a senior

Talk about page size and payload width as real levers, and about confirming the change against statement counts on the actual endpoint.

for a principal

Consider what the codebase should default to, and how much per-read tuning is worth its maintenance before a narrower read model is the better answer.

## The question cardinality actually answers Both remedies remove the per-row statements. They differ in what they do to the **shape of the result**, and cardinality is what determines that shape. - Joining a **to-one** link appends the linked row's columns to each parent row. One row per parent, before and after. - Joining a **to-many** link appends a row *per child*. A parent with twelve children appears twelve times, with all of its own columns repeated twelve times. Everything else follows from that difference. ## To-one links: prefer the single statement For a link that reaches at most one row, a join fetch is normally the better remedy: - **One round trip.** No second statement, no key list, no ordering work in the layer. - **Stable row count.** Filtering, sorting and paging keep meaning exactly what they meant before. - **Predictable growth.** The result grows by the width of the linked row, not by a multiplier. The counter-case is width. If the parent is wide — long text columns, a big serialised blob — and the read is a page of many parents, joining a further table onto it multiplies nothing but still moves more bytes than needed. There the honest answer is often a projection: stop fetching the wide columns at all. Chains of to-one links are the other place to slow down. Each one is individually cheap, but joining five of them into one statement produces a wide row for every parent and gives the database a larger plan to choose from. Three or four is usually unremarkable; a dozen is a read that has stopped describing what the screen needs. ## To-many links: prefer the bulk second statement For a collection, the second statement usually wins: | Concern | Join fetch of a collection | Bulk second statement | |---|---|---| | Rows returned | parents x average children | parents, then children, separately | | Parent columns | repeated on every child row | sent once | | Limit on the page | applies to result rows, not parents | applies to parents, as intended | | Two collections at once | the two multiply each other | one extra statement each | | Round trips | 1 | 2, or one per collection | The row multiplication a wide fetch produces, and how layers cope with it, is its own subject; the point for remedy selection is that it is the predictable consequence of joining something with cardinality greater than one, and it gets worse as collections get larger. Two numbers make the trade concrete. For a page of 100 parents each holding 20 children, a join returns 2,000 rows carrying 100 copies of every parent column; the bulk second statement returns 100 rows plus 2,000 narrow child rows, and the parent columns cross the wire once. The saving grows with the parent's width and with the average collection size, and it costs exactly one extra round trip. ## Page size, the other lever The two bulk shapes react differently to page size. 1. **Keyed by the page's identifiers.** Collect the parent identifiers already in hand and ask for all their children in one statement. Cheap and precise, but the key list grows with the page and eventually has to be split into chunks — turning "one extra statement" into a small, bounded handful. 2. **Re-deriving the parents inside a subquery.** The child statement restates the parent query as a subquery, so no identifier list travels and page size does not change the statement's text. The price is running the parent query's predicate a second time, which matters when it was expensive. Large pages therefore tilt towards the subquery shape, small pages towards the keyed shape. Both remain bounded; neither reintroduces a statement per row. ## Working through it A useful order of questions for a link that is causing per-row statements: 1. **Is it to-one or to-many?** To-one: start with the join. To-many: start with the bulk second statement. 2. **How many such links does this read touch?** More than one collection in one statement is a product; keep at most one in the join. 3. **How wide is the parent?** Wide parents punish repetition, which argues against joining collections and sometimes for a projection instead. 4. **How big is the page?** Large pages argue for the subquery shape over a long identifier list. 5. **Did the statement count actually drop?** Confirm the change on the real code path rather than assuming. ## The trade in one line A join buys a round trip with bandwidth and row multiplication; a bulk second statement buys narrow, single-copy rows with an extra round trip. Cardinality decides which of those currencies you are actually spending, which is why it is the first question and not the last.

  • Why does a limit behave unexpectedly over a joined collection?
    The limit is applied by the database to result rows, and a joined collection produces several rows per parent. Asking for twenty rows can return five parents with their children, not twenty parents. Layers that support it work around this by paging the parents in one statement and fetching the children for that page separately.
  • When would you join a collection anyway?
    When the collection is small and bounded, the parent is narrow, only one collection is involved, and the read is not paged — a single detail record with its handful of lines, for instance. The multiplication is then trivial and one round trip is genuinely simpler.
  • Do these choices differ between a full mapper and a query builder?
    The trade is the same, but the ergonomics differ: some layers can be told to load a link with a chosen strategy and will assemble the objects for you, while others expect you to issue the second statement and stitch the results by key yourself. The cardinality reasoning does not change.

saying these in an interview costs you the question

  • Joins several collections in one statement and expects clean rows
  • Says a join fetch is always fewer statements so always faster
  • Applies a limit over a joined collection and expects that many parents
  • Ignores parent width when repeating parent columns per child row
  • Thinks the identifier list for a batch can grow without bound