One bulk query can beat thousands of careful loops

A common data job starts with a list of records, performs a lookup for each one, decides what needs to change and writes the result. The code is easy to read. Each query is small. Running it against ten records gives an answer quickly, so the design can survive development and review without raising concern. A loop over 200,000 records can produce several database calls per record. Connection setup, network latency, query planning, index traversal and transaction overhead all repeat. Even when each call is cheap, the pipeline spends most of its time asking the database thousands of small questions. This is easy to misdiagnose as slow application code. More workers may increase connection pressure and lock contention, while larger instances can hide the query pattern for a while. Before changing the application, count how many round trips the job performs and how much work each trip carries. Total runtime is only the first measurement. I also record source records, database calls, rows read and written, batch duration and retries. A small sample with query counts enabled will usually reveal whether one logical operation expands into repeated lookups. If processing 100 records produces 301 calls, the likely shape is one initial read followed by a lookup and write for every item.

I also inspect the query plan for the repeated lookup. Bulk processing will not rescue a predicate that cannot use an index. It may simply turn a slow single-record query into a very large slow query. The matching columns, data types and normalisation rules need to agree before the work is grouped. The central change is to send records to the database as a set. Depending on the system, that may mean a staging table, a temporary table, a multi-row insert, an array parameter or a query that joins against an uploaded batch. The application still decides what a batch contains, but the database handles matching and updating within that batch. A staged import can use these phases:

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.

This reduces network traffic and lets the query planner choose a join strategy across the whole set. A unique key in the staging data can also expose repeated source identifiers before they reach the destination table. Batch size still matters. One transaction containing an entire file may hold locks for too long or exceed memory and statement limits. Choose a size large enough to reduce round trips and small enough to rerun, inspect and commit predictably. That number has to come from measurements on representative data. A boolean dry_run flag that merely skips the final write is not enough. The dry run should execute the same parsing, matching and classification logic as the live path. Its output should show how many rows would be inserted, updated, ignored or rejected. For risky updates, include a sample of proposed changes with stable identifiers and the relevant before and after fields. Sensitive values should stay out of ordinary logs, but reviewers still need enough information to recognise a broken mapping.

The dry run also needs threshold checks. A job expecting a modest refresh should stop if almost every existing record suddenly appears changed. That may indicate a timezone conversion, whitespace rule or identifier mapping has shifted. The threshold is an operational decision, so it belongs in configuration and in the run record rather than being buried in code. Each committed batch should leave a checkpoint that identifies the run, source, batch boundary, code or rule version, start and finish times, and result counts. If input order is stable, the boundary may be a row number. A durable source identifier or partition key is safer when files can be reordered. A retry should be idempotent. An upsert can help, but timestamps, generated identifiers, counters and side effects can still change on every attempt. Test that applying the same accepted batch twice leaves the destination in the same business state. Do not advance a checkpoint before the destination transaction commits, or a restart may skip work that never landed.

After the runner reports completion, query the destination for evidence. Reconciliation can compare source and destination counts by partition, check for missing source identifiers, report duplicate destination keys and verify a sample of transformed values. Totals can agree while individual records are wrong. Compare accepted staged rows with inserted and updated rows, then query for staged identifiers with no destination record. Keep rejected records with a reason and enough source context to correct or deliberately exclude them. Before replacing a loop, I would capture its query count and runtime on a representative batch. Then I would run the bulk path in dry-run mode, review operation counts, execute one bounded live batch and reconcile the destination. Only after those checks agree would I increase the batch size or run the full import.