All references

Optimising a management report over a database

A transport company (a long-term client of our team)

A key management report kept crashing and a single run took around two minutes. After a series of measured iterations we made it up to 10.4× faster — with functionally identical output.

Challenge

A report used daily by management crashed at random and a single run took around 2 minutes; every run scanned the whole table and put an extreme load on the database server. The client’s hard condition: the output must not change by a single row.

Solution

We identified the real bottleneck by measurement (execution plans, I/O statistics), not by guessing. Before the first change we built an automated test that compared every variant against the original output row by row — only a variant with identical results moved on to performance measurement. We worked strictly with read-only access, without touching the application or the data.

Results:

• Sped up from ~2 minutes to 11–24 seconds depending on the data window; largest measured 10.4×
• Database server load cut by an order of magnitude (from ~145 million reads to a fraction)
• Random report crashes eliminated
• Functionally identical output verified by automated comparison (7 measured versions, 140+ test artefacts)
• Handed over a test harness and diagnostic scripts reusable on other reports

Measure, don’t guess

We did not identify the real bottleneck by guessing, but by measurement — execution plans and I/O statistics. We tried and documented the dead ends too. The final solution combines pre-filtering the main table, a targeted optimiser hint, pre-aggregating the heavy sub-queries, and deterministic ordering for reproducible output.

A guarantee the output won’t change

The report is a basis for decisions, so it could not change by a single row. That is why, before the first change, we built an automated test: every query variant ran with the same parameters and its output was compared to the original cell by cell. Only a variant with matching results advanced to speed measurement. Exactly this systematic approach — generate variants, measure, compare automatically — is what lets optimisation be done quickly and with AI in the working process, yet always honestly, through evidence.