Improving the performance of a multi-company playback report

Problem

The global report brings playback and error records from hundreds of devices across multiple companies into a single screen. In production the report became sluggish: with all companies and a full day selected, the record list took 44–94 seconds. The same query, narrowed to a single company, returned instantly.

On closer inspection, several problems were stacked on top of each other:

• Filters were applied in the application after the data was fetched, not in the database.
• There was no suitable index for this query.
• A separate query was sent for every company and every device.
• The same heavy calculation was repeated many times for different report breakdowns.

Approach

• We moved the filters into the database and added an index suited to the query.
• We merged identical calculation requests arriving at the same time into a single calculation.
• We ran the per-device queries in parallel in a controlled way; this part became roughly 8 times faster.
• We cached results for past days; today’s data is always calculated live.

Result

The part where the per-device queries run became roughly 8 times faster. This measurement covers the improved query section, not the response time of the whole report. Caching past days reduced repeated calculations.

Lessons learned

The first version of a new API endpoint I added for the status report recalculated statistics from raw records, and the report went up to 47 seconds. The right approach was to read the summaries that were already prepared. Not every slowdown needs a new calculation; sometimes you just need to read from the right source.

My contribution

I did the performance analysis, the improvement plan and all of the fixes.