The six-hour batch job, and how we got it to 22 minutes

We inherited a nightly reconciliation job that took just over six hours and had quietly crept past its window - it was still running when the morning reports needed it. The instinct on a job like this is to reach for the obvious knobs: more memory, a bigger box, more threads. We did none of that first. We profiled it. For one full run we captured wall-clock time per stage, query counts, and row counts, and wrote them down. The result was the usual surprise: 78% of the runtime lived in a stage we had assumed was cheap, and the stage everyone complained about was under four minutes.

The first change came straight off that profile. The expensive stage was loading a batch of records and then, for each one, issuing a query to fetch related rows - a textbook N+1, running roughly 40,000 small queries per night. We replaced the per-row lookups with a single set-based query that joined and aggregated in the database, and added the index the join had been missing. The database was already good at this work; we had just been asking it one row at a time. That stage dropped from a little over four hours to about nine minutes.

The second change was redundant recomputation. The job recalculated the same exchange-rate and tax tables on every iteration, even though they were identical across the whole run. We computed them once, held them in memory for the duration, and stopped paying for the same arithmetic tens of thousands of times. On its own that was worth roughly forty minutes. It is the least glamorous kind of fix - you are deleting work, not adding cleverness - and it is often the highest return per line changed.

The third change was about shape. After the first two fixes the job was bottlenecked on a sequence of independent account batches processed one after another, each waiting on network round-trips to a downstream service. These batches did not depend on each other, so we ran them in parallel with a bounded pool of eight workers - bounded on purpose, so we did not trade a slow job for an overloaded downstream. That collapsed the remaining serial tail and took us under the line. Total: six hours and change to 22 minutes, comfortably inside the window with room to grow.

None of this was exotic. A set-based query, a cache, and bounded parallelism did about 90% of the work, and we would have found none of them quickly by reading the code and guessing. The lesson we take from every job like this is the same one: profile first, measure each stage, and let the numbers point. The bottleneck is rarely where you think it is, and the hours you save by measuring are almost always larger than the hours you spend doing it.