skip to content

An overnight job reads rows one at a time from the application, decides something per row, and writes each one back, and it is far too slow. How do round trips and transaction scope explain this, and what are your options short of moving all the logic into the database?

level: middleimportance: should knowfreq 42%

answer

  1. per-statement round trip - latency-bound, not CPU-bound
  2. 1M rows x 0.5 ms = minutes of pure waiting
  3. one long transaction = locks held, MVCC cleanup blocked
  4. set-based first, batch second, procedure last
  5. chunked commits + resumable job

basics

~20 s

Each statement pays a network round trip plus parse and plan cost, so per-row work is latency-bound: a million rows at half a millisecond each is minutes of pure waiting. Fix it by expressing the work as set-based SQL, or batching, before considering a server-side procedure.

solid answer

~50 s

The job is latency-bound, not throughput-bound. Every statement costs a round trip; at 0.5 ms and one million rows that is roughly eight minutes of network waiting alone, before any database work. Holding one transaction open around the whole loop makes it worse: locks and old row versions are retained for the entire run, blocking cleanup and other writers. Order of remedies: 1. **Make it set-based.** A single UPDATE-from-select or INSERT-select expressing the same rule removes the loop entirely. This is the biggest win and stays in ordinary SQL. 2. **Batch what cannot be set-based.** Multi-row inserts, array parameters, chunked commits of a few thousand rows - amortises the round trip and bounds transaction size. 3. **Reduce data movement.** Filter and aggregate server-side instead of shipping rows to decide in the client. 4. **Only then consider a procedure**, when the per-row decision genuinely cannot be expressed in SQL and the data volume makes shipping rows prohibitive - accepting the testing, versioning, and database-CPU costs.

code

sql · 9 lines
sql
-- per-row loop from the application (one round trip per row):
--   SELECT id, balance_c FROM accounts WHERE id = ?;
--   UPDATE accounts SET status = ? WHERE id = ?;

-- one statement, one round trip, one plan:
UPDATE accounts a
SET    status = CASE WHEN a.balance_c < 0 THEN 'OVERDRAWN' ELSE 'OK' END
WHERE  a.status IS DISTINCT FROM
       CASE WHEN a.balance_c < 0 THEN 'OVERDRAWN' ELSE 'OK' END;

go deeper

for a junior

Say that each statement costs a network round trip, so per-row loops are dominated by waiting, and that one set-based statement replaces them.

for a middle

Do the arithmetic, explain batching and chunked commits, and note what one long transaction costs in locks and cleanup.

for a senior

Diagnose first - show how you would confirm it is latency-bound - then sequence the remedies and state exactly what a procedure does and does not fix.

for a principal

Frame it as where computation and data should meet given network topology, transaction-scope limits, and which tier has headroom, including the operational cost of resumable bulk jobs.

## Where the time actually goes A client-side row-at-a-time loop pays, per row: serialize the statement, network round trip to the server, parse and plan (unless prepared), execute, return the result, and often a second round trip for the write. The database work per row may be tens of microseconds; the round trip on the same network is commonly 0.2-1 ms, and across availability zones several times that. The work is therefore dominated by latency that no index and no faster disk will improve. Multiply by row count and the arithmetic is brutal: one million rows times two statements times 0.5 ms is roughly fifteen minutes of pure waiting. This is the real content of 'fewer round trips' as an argument for server-side logic - it is not that the database is a faster computer, it is that the network between the two tiers is charged per statement. ## Transaction scope: the second problem If the loop runs inside one transaction, the run also holds locks for its full duration and prevents cleanup of the old row versions it superseded. In an MVCC engine, a long-running write transaction keeps dead row versions unremovable, so table and index bloat grow and other queries slow down. Worse, if the client does anything slow between statements - an external call, a log flush, a garbage-collection pause - the transaction sits idle while holding everything. If instead the loop commits per row, you trade that for a different problem: the job is no longer atomic, a crash leaves it half-applied, and per-commit durability costs (flushing the write-ahead log) now dominate. The practical target is neither extreme: chunked transactions of a few thousand rows, each committed, with the job written to be resumable. ## Remedies in order **1. Express it as a set operation.** The overwhelmingly common finding is that the per-row 'decision' is a CASE expression or a join in disguise. One statement does what the loop did in a million: one round trip, one plan, and the engine is free to use hash joins and parallelism. This is not 'business logic in the database' in the contentious sense - it is using SQL as a set language, which is the job of the application's data access layer. **2. Batch.** When the decision truly cannot be expressed in SQL (it calls an external service, it uses a model, it needs a library the database does not have), keep the decision in the application but stop paying per row: read in pages of a few thousand, decide in memory, and write back with a multi-row statement or a bulk-load path. Round-trip cost is then amortised over the batch. Use a stable ordering and a resume marker so restarts are cheap. **3. Reduce what crosses the wire.** Push filtering, joining, and aggregation into the query so the client receives the rows it needs rather than the rows it will discard. Fetching a million rows to act on five thousand is a data-movement bug independent of round-trip count. **4. Consider server-side logic.** After the above, if you still need per-row control flow over enormous volumes and the decision is a pure function of data already in the database, a procedure is a legitimate answer: the loop runs where the data is, with no network per iteration, in one transaction the client cannot leave idle. Note honestly what you take on - a real database needed for tests, replace-in-place deployment, and CPU spent on your least scalable tier. Also note that a procedure does not remove the transaction-scope problem: a procedure running for an hour holds its locks for an hour just the same, so it too should commit in chunks where the engine allows. ## What the interviewer is listening for That you diagnose before prescribing - identify latency-bound behaviour rather than assuming a slow database - and that 'move it into a stored procedure' is your last option, not your first. Candidates who jump straight to procedures usually cannot say what the actual bottleneck was, and candidates who refuse procedures on principle cannot say what they would do when set-based rewriting genuinely does not apply.

  • Would wrapping the whole loop in a single transaction make it faster?
    It removes the per-row commit and its log flush, which helps if you were committing every row, but it does not remove the round trips that dominate. It also holds every lock for the whole run and prevents cleanup of superseded row versions, so bloat and blocking grow. Chunked transactions of a few thousand rows capture most of the commit savings without the long-transaction damage.
  • If you move the loop into a stored procedure, which of the two problems does that actually solve?
    It solves the round-trip problem - the loop no longer crosses the network per iteration - and it removes the risk of a client stalling mid-transaction. It does not solve transaction scope: a procedure that runs for an hour holds its locks for an hour and blocks cleanup exactly like a client-held transaction. It also moves the CPU onto the database, so a genuinely compute-heavy decision may simply relocate the bottleneck to the tier you can least afford to load.

saying these in an interview costs you the question

  • Diagnosing a latency-bound loop as 'the database is slow' and adding indexes or hardware
  • Jumping straight to a stored procedure without trying a set-based rewrite
  • Assuming a single giant transaction is the safe default, ignoring lock duration and MVCC cleanup
  • Believing a server-side loop is atomic in a way that also makes long lock hold times harmless
  • Fetching all rows to the client to filter in application code

context

resolving links…