How I Cut a 5‑Second SQL Query to Under 1 Second in a Critical Release

Case Background

A major version (0926) for project I required on‑site support and a strict freeze at 18:00 on September 24. Two hours before the freeze, performance testing revealed a page that took six seconds, exceeding the three‑second SLA by three seconds.

Problem Analysis

The page executed eight SQL statements. Although each individual statement was within acceptable limits, their combined execution time exceeded five seconds, with the remaining two seconds spent on Java‑side logic.

Query task list: 3 SQLs, total 1 s.

Query responsibility: 4 SQLs, total 3 s (dominant contributor).

Query network nodes: 1 SQL, 0.5 s.

The responsibility query alone accounted for five seconds of the total six‑second response time.

Investigation & Decision

After a 20‑minute discussion with the development lead, the author clarified the logic of the four responsibility‑related SQLs. Each SQL fetched a set of DUs (data units) for a specific permission level, progressively filtering the set based on earlier results.

To mitigate risk, the team split the work: the author would attempt to merge the SQLs, while the DBA optimized individual statements.

Solution Design

The four permission queries were essentially a priority‑ranking problem: for each DU, determine the highest‑priority permission it possessed. Instead of fetching the four result sets to Java and iterating, the author rewrote the logic entirely in SQL using Oracle's RANK() OVER construct to perform the ranking and selection in the database.

The merged SQL (shown in the image below) was executed directly in the DB and completed in 0.98 seconds.

Result

After deploying the merged query at 17:20, the responsibility lookup dropped from five seconds to under one second, bringing the total page load time below the three‑second requirement. The release proceeded smoothly.

Key Takeaways

SQL‑level optimization can dramatically reduce latency compared to Java‑side processing.

Having dedicated DB expertise (DBA/SQL engineer) is crucial for large projects; relying solely on Java developers for SQL leads to sub‑optimal code.

Complex permission checks are often better expressed with set‑based SQL features like RANK() rather than multiple round‑trips.

Time‑critical incidents require rapid collaboration and clear division of responsibilities.