Batch DML Patterns

## The shape of the anti-pattern The classic slow data fix looks like application code that reads a list of ids, then for each id sends a statement: ```sql UPDATE orders SET status = 'ARCHIVED' WHERE id = ?; ``` Run 200,000 times, this is 200,000 statements. Functionally it is correct; that is what makes it survive code review. Its cost is invisible in the SQL, because none of the expensive parts are in the statement text — they are in the *number of times* the statement is sent. ## What each iteration costs Four costs are paid per statement rather than per unit of useful work. 1. **A network round trip.** The client sends the statement and waits for the server's response before it can send the next one. This is latency, not throughput: it does not get better with a faster disk, and it is paid even when the statement changes nothing. At 1 ms round trip, 200,000 statements is 200 seconds of waiting; at 5 ms — a cross-availability-zone hop — it is over 16 minutes. 2. **Statement handling on the server.** Each statement is received, parsed, and turned into an executable plan (a prepared statement amortises the parse, but not the execution setup or the round trip). 3. **Transaction work.** If the loop commits per row — the default for many clients in autocommit mode — each row forces a durable log write. 4. **Client-side work.** Result handling, driver bookkeeping, and often an ORM's own per-row overhead. None of these scale with how much data changed. They scale with the statement count, which is why 200,000 tiny updates can take an hour while one statement touching the same 200,000 rows takes seconds. ## The set-based rewrite A set-based statement describes the whole set of rows to change and the change to make. Two shapes cover almost every loop: **The criterion is expressible as a predicate.** Move the loop's `if` into the `WHERE` clause: ```sql UPDATE orders SET status = 'ARCHIVED' WHERE placed_at < DATE '2024-01-01' AND status = 'CLOSED'; ``` **The values differ per row.** Put the per-row values in a table (a staging or temporary table, loaded once in bulk), then join or correlate to it in a single `UPDATE`. The loop's body becomes the `SET` list; the loop's iteration becomes the join. The same reasoning applies to `INSERT` (one `INSERT ... SELECT`, or one multi-row `VALUES`, instead of N inserts) and to `DELETE` (one predicate instead of N id-equality deletes). ## What the rewrite does not fix A set-based statement removes per-statement overhead. It does not remove per-row work: every changed row is still written, and every index whose columns changed is still maintained. Two consequences follow. First, if the single statement's `WHERE` clause cannot find its rows efficiently, the statement is still slow — just slow for a different reason. Check that the predicate can use an existing index and is written against the bare column rather than wrapped in a function. Second, one enormous statement is one enormous unit of work: it runs as a single statement in a single transaction, and it either completes or is rolled back whole. When the row count is very large this is a reason to split the work into **bounded chunks** — a sequence of set-based statements, each covering a key window — rather than to fall back to one statement per row. The unit to reduce is the statement count, and chunking keeps it small (hundreds of statements) instead of enormous (millions). ## When a loop is genuinely correct A loop is right when the per-row work cannot be expressed in SQL at all: calling an external service per row, applying encryption the database does not have keys for, or needing an individual success/failure outcome per row for a reconciliation report. Even then, read in batches and write in batches; the loop should be over *batches*, not over rows. ## What interviewers listen for The strong answer names round trips and per-statement overhead as the dominant cost, shows the rewrite, and then volunteers the two caveats: that the single statement still needs a sargable predicate, and that at extreme row counts you chunk rather than issue one gigantic statement. The weak answer says "loops are slow" without being able to say what, specifically, is being paid 200,000 times.