---
title: "From 3.5 million round-trips to hundreds: batching a remap with temp tables"
canonical: https://dxdev.com/blog/batch-remap-temp-tables-3-5-million-round-trips/
datePublished: 2026-01-27
---
# From 3.5 million round-trips to hundreds: batching a remap with temp tables

A customer's entire sports site got deleted, and the fix I built was a 1,477-line Classic-ASP recovery engine that resurrects an account row by row from a dated backup database. The part that nearly killed it on the real data wasn't correctness. It was that the naive ID-remap fired one `UPDATE` per row, per table, per tenant, and the customer wasn't one site. It was a league with 327 teams under it. Multiply that out and you're looking at roughly 3.5 million individual round-trips to SQL Server for a single recovery.

This is the story of getting that down to a few hundred without changing what the remap does, only how it talks to the database.

## Why a remap exists at all

The platform stores each org's site as rows keyed by `username` across hundreds of per-sport tables: `<sport>games`, `<sport>scores`, `<sport>layoutstruct`, and so on. Recovering a site means re-inserting all of those from a backup DB into live prod. The hard part is foreign keys. A `scores` row points at a `games` row by `gameID`, but the games table has `IDENTITY` primary keys, so you don't know the *new* gameIDs until after the inserts land.

I built the recovery engine around a deliberate two-pass ordering, stated in the file's own header comment: copy first, then remap IDs. Rows go in with their foreign keys offset into a safe numeric range (`OFFSET_ID = 100000000`, parked where it can't collide with real IDs), and then a second pass rewrites every offset value to the real new ID. The remap walks maps for game, gameArch, location, player, and album, and updates the children: `games.lgoppgid`, `scores.gameID`, `registrationstruct.gameID`, every per-sport stat table, plus the `Arch` variants.

There's one rule the remap can never break, and the file calls it out by name. The biggest bug in several legacy league scripts was that remap UPDATEs did not filter by username, which corrupts multi-team leagues where different teams share the same old ID values. So every single remap UPDATE is scoped: `WHERE username = ? AND field = oldVal`. Tenant-scoping is load-bearing. Drop it and you cross-corrupt teams that happened to reuse an integer.

## The naive path, and why it explodes

The first working version I wrote did the obvious thing. For each `(oldID -> newID)` pair, for each child table, for each Arch variant, for each tenant, call `UpdateNumField`, which builds a single-row `UPDATE ... SET field = newVal WHERE username = ? AND field = oldVal` through the app's typed command layer. Readable, correct, easy to reason about one row at a time.

It's also a nested loop with four levels of fan-out. Hundreds of teams, many game keys each, several child tables, doubled by the archive variants. The troubleshooting doc estimates the result at about 3.5 million individual UPDATE statements, each one a separate round-trip across the ADO connection. Even if every statement runs in a millisecond, the network and per-statement overhead alone turns a recovery into something that can't finish inside the hard 30-minute `Server.ScriptTimeout` the engine runs under.

Loop-and-UPDATE is fine until N is large. At 327 teams, N got large.

## The batched path

The rewrite I landed on is a function called `BatchLeagueWideGameRemap`, and the shape is the standard set-based answer: stop chatting with the database and hand it the whole problem once.

It opens a single ADO connection and creates a temp table:

```sql
CREATE TABLE #gameMap (
  username NVARCHAR(255),
  oldID    BIGINT,
  newID    BIGINT
)
```

Then it bulk-inserts the entire mapping into `#gameMap` using chunked multi-row `VALUES` batches:

```sql
INSERT #gameMap (username, oldID, newID)
VALUES (...), (...), (...), ...
```

Chunking matters here. The code slices the mapping rows into batches of 500 (`chunkSize = 500`) and fires one `INSERT ... VALUES` per chunk, which keeps each statement well under SQL Server's per-statement multi-row `VALUES` limit and turns the whole load into a handful of round-trips instead of one per pair.

With the whole mapping resident in a temp table, each child table gets exactly one set-based join-UPDATE:

```sql
UPDATE t
SET t.gameID = m.newID
FROM dbo.child_table t
INNER JOIN #gameMap m
  ON t.username = m.username
 AND t.gameID  = m.oldID
```

One statement per table, not one per row. The same join key as the per-row version (`username, oldID`) carries the tenant-scoping straight through, so the correctness property that kept leagues from cross-corrupting is preserved by construction. There's a sibling temp table `#gameArchMap` for the archive variants, populated and joined the same way.

The whole loop collapses from millions of statements to: two CREATE TABLEs, a set of chunked inserts to load the maps, and one UPDATE per child table. Round-trips drop from ~3.5 million to roughly hundreds.

## It's conditional, on purpose

The batched path isn't the only path, and that's deliberate. It only kicks in when `EDIT_DATABASE` is true and there are two or more accounts to remap. Single-account recoveries and plan-mode (read-only, zero writes) runs stay on the original, readable per-account loop. There's no reason to spin up temp tables and bulk inserts to remap one team, and the per-row version is easier to trace when you're validating a single account against a disposable test DB.

The engine tells you which path it took with a log line:

```
Using batched remap (temp tables + bulk UPDATEs) to minimize round-trips.
```

and emits progress as it goes (`Remap progress: n/N accounts...`), so a long league run isn't a black box. When you have two code paths that must produce identical results, log which one ran. It's the cheapest way to debug a "why was this one slow / why did this one behave differently" question later.

## The half of "fast" that isn't in the query

A set-based join-UPDATE is only fast if the join can find its rows fast. The predicate is `t.username = m.username AND t.gameID = m.oldID`, which means SQL Server has to locate, for every mapping row, the matching child rows by `(username, gameID)`. With no supporting index that's a scan of the child table per join, and you've traded 3.5 million tiny statements for a handful of enormous table scans. Not obviously a win.

So I documented covering indexes for exactly the columns the remap joins on:

- `(username, lgoppgid)` on `games` and `gamesArch`
- `(username, gameID)` on `scores`, `scoresArch`, `registrationstruct`, and the stat tables

and it ships the verification queries to confirm they exist rather than assuming, via `sp_helpindex` and a `sys.indexes` lookup. The lesson is that the rewrite is two changes that only work together. The temp-table join removes the round-trips, and the `(tenant, fk)` index makes each join cheap. Do one without the other and the speedup either doesn't materialize or moves the cost somewhere you didn't measure.

## The takeaway

Loop-and-UPDATE is the right default. It's readable, it's obviously correct, and at small N the round-trip overhead is invisible. Keep it until N is genuinely large, and then don't try to make the loop faster. Change the shape.

Marshal the entire mapping into a temp table in a few bulk inserts, then do one set-based join-UPDATE per table, joining on the same key your per-row version used so you keep whatever scoping kept the data correct. Gate it behind a threshold so trivial cases stay on the readable path, and log which path ran. And before you call it fast, check the index. The join-UPDATE is only quick if `(tenant, fk)` is indexed. The query is half the work, the index is the other half, and they only count together.

## Related

- [The OFFSET_ID trick: copying rows whose foreign keys you don't know yet](offset-id-trick-remap-foreign-keys-restore): the two-pass copy strategy that this batch remap completes
- [I wrote a 1,477-line site-recovery engine in Classic ASP: copy first, remap IDs second](site-recovery-engine-copy-first-remap-second): the full recovery engine this optimization lives inside