Batch & Subquery Fetching

## The situation batching is answering A **deferred link** is an association the data-access layer chose not to fill when it loaded the owning row. In its place it puts a **stand-in**: an object that carries the key and knows how to fetch the rest the first time anything touches it. That is what keeps the first read cheap and the object graph finite. The cost is deferred, not removed. A loop over fifty loaded owners that touches one deferred link each will fill fifty stand-ins, and if each fill is its own statement, that is fifty round trips to satisfy one screen. Batch fetching is the middle path between that and pulling the whole graph in the first statement. It keeps the loading deferred, and it keeps the extra statements — it just makes there be very few of them. ## What the layer actually does 1. Something touches a stand-in, so the layer must now fill it. 2. Before emitting anything, it inspects the other stand-ins **of the same kind** that are registered in the current unit of work and still unfilled. 3. It collects up to a configured maximum number of their keys — the **batch size** — including the key of the one that was touched. 4. It emits a single statement whose predicate is a set-membership test over those keys: `select ... from owner where id in (?, ?, ?, ?)`. 5. It routes each returned row to the stand-in that owns that key. Every stand-in in the batch is now filled. The touch that triggered the load pays for the whole batch. The next several touches in the loop find their objects already filled and emit nothing at all; only when the loop reaches a stand-in that was outside the batch does another statement fire. Fifty pending links at a batch size of ten produce five statements instead of fifty. ## Two things get batched, and they emit different statements | What is being filled | Statement the batch emits | What comes back | |---|---|---| | Stand-ins for single-valued links (the key sits on the owner row) | `select ... from target where id in (...)` | At most one row per key; a missing key means that link is genuinely absent | | Deferred collections (the key sits on the child rows) | `select ... from child where owner_id in (...)` | Many rows per key, filed under their owner; an owner with no children ends up with an empty collection | The collection case is the one that matters most in practice, because it is the case where the per-owner alternative is a statement per owner per screen. ## What it requires, and what it does not change - It needs the **keys already in hand**. Batching can only include stand-ins the unit of work already knows about; it cannot guess at links that have not been loaded or registered yet. - It needs the objects to be filled **in the same unit of work**. Once the owners are detached from the unit of work that loaded them, there is no shared registry to batch against. - It is **opportunistic, not a plan**. Nothing about the first query changes; the driving statement keeps the shape you wrote, with its own ordering and its own row count. - It can **over-fetch**. The batch fills links nobody has asked for yet. That is usually a win, because they are about to be asked for, and a waste when they are not. - The rows come back in whatever order the engine likes; the layer matches by key rather than by position, so the returned order carries no meaning. ## The partly filled last batch The pending keys rarely divide evenly by the batch size. The final statement of a run therefore covers fewer keys than the maximum, and layers differ in how they handle that: some simply emit a shorter list, some round down to one of a few pre-agreed list lengths, and some pad the list by repeating a key so that a small number of statement shapes recur. Which one a layer chooses is a detail of that layer, not a property of batching. ## How to talk about it in an interview Say what it is (fewer statements, not zero), say what triggers it (a touch, plus the other pending stand-ins of the same kind), say what it emits (one set-membership predicate over keys), and say what it costs (some links filled that were never needed, and a statement whose parameter list varies with how many keys were pending). That is the whole mechanism, and it is the same mechanism whichever data-access layer you have used.