Challenge
An order system that had been fine for years was timing out at month-end. The proposed fix was a bigger server, which had already been quoted for and budgeted.
Approach
Profiled the actual query load rather than the symptom. The reporting screens were scanning a table that had grown past twenty million rows with no supporting index, and one report ran on every page load rather than on demand.
What we did
- Slow query profiling
- Index design & rollout
- Query rewrite
- Report execution moved off the request path
- Before-and-after benchmarking
Platform
Result
Month-end timeouts stopped without new hardware. The server upgrade was cancelled, which covered the cost of the engagement several times over.