When do you replace batched per-row writes with a single set-based statement, and what does that cost the team?
answer
- uniform change, predicate-selected rows
- rules live above the statement
- version and cascade become manual
- chunk it, or hold locks
- a second write path costs twice
basics
~20 sWhen the change is uniform and its rows can be named by a predicate rather than enumerated. One statement removes the trips and the per-row object work, but hooks, concurrency checks and cascades no longer run.
solid answer
~50 sBatching is right while each row genuinely needs its own statement -- different values, per-row rules, per-row concurrency checks. It stops being right when the change is the same for every row and the rows can be named by a predicate rather than enumerated: a set-based statement then removes the per-row object work as well as the trips, and lets the engine plan the whole change once. The price is everything the layer did around a per-row write. Callbacks and validation do not fire, a version column is maintained only if the statement maintains it, cascades do not fan out, and objects already loaded no longer match what is stored. There is an organisational price too: a second write path, with its own tests and its own chance for a rule to be enforced in one place and forgotten in the other.
go deeper
Know that one statement can change many rows at once, and that the application-side rules attached to saving an object do not run when it does.
Explain the tradeoff concretely: what a set-based write removes from the cost, and which of hooks, validation, cascade and version maintenance it removes with it.
Judge when the change is uniform enough to qualify, chunk it so locks and recovery stay bounded, and state which invariants the statement must reproduce itself.
Treat a second write path as a standing liability: decide where it is allowed, who owns keeping its rules in step, and whether the per-row path's slowness is really a mapping defect instead.
## The decision, framed honestly Both paths are legitimate. The per-row path -- batched -- keeps every rule the application layer enforces and pays object and statement overhead per row. The set-based path hands one statement to the engine and gives up those rules. The question is never "which is faster"; it is **which set of guarantees this change actually needs**. Five questions decide it: 1. **Is the change uniform?** "Set status to expired where the deadline has passed" is uniform. "Set each row's total to a value computed in application code" is not, unless that computation can itself be expressed against the data. 2. **Can the affected rows be named by a predicate?** If the set is already a list of identifiers computed in memory, much of the advantage is gone -- the identifiers still have to travel. 3. **Does per-row application logic have to run?** Callbacks, derived fields, validation, domain events. If any of it is load-bearing, either it moves into the statement or the set-based path is wrong. 4. **Does optimistic concurrency have to hold per row?** A version check exists to detect a concurrent modification of one row. One statement can bump a version column, but "one of the rows was modified underneath us" is a different question from "how many rows did I change", and only the per-row path answers it naturally. 5. **Who else is touching these rows now?** A single statement over a very large set holds locks for its duration and produces one large unit of work to undo; chunking it is usually mandatory, and that is a design decision, not a detail. ## What the set-based path buys | | Batched per-row writes | One set-based statement | |---|---|---| | Round trips | one per batch | one | | Statement executions | one per row | one | | Per-row application object work | full | none | | Rows enumerated by the application | yes | no, a predicate suffices | | Planning | once per statement text | once for the whole change | The row that people forget is the fourth. When the rows are chosen by a predicate, the application never has to read them at all -- the change is expressed once and the engine finds the rows. On a large set that is a bigger saving than the trips. ## What it costs - **Application-side rules do not run.** Callbacks, validation, derived columns and any domain event a save would have raised are simply absent. Anything essential must be reproduced inside the statement or accepted as lost. - **Concurrency control becomes manual.** If rows carry a version, the statement must maintain it, and the per-row detection of a concurrent change is gone. Sometimes that is fine -- a sweep that sets the same value is often idempotent -- and sometimes it silently discards a conflict. - **Cascades do not follow.** Children the per-row path would have reached need their own statements, in the right order. - **In-memory copies go stale.** Objects the current unit of work already holds were not updated by a statement it did not route through them. Planning for that -- and where the boundary between the two views sits -- is its own subject, but the decision belongs here, because choosing the set-based path is what creates the problem. - **A second write path exists.** This is the cost a principal weighs and an engineer overlooks. Every rule now has two homes. A new required field, an audit column, a state-machine guard: each must be added twice or deliberately scoped to one path. Teams that reach for a set-based write on every slow screen accumulate a shadow model of the domain expressed in statements. ## How to keep it safe 1. **Scope it narrowly.** One named operation -- an expiry sweep, a backfill, a tenant purge -- not a general-purpose escape hatch. 2. **Chunk by a bounded predicate** so no single statement holds locks or accumulates undo for an unbounded time, and so the job can be resumed. 3. **Own the invariants explicitly.** Write down which rules the statement reproduces and which it deliberately skips, and test the statement against those rules directly. 4. **Fix the boundary.** Decide whether the operation runs where objects are loaded at all; a background job with nothing tracked in memory is far easier to reason about than the same statement mid-request. 5. **Re-check the premise.** If the per-row path is slow because a collection is being rewritten wholesale or a cascade reaches untouched children, the set-based statement is treating a symptom that a mapping fix would have removed at lower risk.
- What makes a set-based write faster than a perfectly batched per-row write?Two things the batch cannot remove. The engine plans one change instead of executing a statement per row, and the application never enumerates or materialises the rows -- a predicate names them. Per-row engine work such as index maintenance is still paid, so the gap narrows when that work dominates.
- How do you keep optimistic concurrency meaningful if a sweep is set-based?Either fold the check into the statement's predicate so only rows still in the expected state are touched, and treat the affected-row count as the signal, or accept that the sweep is authoritative and document it. What you must not do is leave a version column unmaintained while other code still trusts it.
- Why chunk a large set-based statement rather than run it once?One statement over millions of rows holds locks for its whole duration, accumulates a large amount of undo, and cannot be resumed if it is interrupted. Bounded chunks keep the lock footprint and the recovery cost proportional and give the job restart points, at the price of losing all-or-nothing semantics across the whole sweep.
saying these in an interview costs you the question
- Reaches for a set-based statement before diagnosing the fan-out
- Assumes callbacks and validation still run for a set-based write
- Leaves a version column unmaintained while other code trusts it
- Runs one statement over millions of rows without chunking
- Treats the set-based path as a general escape hatch for slow screens
- Ignores that the same rule now has to live in two write paths