The previous article closed on a specific problem. Integration.GetStockItemUpdates finds every stock item change since the last ETL run with a set-based query and then discards that efficiency immediately afterward. It opens a cursor, and for every row that query returns, it runs a separate FOR SYSTEM_TIME AS OF lookup to fetch that row's point-in-time snapshot: ten changes since the last load produce ten round trips, and a historical backfill pulling years of change events incurs that same round trip once per row, however many years back the backfill goes. At that volume, the cost isn't merely slow, it's genuinely resource intensive, since every one of those round trips is its own query compile-and-execute cycle, repeatedly hitting the same tables rather than reading them once.
The fix, in principle, is straightforward to describe. Database people refer to this pattern as RBAR, row by agonizing row: performing one row's worth of work at a time when the engine is capable of performing all the rows at once. The correct fix is a set-based rewrite, a single query that finds every changed row and resolves every lookup it needs in one pass, rather than a loop that resolves one row, then the next, then the next.
The fix is easy to describe; implementing it correctly is a different matter. Converting RBAR into a genuine set-based operation usually means re-deriving, as a single query, logic that was originally written as a sequence of steps precisely because a sequence of steps was easier for whoever wrote it to reason about. It requires care, because a query that looks equivalent on the surface can behave differently in ways that aren't obvious from reading it, particularly here, where this procedure feeds a Type-2 dimension loader in a warehouse. A subtle error here produces no thrown exception; it quietly corrupts history instead. This is how a known-flawed piece of code survives untouched for years: the perceived risk of changing it outweighs the cost of continuing to run it every night.
Rather than approaching this the usual way, reviewing the code manually and hoping every dependency turns up, we're going to use the Hyper-Velocity Engineering agents to work through this instead, checking each agent's output before the next one begins.
Understanding Hyper-Velocity Engineering
Hyper-Velocity Engineering, the microsoft/hve-core repo, is a set of instructions, prompts, and agent definitions layered on top of GitHub Copilot. The piece we’ll be using is the RPI workflow: Research, Plan, Implement, and Review, corresponding to four separate agents, Task Researcher, Task Planner, Task Implementor, and Task Reviewer, each of which feeds its output directly into the next.
For readers wanting the general background on RPI and how to get it running natively in Visual Studio, a previous article, How to Actually Adopt Hypervelocity Engineering (Without Buying the Whole Pitch) , covers the concept and the installation mechanics, specifically what RPI is and how the agents load natively in Visual Studio through .agent.md files. That article doesn't walk through an applied example, so we're going to do that now, to show how to use these agents to perform a complex change to existing database code in a SQL Server Database Project in Visual Studio.
The advantage over simply asking Copilot to make the code set-based isn't raw intelligence; these agents aren't necessarily more capable than a careful engineer working alone. The advantage is structure, specifically structure that forces the parts of this work that are easy to skip. Task Researcher isn't permitted to propose a fix, only to gather evidence. Task Planner isn't permitted to write final code, only to decide what happens and in what order. By the time anything touches the real procedure, a written trail exists documenting what is known, what was decided, and what was deliberately left out, and that trail is what makes a change of this kind reviewable.
We will be invoking each agent from the GitHub Copilot Chat tab in Visual Studio, working against the WideWorldImporters solution. The Agent button on that tab opens a dropdown list of available agents, and that list includes the four Hyper-Velocity Engineering (HVE) agents used throughout this exercise.
A Note on the Environment
Before the first agent begins its work, it surfaces something worth documenting here, since anyone following along in Visual Studio rather than VS Code will encounter the same limitation.
Task Researcher's instructions expect it to delegate the actual investigation to a subagent through a tool called runSubagent. That tool doesn't exist in Visual Studio; it's a VS Code Copilot Chat capability, and GitHub's own feature matrix confirms the gap directly, since the agent skills and prompt files supported by the VS Code extension aren't yet available in Visual Studio as of this writing. Task Researcher identifies the missing tool, reports it, and asks whether to stop or proceed with the investigation directly using what is available.
This is a reasonable response to a missing tool, and it's worth noting mainly because nothing unusual happens as a result: no error gets thrown, and no silent workaround gets attempted. The agent identifies the gap, reports it, and adapts accordingly. We instruct it to proceed directly, and everything that follows reflects that fallback.
Task Researcher: Confirming the Problem Before Attempting a Fix
We clear context using the /clear command, select Task Researcher, and give it the following prompt:
Integration.GetStockItemUpdates currently uses a CURSOR FAST_FORWARD READ_ONLY to loop over change events and, for each row, runs a separate SELECT ... FROM Warehouse.StockItems FOR SYSTEM_TIME AS OF @ValidFrom query to fetch that row's point-in-time snapshot. This means one round trip per changed row, which is fine for a normal incremental load but should get progressively worse on a bulk change set or a historical backfill.
Research:
- Confirm this is genuine RBAR: quantify what the cursor actually costs (round trips = row count) versus a set-based alternative
- Why FOR SYSTEM_TIME AS OF is called inside the loop at all instead of once, specifically whether it's a hard syntax constraint (FOR SYSTEM_TIME AS OF only accepts a scalar, not a correlated column) or just how the original author wrote it
- Whether the data the cursor re-fetches via FOR SYSTEM_TIME AS OF is already present in Warehouse.StockItems_Archive / Warehouse.StockItems at the point the enumeration query runs, i.e. whether the second query is doing new work or re-fetching a row already in hand
- How Integration.MigrateStagedStockItemData consumes the output of this proc (staging table shape/columns), so any rewrite doesn't break that contract
- Any indexing on Warehouse.StockItems_Archive.ValidFrom, since a set-based rewrite would filter on it directly instead of scanning per row
- Other procs in the Integration schema that follow the same cursor-per-temporal-row pattern, in case this is a repeated convention worth flagging
Do not propose or write the rewrite, that's Task Planner/Implementor's job. I just need the evidence and the "yes, this is fixable and here's why" case.
This explicitly disallows the agent from proposing a fix; the task is limited to gathering the evidence a fix would require.
The findings include more than a manual review would likely surface, including two things that would probably be missed entirely.
The confirmation itself is straightforward: the loop runs one lookup per changed row, and cost scales linearly with row count. The more useful finding is why. FOR SYSTEM_TIME AS OF accepts only a scalar value, meaning it can't be correlated to a column from another table; there's no way to express "as of this row's own timestamp" for an entire set of rows at once, only one timestamp at a time. The original author almost certainly attempted a join first, encountered that constraint, and fell back to a cursor as the only approach that would compile. This reframes the original author's choice considerably: the cursor wasn't the product of carelessness, it was the only path the language actually permitted.
The first of those findings concerns redundancy. The columns the loop re-fetches through AS OF are, for the base stock item, already present in the very rows the enumeration query reads to build the cursor in the first place, since the history table physically holds every column, not merely an identity and a timestamp. Most of that per-row lookup, in other words, is re-fetching data that is already available. The genuine remaining work is resolving two dimension values, package type and color, which live in separate temporal tables entirely and aren't carried on the archive row.
The second finding is one that a research pass is a considerably better place to catch than a production incident. The procedure currently feeds a single source column, lead time, into two different output columns, lead time and tax rate. This is almost certainly a bug. The research document itself records it as follows:
Observed quirk: the current procedure mapssi.LeadTimeDaysinto BOTH[Lead Time Days]and[Tax Rate]. A rewrite team should flag/decide this explicitly (likely a latent bug) but note that changing it alters the data the DW receives.
What is worth flagging is Task Researcher's handling of this finding, which is to take no action on it. It records the finding and defers the decision, because correcting it would change what the warehouse receives, and that decision falls outside the scope of the research phase.
Task Planner: Deciding Everything, Changing Nothing
Before starting Task Planner's phase, we clear context again with the /clear command. Clearing context between agents matters because each phase of the RPI workflow is meant to reason from the previous phase's actual written output, not from the full back-and-forth conversation that produced it. Without a fresh context, an agent's reasoning can quietly drift: it might repeat conclusions it already stated once rather than re-deriving them from the document in front of it, or it might carry forward assumptions from an earlier phase that were never actually written down anywhere a reviewer could check. Task Planner works from an actual file reference rather than a description, so we open the research document in an editor tab in Visual Studio, select Task Planner, and give it the following prompt:
Research: .copilot-tracking/research/getstockitemupdates-cursor-rbar-research.md
Plan the set-based rewrite of Integration.GetStockItemUpdates. Key things the research surfaced that the plan needs to account for:
- Base StockItems columns are redundant to re-fetch; PackageTypes (x2) and Colors lookups are NOT (only IDs live in the archive row), so those still need a point-in-time join, just collapsed to one set-based pass instead of one per row
- Archive table's clustered index leads on (ValidTo, ValidFrom), not ValidFrom, flag whether a supporting index is in scope for this plan or a follow-up
- The LeadTimeDays -> both [Lead Time Days] and [Tax Rate] mapping looks like a latent bug in the current proc, decide explicitly whether to preserve or fix it, don't silently change it
- Output contract (17 columns, NOT NULL guards via ISNULL, ORDER BY ValidFrom) must be preserved exactly since MigrateStagedStockItemData depends on it
Task Planner's role from that point is to convert the evidence into an ordered sequence of steps and to surface any decision the research leaves open rather than allow it to remain implicit.
On the lead-time-into-tax-rate bug, Task Planner makes the decision explicit instead of quietly resolving it: preserve the existing behavior exactly, so the rewrite can be proven identical to what currently runs, and log the bug itself as separate follow-on work. Separating the two matters because a performance rewrite and a correctness fix are different changes with different failure modes; combining them makes it impossible to cleanly prove that either one did not introduce a problem.
A second decision worth noting concerns the history table's date-range filter, which has no supporting index: no index leads with the column the enumeration query filters on. Task Planner deliberately leaves adding one out of scope, reasoning that an index change affects every procedure reading that table and carries its own separate risk. The rewrite stays a clean, behavior-preserving structural change, with indexing treated as a separate, independently measurable improvement.
The plan also documents, in plain terms, the output contract the rewrite has to preserve: the same columns, the same order, the same guards preventing certain fields from ever landing as null, feeding the same downstream loader. This is precisely the kind of detail that tends to get skipped in a manual rewrite and rediscovered later when the loader fails.
Task Implementor: Writing the Rewrite
Once Task Planner has completed its job, we clear the context again, using /clear, and open the plan document in an editor tab, select Task Implementor, and give it the following prompt:
Plan: .copilot-tracking/plans/getstockitemupdates-setbased-rewrite-plan.instructions.md
That is genuinely all the prompt requires. Task Implementor's role is to execute what the plan already specifies rather than make new judgment calls, so there's nothing further to provide beyond a reference to the plan itself. From this point, Task Implementor writes the set-based T-SQL, replacing the cursor in the stored procedure.
Once Task Implementor has finished, there's comparatively little to narrate, which reflects the plan being followed rather than reinterpreted. The cursor and its per-row lookup are gone, replaced by a single INSERT ... SELECT. The query enumerating changed rows now pulls every base column directly from the source, rather than only a row's identity and timestamp as the original cursor did. The two lookups that genuinely require a point-in-time answer, package type and color, are resolved set-based instead: each one's full history is joined against the change rows using a range condition, so each row matches against whichever version of the lookup was valid at that row's own timestamp.
What that range condition is actually doing is worth spelling out plainly, since the scalar-only rule obscures it. FOR SYSTEM_TIME AS OF is one way to ask what was true at a given instant, and it's a way that can't be correlated to a column. The operation underneath it, finding the row whose start and end bracket a given moment, is simply a join condition, and join conditions can reference a column from another table without difficulty. The cursor exists only to feed that scalar clause one value at a time; once the scalar clause is removed and the same version-matching is expressed as a join instead, the loop is no longer necessary.
The lead-time quirk stays exactly as it was, consistent with the plan, and the temp table, the close-off logic, and the final column list stay unchanged as well, since none of those pieces are ever part of the problem. What remains contains no cursor, no FETCH, no WHILE, and no FOR SYSTEM_TIME AS OF anywhere in the procedure.
We compile the procedure, and the project builds without complaint; however, a successful compile only confirms that the syntax is valid, it doesn't confirm that the procedure returns the same rows the previous version does.
Confirming the Rewrite Produces Identical Output
None of the preceding work matters unless the rewrite's output matches the original's exactly, row for row, since the Type-2 loader downstream trusts every row it receives without independently verifying any of it.
Establishing that cleanly requires running a diff in both directions: we keep the original procedure under a second name, run both versions over the same window, and compare the two result sets in both directions, what the original produces that the new version doesn't, and what the new version produces that the original doesn't. Both checks matter, not only one. When we check only one direction, we confirm that the new procedure hasn't dropped anything; but this doesn't confirm whether it has introduced rows that shouldn't be there, which requires the second check. Only when both checks return zero rows do the two result sets actually match.
The core of this check looks like this, with #Old and #New declared ahead of time as temp tables matching the column shape the procedure returns:
DECLARE @LastCutoff datetime2(7) = '2013-01-01';
DECLARE @NewCutoff datetime2(7) = SYSUTCDATETIME();
INSERT #Old EXEC Integration.GetStockItemUpdates_Original @LastCutoff, @NewCutoff;
INSERT #New EXEC Integration.GetStockItemUpdates @LastCutoff, @NewCutoff;
SELECT * FROM #Old EXCEPT SELECT * FROM #New; -- rows here mean the rewrite lost something
SELECT * FROM #New EXCEPT SELECT * FROM #Old; -- rows here mean the rewrite invented something
Both EXCEPT queries need to return zero rows; any row appearing in either result represents a genuine discrepancy requiring investigation.
We run this check against the real WideWorldImporters data, over a window wide enough to capture every change event the sample database contains. Both directions return empty results, with identical row counts on both sides and every row accounted for. The rewrite produces the same output as the cursor it replaces, down to the individual row.
The rewrite is therefore both correct and already faster. We run both procedures against the real, untouched WideWorldImporters data, using the same window the diff uses, and the rewrite completes roughly three and a half times faster, consistently. The sample database offers only a few hundred change events, so this isn't a dramatic figure in isolation, but it's a real, repeatable result with identical output on both sides.
That question is settled. A further question remains: the research already indicates that the cursor's cost scales with the number of changed rows, one round trip per row, while the set-based version performs its work in a single pass regardless of row count. If that holds, the gap measured here on a few hundred rows should be a small preview of a considerably larger gap once real volume is involved, the kind a genuine historical backfill would produce. Determining exactly how that margin grows, and what it takes to bring a sample database with no real history up to a volume capable of answering that question, is addressed separately.
The next article addresses that question directly.
No comments yet