A developer's script opens a cursor over one million rows in the orders table and, for each row, runs an UPDATE that sets status = 'ARCHIVED' when order_date is before 2020-01-01. The job takes hours. What should the DBA recommend?
Select an answer to reveal the explanation.
Short Explanation
Row-by-row processing is like walking a million letters to the post office one at a time. A single set-based UPDATE lets you hand the whole stack to the engine at once, and the engine is built for exactly that. Don't fiddle with the loop; the loop itself is the problem.
Full Explanation
Relational engines are designed for set-based logic: a single statement describes which rows to change, and the optimizer chooses an efficient plan, scanning or using an index once and applying the change to every qualifying row in bulk. A cursor loop instead issues one million separate UPDATE statements, each with its own parsing or execution overhead, lock acquisition, log record and round trip, which is why the job runs for hours. Rewriting it as UPDATE orders SET status = 'ARCHIVED' WHERE order_date < DATE '2020-01-01' expresses the same intent in one operation. Committing after every row adds a log flush per row, making the loop slower and leaving the table partly updated if it fails midway. Moving the loop to a client script keeps the row-by-row design and adds a network round trip per row, so it shifts work in the wrong direction rather than removing it. Triggers fire on data modification events, not when a cursor reads a row, so a trigger cannot replace the update and would only add overhead to future changes. Exam caveat: very large set-based changes may be split into batches by key range to limit lock duration and log growth, but each batch is still a set-based statement. Operational check: run the set-based UPDATE in a test copy, compare elapsed time with the loop, and confirm the affected row count matches a COUNT(*) with the same predicate.