A nightly process that had grown from twenty minutes to over four hours
The problem
An overnight reconciliation job had crept from twenty minutes to over four hours and regularly ran into the working day, delaying every downstream report. Nobody remaining on the team had written it, and the two engineers who understood it had left eighteen months earlier.
What we did
Profiled before changing anything. Two queries accounted for most of the runtime, so those were rewritten first and the remainder moved to incremental processing. We then added a regression test that fails the build if the job ever exceeds fifteen minutes. The problem was slow creep, so the fix had to include a tripwire.
The constraint
No downtime window was available, and there was no staging environment holding production-scale data. Building one became the first milestone rather than an assumption, which is where the schedule initially slipped.
We lost three weeks building a staging environment because we assumed one existed. That question now belongs in the first call, not the first sprint.