SQL Refactoring Case Study: Optimizing Complex Queries in the JDL Routing System

In modern software development, continuous addition of features and bug fixes often causes codebases, including SQL scripts, to become bloated and hard to maintain, a phenomenon referred to as "code decay". When SQL queries suffer from similar decay, they become slow, unreadable, and difficult to maintain.

The article presents a real‑world example from the JDL routing system where a complex, production‑level SQL query was identified as a slow‑SQL hotspot. The original query combines multiple sub‑queries, joins, and conditional logic, resulting in poor performance and low readability.

Step 1 – Formatting

Proper formatting improves readability and sets the stage for further analysis.

select count(*) total_count from (
    select * from (
        select a.line_store_goods_id as line_resource_id, a.group_num as group_num, a.approval_erp as approval_erp, ...
        from line_store_goods a
        join line_resource b on a.line_code = b.line_code and a.start_node_code = b.start_node_code
        join line_resource c on a.parent_line_code = c.line_code and a.start_node_code = c.start_node_code
    ) temp
    where start_node_code = '311F001' and disable_time > '2023-11-15 00:00:00' and enable_time < disable_time
) t_total;

After formatting, the query’s purpose—counting routes that satisfy specific conditions—becomes evident.

Step 2 – Layer Decomposition

The query is broken down into logical layers (level0 to level4), each representing a distinct part of the data retrieval process.

-- level0
select count(*) total_count from t_total;

-- level1 – t_total
select * from temp where start_node_code = '311F001' and disable_time > '2023-11-15 00:00:00' and enable_time < disable_time;

-- level2 – temp
select a.line_store_goods_id as line_resource_id, a.group_num, a.approval_erp, ...
from line_store_goods a
join line_resource b on a.line_code = b.line_code and a.start_node_code = b.start_node_code
join line_resource c on a.parent_line_code = c.line_code and a.start_node_code = c.start_node_code;

Step 3 – Refactoring

Key refactoring techniques include merging temporary tables, pushing filter conditions down to the earliest possible stage, removing redundant joins, and simplifying expressions.

-- Optimized count without sub‑query
select count(*) total_count from temp where start_node_code = '311F001' and disable_time > '2023-11-15 00:00:00' and enable_time < disable_time;

Condition push‑down reduces the size of intermediate results, while eliminating unnecessary sub‑queries and duplicate joins cuts both CPU and I/O overhead.

Step 4 – Theoretical Validation

The refactored query is validated against the original business logic to ensure functional equivalence, acknowledging that production SQL may sometimes be functionally correct but not optimal.

select count(*) from (
    (select line_code FROM line_store_goods WHERE yn = 1 and parent_line_code = line_code and master_slave = 1 and start_node_code = '311F001') a
    join (select line_code, min(enable_time) as enable_time, max(disable_time) as disable_time
          from line_resource where yn = 1 and start_node_code = '311F001' group by line_code) b
    on a.line_code = b.line_code
) where disable_time > '2023-11-15 00:00:00' and enable_time < disable_time;

Step 5 – Index Optimization

Although not detailed here, appropriate indexing on columns such as start_node_code , disable_time , and enable_time further enhances performance.

Step 6 – Testing

Both functional tests (ensuring the same result set) and performance tests (measuring execution time and resource usage) are essential to confirm the refactoring’s success.

Finally, the article emphasizes that while refactoring principles are universal, each team should adopt tools and practices that best fit their workflow, and that good upfront design often prevents the need for extensive post‑hoc optimizations.