Remedy Selection by Cardinality

## Why a list read turns into many statements Most mapping layers defer a linked object or collection. The parent row is materialised, the link holds a stand-in, and the real statement runs the first time code touches it. Read a page of `N` parents and then walk each parent's link — in a serialiser, a formatter, a total, a permission check — and the layer emits one statement for the page plus one per parent. The database is usually not slow; the round trips are, and their number grows with the page. A remedy is anything that makes the number of statements **bounded** — independent of `N` — while still producing the data the read needs. There are four families. Two of them change **how** the link is loaded, one changes **what** is loaded, and one changes **where the data comes from**. ## The four families | Remedy | Statements for a page of N | Fits | Main cost | |---|---|---|---| | **Join fetch** | 1 | a to-one link, or a single narrow collection | parent columns repeat on every child row | | **Bulk second query** (batch or subquery fetch) | 2, or a few | to-many links; several links at once | one extra round trip per link | | **Projection to a transfer model** | 1 | reads that display a handful of fields | results are not tracked objects; another shape to keep in step | | **Cache** | 0 on a hit | small, hot, reused, slow-changing data | staleness and invalidation | - A **join fetch** widens the parents' own statement so the linked rows arrive with them. One round trip, nothing left to touch later. - A **bulk second query** keeps the parent statement narrow and issues one more statement that answers the whole page at once: either by collecting the page's parent identifiers and asking for all their children together, or by re-using the parent query as a subquery inside the child query. - A **projection** sidesteps the graph entirely. If the read displays six fields, ask the database for six columns; the linked values come back as ordinary columns of a flat result, and there is no deferred link left to trip over. - A **cache** removes the statement rather than reshaping it. It only helps when the same linked data is read again and again and can tolerate being slightly out of date. The detailed mechanics of each fetch mode belong to the loading-strategy topics; what matters here is that they are alternatives with different shapes, not a ranking. ## What decides the choice 1. **Cardinality of the link.** A to-one link adds columns to a row. A to-many link adds rows, and a second to-many link in the same statement multiplies them. 2. **Page size.** A remedy that carries the page's identifiers into a second statement grows with the page and may need chunking; a remedy that re-derives the page inside a subquery does not, but pays for the parent query twice. 3. **Payload width.** A wide parent repeated once per child row moves far more bytes than the same data fetched separately, even though it costs one fewer round trip. 4. **Reuse.** Data read once per request argues for a fetch change; the same small reference data read on every request across many users argues for a cache. ## Remedies are chosen per read, not per link The same link can be right to join in one screen, batch in another, and ignore entirely in a third. That is why the remedy usually lives with the read that needs it — a fetch plan on that query, or a purpose-built projection — rather than being burned into the mapping as a global default. A global default has to be right for every read at once, and the reads disagree. ## Two things a remedy does not do - **It does not make an unnecessary read necessary.** If the screen never shows the children, the fix is to stop touching the link, not to fetch it more cleverly. - **It does not prove itself.** The only evidence that a remedy worked is the statement count for that code path before and after; a change that feels faster on a small fixture may simply not have enough rows to multiply. A useful default posture: keep links deferred, decide the loading per read, reach for a join when the link is to-one and narrow, reach for a bulk second statement when it is a collection, and reach for a projection when the read only ever needed a few columns.