What must be true of accumulated writes before a data-access layer can send them as one batch?
answer
- one shape, many parameter sets
- grouping needs reordering freedom
- parents still before children
- nothing may force an answer
- keys known before the insert
basics
~20 sThree things: the statements share one text and differ only in parameters; the layer may reorder them, grouping by shape while keeping parents before children; and nothing between them forces an early trip to the database.
solid answer
~50 sA batch is one statement text executed with many parameter sets, so the first condition is **uniformity**: an insert into one table and an insert into another, or an insert followed by an update, cannot share a batch. The second is **reordering freedom** -- writes interleaved across tables batch only if the layer may group them by shape, and it may do that only while parent rows still land before the child rows whose foreign keys point at them. The third is that **nothing between the statements forces an exchange**: a value only the statement can return, a read the layer must make consistent with pending changes, or a per-row check needing one statement's own affected-row count each split the run back into single trips. Miss any of the three and batching quietly degrades to a statement per row.
go deeper
Remember that a batch is one statement text with many parameter sets, so mixed statement shapes cannot travel together.
Be able to state all three conditions and give one concrete example of each, especially why an immediately needed generated key prevents accumulation.
Diagnose from the emitted statement sequence: alternating shapes, an insert followed by a read-back, or a query inside the loop each point at a different broken condition.
Treat batchability as a design constraint on the write path, and decide early whether key allocation and per-row concurrency checks are worth the throughput they cost.
## Condition one: one shape, many parameter sets A batch is not "several statements sent together"; it is **one statement text executed repeatedly with different parameters**. That is why uniformity is the first gate. Two inserts into the same table, differing only in their values, batch. An insert into one table and an insert into another do not -- though each group may form its own batch. An update whose set clause touches a different column list produces a different statement text and therefore a different batch, which is why layers that emit an update naming only the changed columns can batch worse than layers that always update the full column list. ## Condition two: freedom to reorder Real code rarely produces its writes already grouped. A loop that saves an order together with its lines produces `insert order, insert lines, insert order, insert lines, ...`. Sent in that order nothing accumulates: every statement differs in shape from the one before it. To batch, the layer must be free to **group by shape** -- all the orders, then all the lines. That freedom is not unconditional. Grouping is only safe while it preserves the orderings that actually matter: - **Referential order.** A child row carrying a foreign key must be written after the parent it points at, so the parent's batch must precede the child's. - **Delete-before-insert on a constrained key.** If a row is removed and another with the same unique key is added, the removal must reach the engine first. - **Observable order.** Anything that reads the rows in between -- another statement in the same transaction, a trigger-driven side effect -- can see the reordered sequence. What statements the layer decides to emit at all, and the order it settles on when it flushes, is a separate subject; here the point is narrower: batching consumes whatever slack that ordering leaves. ## Condition three: no forced round trip in between Even with one shape and full reordering freedom, a batch only survives if nothing between the statements requires an answer from the database. Three things commonly do. | What forces the trip | Why it breaks accumulation | Usual way out | |---|---|---| | A key value only the insert can produce | The layer needs the value now, so the insert must execute now and be read back | Assign keys on the client, or draw them from a pre-allocated block | | A read that must reflect pending changes | The layer flushes what it is holding so the query sees a consistent picture | Move reads out of the write loop, or complete the lookups before it | | A per-row check needing that statement's affected-row count | A concurrency check compares an expected count against one row's outcome | Batch where per-row verification is not required, or verify differently | The key case is the one that surprises people most often. When the row's identifier is produced by the storage engine as the row is inserted, and the layer needs it immediately -- to key the row in its identity map, or to fill in the foreign key of a child it is about to write -- the insert cannot sit in an accumulator. When identifiers are known before the insert, the same code batches without any other change. (Exactly when a key value becomes known is its own topic; what matters here is that the timing decides whether accumulation is possible.) The third row deserves a caveat rather than a flat rule: **layers differ**. Some can read per-statement outcomes back from a batch and still verify each row's concurrency check; others cannot distinguish the outcomes and will disable batching for rows that carry such a check. Do not assert a universal behaviour -- state the dependency and say you would confirm it for the layer in front of you. ## Putting it together 1. Group the work so that same-shaped statements are adjacent, or let the layer group them. 2. Remove anything from the loop that needs an answer: pre-resolve lookups, pre-assign or pre-allocate keys. 3. Keep the dependency ordering explicit, so grouping cannot invert parent and child. 4. Send on a fixed rhythm, and clear the layer's accumulated state on the same rhythm. The failure mode to watch for is silence. None of these conditions produces an error when it is violated: the layer simply falls back to a statement per row, and the only symptom is a run that is as slow as it was before.
- Why can a layer that updates only the changed columns batch worse than one that updates them all?Because the statement text depends on the column list. Rows that changed different columns produce different update statements, so they land in different batches and each group may be too small to matter. Always naming every column yields one shape for the whole table, which batches, at the price of writing columns that did not change.
- If reordering is what makes batching possible, what stops a layer from reordering freely?Anything that makes the order observable: foreign keys that demand a parent before its child, a unique key freed by a delete before an insert can reuse it, and side effects that read the rows mid-transaction. A layer groups by statement shape only within those constraints, which is why a heavily interleaved write pattern batches poorly.
- How would you check which of the three conditions your code is violating?Look at the statements in issue order. Alternating shapes point at missing grouping; an insert followed immediately by a read of its identifier points at key timing; a query appearing mid-loop points at a forced flush. Then remove one cause at a time and re-measure, because fixing only two of the three leaves the run just as slow.
saying these in an interview costs you the question
- Thinks any mix of inserts and updates batches together
- Assumes reordering is free and dependency order can be ignored
- Believes an immediately needed generated key still allows accumulation
- Says a query in the middle of the write loop is harmless
- Expects a warning or error when batching silently degrades