When a data-access layer loads deferred links in identifier batches, how is each batch assembled and what happens to the final, partly filled batch?
answer
- opportunistic, not planned ahead
- same kind, unfilled, same unit of work
- capped at the configured maximum
- the remainder never divides evenly
- short list means a new statement shape
basics
~20 sThe layer takes keys from unfilled stand-ins of the same kind in the current unit of work, up to a configured maximum, and emits one set-membership statement. Leftover keys form a shorter final list, sent as-is, rounded, or padded.
solid answer
~50 sAssembly is opportunistic and local to the unit of work. When one stand-in must be filled, the layer walks the registry of stand-ins of the same kind that are still unfilled, takes their keys up to the configured maximum, and issues `... where id in (?, ?, ...)` once. Rows are matched back by key, so the engine's row order is irrelevant, and a key that returns nothing means the row is absent rather than the batch failing. The remainder rarely divides evenly, so the last statement carries fewer keys — and that is where layers differ: some emit the shorter list directly, which produces a statement shape the engine has not seen before; some round down to one of a few standard list lengths; some pad the list by repeating a key so the shape recurs.
go deeper
Recall that the layer collects keys from unfilled links of the same kind and caps them at a configured number, and that the last statement of a run is a shorter one because the keys rarely divide evenly.
Explain the candidate set precisely — same kind, unfilled, current unit of work — and that rows are dispatched by key. Then explain why a short list is a new statement shape and how layers handle the tail.
Connect the tail policy to what the system emits under load: many one-off list lengths mean many one-off statement shapes. Say which policy you would want on a hot path and why.
Treat list-length variety as a property of the whole service, not a single query, and weigh a small recurring set of lengths against the extra statements that rounding down costs.
## Where the keys in a batch come from Batched loading is **opportunistic**, and the word matters. The layer is not planning ahead; it is reacting to a touch it must satisfy anyway, and taking the opportunity to satisfy some neighbours in the same trip. The candidate set is narrow and worth stating precisely: - the stand-ins must be of the **same kind**, because they must be answerable by one statement against one table; - they must be **still unfilled**, since a filled one has nothing left to fetch; - they must be **registered in the current unit of work**, because that registry is the only place the layer can look; - their **keys must already be known**, which they are, since a stand-in exists precisely because the layer holds the key and not the row. Anything outside that set — links in another unit of work, links whose owners have not been loaded, links of a different kind that happen to be needed on the same screen — cannot join the batch. ## Assembling one batch 1. A touch demands that one stand-in be filled. 2. The layer collects keys from the candidate set, starting with the touched one, up to the configured maximum. 3. It emits one statement with a set-membership predicate carrying exactly those keys as bind parameters. 4. It distributes the returned rows to the stand-ins by key. 5. Any key that came back with no row is resolved as an absent target rather than left pending — otherwise the same batch would be re-issued on the next touch. Two consequences fall straight out of step 4. First, the **order of returned rows carries no information**: matching is by key, and asking the engine to preserve the order of a set-membership list is neither required nor useful. Second, **the batch fills links nobody asked for**. On a loop that touches all of them, that is the entire point. On a path that touches one, it is fetched-and-discarded work. ## The partly filled last batch Pending keys do not divide evenly by the batch size, so the last statement of a run is short: twenty-three pending keys at a maximum of ten give ten, ten and three. That three-key statement is not just smaller, it is a **different statement shape** — a different number of bind parameters in the predicate. Over a lively system, a naive scheme can emit a distinct shape for every list length from one up to the maximum. Layers deal with the tail in one of a few ways, and it is fair to describe them generically rather than claim one behaviour is universal: | Tail policy | What the last statement looks like | Trade | |---|---|---| | Send the exact remainder | A list whose length is whatever was left | Simplest; the widest variety of statement shapes | | Round down to standard lengths | Several statements drawn from a small fixed set of lengths | Few recurring shapes; slightly more statements | | Pad the list | The maximum length, with a key repeated to fill it | One shape only; harmless duplicate keys in the predicate, and a slightly wider list than needed | Why any of this is worth caring about: an engine reuses work across statements that are textually the same shape with different parameter values, and a system that emits many one-off shapes gets less of that reuse. The engine-side accounting of shapes and plan reuse is a database topic in its own right; from the data-access side, the practical rule is that **a small, recurring set of list lengths is kinder than an arbitrary one**. ## What the tail does not do - It does not change correctness. A short list fetches exactly the keys in it. - It does not leave anything unfilled. The short statement is issued precisely so the remainder is not left dangling. - Padding by repeating a key does not return duplicate objects: a set-membership predicate matches each row once, and the layer keys results by identity anyway. ## Saying it well The strong answer is three sentences of mechanism and one of nuance: the layer gathers unfilled same-kind keys from the current unit of work, caps them at the batch size, emits one set-membership statement and dispatches rows by key; the leftover keys make a shorter final statement, and layers vary in whether they send that short list as-is, round it to a standard length, or pad it, because a small number of recurring statement shapes is easier on the engine than an arbitrary one.
- Why must a key that returns no row be resolved rather than left pending?Because the stand-in would stay unfilled and the next touch would assemble a batch containing it again, re-issuing the same fetch indefinitely. Resolving it as an absent target — an empty collection, or a missing single link the layer reports in its own way — records that the question has been asked and answered.
- Does the order of rows in the batch result matter?No. Rows are dispatched to stand-ins by key, so the engine may return them in any order without affecting the outcome. Ordering only matters inside a deferred collection whose mapping asks for a defined order, and that ordering is applied per owner after the rows are filed.
- Why does padding the key list not produce duplicate results?A set-membership predicate tests membership, so a repeated key matches the same rows once; and even if a row arrived twice, the layer resolves it through its identity registry to the object it already holds. Padding costs a slightly longer parameter list and nothing else.
saying these in an interview costs you the question
- Thinks batches are planned before the driving query runs
- Believes stand-ins of different kinds can share one batch
- Assumes rows must come back in the order the keys were listed
- Says the short final statement is skipped or merged into the previous one
- Thinks a repeated key in the list returns the row twice