In the previous article, we compared the results produced by the original cursor-based version of Integration.GetStockItemUpdates against the results produced by the set-based rewrite, using a bidirectional diff over the real WideWorldImporters data. The two result sets were identical, and the set-based rewrite completed in roughly a third of the time the cursor-based version required, about three and a half times faster.
That comparison establishes that the rewrite is correct. It doesn't establish how the rewrite performs at scale, because the real WideWorldImporters data contains only a few hundred change events, an artifact of how the sample database was originally built rather than years of accumulated business activity. Before addressing that question, there's a step in the RPI workflow that we haven't yet run: Task Reviewer.
Task Reviewer: Confirming the Implementation Against the Plan
The previous article covered three of the four RPI agents. Task Researcher gathered the evidence establishing that the cursor was genuine RBAR and that a set-based rewrite was possible; Task Planner turned that evidence into an ordered set of decisions, including two that were deliberately deferred; and Task Implementor carried out the rewrite according to the plan. The fourth agent, Task Reviewer, checks the output of the other three against each other rather than evaluating the code in isolation. We don't run Task Reviewer until we're sure the implemented changes produce the same output as the original stored procedure; now that we've verified the output, we're ready to run it.
Task Reviewer's role is narrower than a general code review. Three questions get posed to it specifically: whether the final procedure matches every decision recorded in the plan, not just the straightforward ones; whether the two deliberately deferred items, the archive index and the LeadTimeDays/Tax Rate mapping, were left out cleanly or leaked into the change in some partial way; and whether anything in the implementation is untraceable to a plan step. The prompt used is included below for reference:
Changes: .copilot-tracking/changes/getstockitemupdates-setbased-rewrite-changes.md
Review the implementation against the plan and the original research. I want to know specifically:
- Does the final proc actually match every decision the plan recorded, not just the ones that were easy?
- Were the two deliberately-deferred items (the archive index, the LeadTimeDays/Tax Rate bug) left out cleanly, or did they leak into the change in some partial way?
- Is there anything the Implementor did that isn't traceable back to a plan step?
Asking the question this way matters, because a rewrite can pass a diff and still have drifted from what the plan actually specified. A diff confirms the output matches; it doesn't confirm that the implementation followed the decisions recorded along the way.
We verify the first two questions. Every phase-two step traces back to a specific plan step: the cursor, the WHILE loop, and the per-row FOR SYSTEM_TIME AS OF are all removed, replaced by a single INSERT ... SELECT; the base columns are pulled directly from the union rather than re-fetched; and the two lookups still requiring a point-in-time answer are joined through the history-plus-current union using the range predicate specified in the plan. In other words, none of the removed logic returns in a different form.
The two deliberately deferred items also check out. The archive index isn't partially addressed through a workaround or a query hint. The LeadTimeDays into [Tax Rate] mapping is preserved exactly as PD-01 specified, and the union subquery doesn't even select the column that would be considered "correct," meaning there's no dangling reference that a future edit could accidentally wire up. Both deferrals stay out entirely, without leaking into the implementation.
The third question is the one that actually justifies running Task Reviewer. Here is what it actually reports on that point:
3. Anything not traceable to a plan step?
Yes — one untracked addition (High):
A new file Integration/Stored Procedures/GetStockItemUpdates_Original.sql was created, defining a second permanent procedure Integration.GetStockItemUpdates_Original (the verbatim cursor version). This is not referenced by any plan step, any detail step, or the planning log, and the changes log explicitly records "Added: (none)" — so it directly contradicts the changes log.
Why it matters:
- It was almost certainly created as a parity baseline for Phase 3 Step 3.1 ("diff old vs new proc output"), which is a reasonable intent — but it was never recorded, and Phase 3 is still [ ] incomplete, so nothing formally owns cleaning it up.
- As written it's a CREATE PROCEDURE in the SSDT project, so a normal publish deploys a duplicate cursor proc to production — the exact RBAR object this task set out to eliminate. That's the opposite of the change's stated intent.
Everything else in the diff maps 1:1 to Steps 2.1–2.4.
Of course we know why the file is there: it's the original cursor-based version of the Integration.GetStockItemUpdates stored procedure, saved with the suffix _Original. We had to create that to be able to run it and compare its output against the rewrite. But we should have recorded it; as far as the paper trail is concerned, the file has no documented origin.
This is a meaningful finding rather than a minor one. As it currently sits in the project, it's a live CREATE PROCEDURE, which means a standard publish would deploy a second, permanent cursor-based procedure alongside the rewrite, the exact object this task was intended to eliminate. Task Reviewer classifies this correctly as Needs Rework, with one high-severity finding and everything else traced and confirmed.
Reaching Production Volume
The real WideWorldImporters data only offers 671 change events, enough to prove correctness but not enough to say anything about performance at scale. To get an honest answer, we needed the kind of volume a genuine historical backfill would actually produce: years of accumulated changes, thousands of rows in the archive table rather than hundreds. We built a synthetic backfill against Warehouse.StockItems to reach that volume, roughly 10,600 synthetic change events alongside the 671 genuine ones, close enough to real production scale for the question at hand. The technique itself, working around a system-versioned table's own enforcement to load history at that scale, is enough of a topic on its own that it may be worth covering in a future article.
Benchmark Results
We run both procedures, the original cursor-based version and the set-based rewrite, against both datasets: the untouched real data and the synthetic backfill.
Against the real, untouched data (671 change events), the cursor-based version completes in roughly 630 milliseconds, and the set-based rewrite completes in roughly 176 milliseconds, about three and a half times faster, confirming the result from the previous article against the complete change set rather than a subset of it.
Against the synthetic backfill (roughly 10,600 change events), the cursor-based version takes between roughly 145 and 163 seconds, and the set-based rewrite takes between roughly 1.7 and 2.6 seconds. Ranges are given here rather than a single figure because wall-clock timing varies between runs, and a single precise number would overstate the precision actually available. Even comparing the most cursor-favorable end of that range against the least favorable end of the rewrite's, the difference is between sixty and ninety times faster.
That gap isn't simply a larger version of "faster." At 671 rows, the rewrite outperforms the cursor by a factor of roughly 3.5. At roughly fifteen times the row count, it outperforms it by a factor around twenty times larger than that. The advantage isn't constant across row counts, it compounds, and the reason is mechanical rather than incidental.
The cursor-based version incurs one round trip per changed row; this isn't an approximation, it's the direct consequence of a CURSOR FAST_FORWARD loop executing a separate query per fetch. Its cost therefore scales linearly with row count: twice the rows produces twice the round trips, twice the compiles, and twice the execution cycles.
The set-based rewrite, by comparison, incurs the cost of one compiled execution plan and one pass over the data, and that cost changes very little as row count grows. In practical terms, one approach's cost grows in direct proportion to the workload, while the other's remains close to flat regardless of the workload; as row count increases, the gap between a linearly increasing cost and a nearly flat one increases correspondingly. The same principle explains why a person moving pallets one at a time down a warehouse aisle falls further behind a forklift the longer the aisle becomes; the forklift isn't getting faster, but the person on foot continues paying a cost per trip that the forklift doesn't.
There's no crossover point, no row count at which the cursor-based version performs better than the rewrite. The rewrite outperforms the cursor at 671 rows and by a wider margin at 10,600 rows.
Reassessing the Archive Index
Going into this benchmark, the expectation is that the archive table's missing index, the clustered index leads on (ValidTo, ValidFrom) rather than ValidFrom, will become a meaningful factor once volume increases. The planning log deliberately leaves this out of scope as follow-on work, on the assumption that it will eventually matter considerably. This assumption doesn't hold.
The rewrite alone, with no supporting index added, produces the entire sixty-to-ninety-times gap on its own. This is verified directly: the set-based query still performs a scan against the archive table's ValidFrom column, exactly the scan the missing index would otherwise avoid, and it remains roughly sixty to ninety times faster than the cursor-based version regardless.
This is because the scan performed by the set-based query occurs exactly once, independent of how many rows are ultimately returned. The cursor-based version's per-row cost, which is what actually drives its runtime into the hundreds of seconds, isn't the archive scan; it's the compile-and-execute overhead paid ten thousand separate times. A single scan, however unoptimized, will always outperform ten thousand separate round trips, because the cost of those round trips isn't primarily about index efficiency, it's about repeating the same work ten thousand times instead of once.
This reframes the role of the index. It isn't the factor that makes this fast; the rewrite itself is. The index remains a legitimate follow-on improvement and would likely reduce the scan cost further, but treating it as necessary, as this series implicitly did in the previous article, attributes credit that actually belongs to removing the cursor. That assumption was incorrect, and the benchmark corrects it.
Summary
Across the four RPI agents: Task Researcher establishes the actual reason the cursor exists rather than assuming it's an oversight; Task Planner converts that evidence into an ordered set of decisions, including two that are deliberately deferred rather than resolved silently; Task Implementor carries out exactly the changes the plan specifies; a diff confirms the output matches the original procedure exactly; Task Reviewer identifies a genuine gap rather than confirming the other three without scrutiny; and a benchmark at production-representative volume confirms the rewrite isn't only correct but substantially faster, with a margin that increases rather than decreases as data volume grows.
The remaining work is now more extensive than the planning log originally specified, which reflects the value of the review step rather than a shortcoming. The stray _Original procedure needs to be removed from the deployment path, either excluded from the build or removed outright, and the changes log needs to be updated to record that decision. Beyond that, the pre-existing follow-on items remain: correcting the LeadTimeDays/Tax Rate mapping as its own reviewable change, deciding whether the archive index is worth adding now that it's confirmed to be optional rather than required, and applying the same rewrite pattern to the six remaining Get*Updates procedures that share the same cursor-based convention.
No comments yet