Why Splitting a Giant SQL Query Cut Report Time by 6 Seconds

Case Overview

A performance‑optimization task was launched for a one‑year‑old system that exhibited severe latency, with some reports lagging up to 143 seconds. The team, a four‑person “4321” group (4 members, 3 companies, 2 optimization domains, 1 tester), was tasked to improve the most critical reports before the 1017 release.

Challenges Identified

The selected six reports were read‑only but contained extremely complex SQL statements featuring nested WITH clauses, regular expressions, CONNECT BY, and other advanced constructs. The original SQLs lacked documentation of owners, performance metrics, and were difficult to understand.

Optimization Strategy

Classify data into dimensions (region, contractor, period, milestone) and facts (interval days), following data‑warehouse principles.

Separate dimension and fact data into distinct temporary tables.

Decompose the monolithic SQL into smaller queries that populate these temporary tables, then join them to produce the final report.

This approach allowed incremental validation of each sub‑query and ensured that the overall execution time could be measured against the original.

Implementation Details

Each report’s SQL was rewritten to extract dimension data (e.g., delivery area, contractor, period) and store it in temporary tables. After all temporary tables were populated, a final aggregation query combined them to generate the required rows, supporting dynamic milestone selection and full‑lifecycle calculations.

Sample diagram of the workflow is shown in the image below:

Results

After applying the refactored queries, the most critical report’s runtime dropped from roughly 10 seconds to about 6 seconds, surpassing the target improvement. The team received formal commendation from the project group.

Key Takeaways

Breaking a massive query into manageable pieces can dramatically improve performance, even when additional I/O for temporary tables is introduced. The success hinges on clear dimensional modeling, incremental testing, and a data‑driven validation process.