Looking beyond a slow search panel
When a search panel started failing under load, we traced the problem to two large queries with many subqueries, one for search and another for data export. Improving the SQL was our first step, but the investigation also led us to review how the panel handled both operations.
What I worked on
I investigated the issue by checking execution times and tracing how the panel used each query, then helped improve the search query and optimised the export query myself.
What changed our approach
We initially focused on making the queries faster. Reviewing the panel with Tech Leads and SQL specialists made me question whether large exports needed to run through the panel at all. They used the same filters as search but returned more fields, through a different query.
The proposal is to move those exports into a separate batch process that generates the file and sends it to the user, while we improve the search query. This helped me look beyond the SQL and think about what work the panel should actually be doing.
Where it stands
Export · In production
The first export optimisation is live and verified in production: it now extracts 350 records in under a minute, compared with 160 in more than a minute before the change. Larger exports still hit the timeout, so we are working on further optimisation while supporting the panel redesign.
Search · In testing
Initial tests show the same query returning the same data in about 4 seconds rather than more than 60 seconds. These changes are still in testing, while the broader redesign will be implemented by the development team.