One bulk query can beat thousands of careful loops

A lot of data jobs look like this: take a list of records, do a lookup for each one, decide what changes and write the result. The code's easy to read and it runs fine against ten records, so it gets through development and review without anyone worrying. Run it over 200,000 records, though, and you've got several database calls per record, each one paying for connection setup, network latency, query planning and transaction overhead all over again.

It's tempting to treat that as slow application code and throw more workers or a bigger instance at it. More workers can add connection pressure and lock contention, and a bigger instance just hides the query pattern for a while. Before changing anything I'd count round trips on a small sample with query counting turned on. If 100 records produce 301 calls, you're probably looking at one initial read and then a lookup and a write for every item. I'd also check the query plan for that repeated lookup, because if the predicate can't use an index, batching it up just gives you one very large slow query.

The change is to send the records to the database as a set (a staging table, temp table, multi-row insert, array parameter, whatever your system supports) and let the database do the matching. A staged import might go like this:

1. Parse and validate a bounded batch in the application.
2. Load valid rows into a staging area with a run identifier.
3. Compare staged rows with current records in one query.
4. Write inserts and updates in set-based operations.
5. Record counts and rejected rows before accepting the checkpoint.

Batch size still matters. One transaction for a whole file can hold locks too long or run past memory and statement limits, so you want batches small enough to rerun and inspect, and that number should come from measuring representative data.

The catch is that a bad bulk update can wreck a lot of records very quickly. A dry_run flag that just skips the final write isn't much of a dry run. It should run the same parsing, matching and classification as the live path and report how many rows would be inserted, updated, ignored or rejected, with a sample of before and after values for risky changes. I'd put a threshold in config too, so if a modest refresh suddenly says almost every record has changed, the job stops (that's usually a timezone, whitespace or ID mapping problem). Each committed batch should leave a checkpoint with the run, source, batch boundary, rule version and counts, a retry should leave the destination in the same state, and the checkpoint shouldn't move until the transaction commits.

Once the runner says it's finished, I'd go and look at the destination: counts by partition, staged identifiers with no destination record, duplicate keys and a spot-check of transformed values, since totals can agree while individual records are wrong. Swapping thousands of careful per-record loops for one bulk query fixes the round trips, but it only pays off when the dry run, checkpoints and reconciliation are there to catch a bad update before it spreads across the whole table. The order I'd follow is to capture the old loop's query count and runtime on a representative batch, run the bulk path as a dry run, do one bounded live batch, reconcile it, and only then increase the batch size or run the full import.