In a data-access layer, what does batching accumulated writes mean, and why is it faster than one statement per row?
answer
- one trip, not one per row
- same text, different parameters
- waiting dominates, not row work
- engine work is unchanged
- statement count does not move
basics
~10 sBatching sends many same-shaped statements in one round trip, one parameter set per row, instead of a request-and-wait per row. The database still does each row's work; what disappears is the per-row waiting.
solid answer
~40 sA save inside a loop usually becomes one statement per row, and each statement is a separate request the application must wait for before it can send the next. On a link with even a few milliseconds of latency that waiting dominates, and the database sits idle between rows. Batching means the layer holds back statements that share one text and differ only in their parameters, then ships the group in a single exchange and reads the outcomes together. The engine still inserts every row, checks every constraint and maintains every index, so a batch removes protocol and waiting cost, not per-row work. It is usually the cheapest write-side fix, because the same rows and the same statements simply travel in far fewer trips.
go deeper
Recall that a save in a loop means a statement and a wait per row, and that batching ships a group of same-shaped statements in one trip.
Explain that batched statements must share one text and differ only in parameters, and that the engine still performs every row's insert, constraint check and index maintenance.
Show the evidence you would gather: batching moves round trips and wall time, not the statement count, so prove it with latency scaling or the layer's batch counters.
Frame batching as throughput bought with memory, lock duration and coarser failure granularity, and decide the batch size and the restart story before an import relies on it.
## The shape that produces the problem The write-side equivalent of a read that fires a query per row is a **save inside a loop**. Code walks a collection, builds one object per element and asks the data-access layer to persist it. The layer records a pending insert or update for each one and, at some boundary, turns those pending changes into statements. If that boundary falls **inside** the loop -- a commit per iteration, or a read that forces the pending work out early -- every row becomes its own conversation with the database. That conversation is the expensive part, and it is easy to underestimate because each individual statement looks fast: - the client serialises the statement and its parameters and writes them to the connection; - the request crosses the network and waits in the server's queue; - the engine executes it -- for a simple single-row insert, often tens of microseconds of real work; - the result crosses back, and only then can the application issue row two. With a database on the same host, the wait per statement may be a fraction of a millisecond. Across a network it is commonly one to several milliseconds. Fifty thousand rows at one millisecond of waiting each is roughly a minute of doing nothing, on top of whatever the engine actually spends. ## What a batch is A batch is **one statement text executed with many parameter sets in a single exchange**. The layer accumulates the pending statements in memory, and when it reaches a configured size -- or when the unit of work flushes, or the transaction commits -- it sends the whole group and receives the outcomes as a list. Nothing about the rows changes; only the number of times the application stops and waits. That distinction matters when you predict the payoff: | | Statement per row | Batched per-row writes | One set-based statement | |---|---|---|---| | Round trips | one per row | one per batch | one | | Statement executions in the engine | one per row | one per row | one | | Per-row constraint and index work | full | full | full | | Per-row application logic (hooks, version bumps) | runs | runs | does not run | | Failure granularity | the single row | the batch's outcome list | the whole statement | ## What batching does not buy This is where candidates overreach. A batch: - **does not reduce the work the engine does per row.** Every row is still inserted, every unique index still probed, every foreign key still validated. - **does not usually reduce the number of statement executions.** Some layers can rewrite a group of same-shaped inserts into a single multi-row statement, which does collapse executions; treat that as a capability that may or may not be present, not as what "batching" means. - **does not remove a fan-out cause.** If a save cascades into a statement per child, or a collection is deleted and reinserted wholesale, batching makes the wrong number of statements cheaper rather than making it right. - **does not shrink the lock or transaction footprint.** Larger batches inside one transaction hold locks longer, not shorter. Because the statements are unchanged, **a statement count looks identical before and after** you enable batching. The figures that move are round trips and wall-clock time. ## The costs to price in 1. **Memory.** Accumulated statements and their parameter sets are held until the batch is sent, and the layer's tracked objects usually accumulate alongside them. 2. **Failure granularity.** A batch reports per-statement outcomes, but the failing row must be located within the group, and the surrounding transaction usually takes the whole batch down with it. 3. **Latency to first durable progress.** Nothing is visible to another session until the batch is sent and the transaction commits, which matters for long imports that need restart points. ## A workable default Accumulate on the order of hundreds to a few thousand rows, send, and repeat -- clearing the layer's tracked objects on the same rhythm so memory stays flat. Then measure: if the run time barely changes, the bottleneck was never the round trips, and the next place to look is per-row engine work or the producer of the data.
- Does batching reduce the number of statements the database executes?Usually not. Each row still gets its own execution inside the engine, with its own constraint checks and index maintenance; the batch removes the request-and-wait exchange around them. Some layers can rewrite same-shaped inserts into one multi-row statement, which does cut executions, but that is an extra capability rather than what batching itself means.
- Why does batching help far more against a remote database than a local one?The saving is proportional to the per-statement wait. On the same host that wait can be a fraction of a millisecond, so a loop is merely wasteful. Across a network at a few milliseconds, the same loop spends nearly all its time idle, and collapsing a thousand waits into one is a step change rather than a tuning gain.
Posting five hundred letters one at a time and waiting for a receipt before writing the next, versus handing over the whole sack at once. Each letter is still sorted and delivered; you simply stop standing at the counter.
saying these in an interview costs you the question
- Thinks batching makes the database do less work per row
- Assumes a loop of saves is fine because each save looks fast
- Believes a batch is always rewritten into one multi-row statement
- Expects the statement count to drop once batching is enabled
- Cannot name what batching costs in memory or failure granularity