Tell about a heavy query that you had to optimize.

In one of the ERP system projects, there was a heavy query that generated a report on stock balances and movements over a long period with many filters and table joins. The query took a very long time — several minutes, which was unacceptable for users.

The optimization involved several steps:

• Analyzing the execution plan of the query to identify bottlenecks.
• Adding indexes on key fields involved in joins and filters.
• Breaking down the complex query into several intermediate queries, storing results in temporary tables.
• Using aggregate functions and precomputations to reduce the volume of data processed.
• Caching results for frequently repeated queries.

As a result, the execution time was reduced from several minutes to a few seconds, significantly improving the system's usability.