How to Refactor Complex SQL Like Java Code: A Step‑by‑Step Case Study

1. Introduction

Software development accumulates technical debt; SQL queries can also become “corrupted” over time, leading to poor performance, readability, and maintainability.

2. Case Overview – JDL Routing System

The article presents a production slow‑SQL identified in a logistics routing system, showing the original complex query that counts routes for a specific node.

3. Refactoring Process

Step 1 – Formatting

Standardize indentation to reveal that the query retrieves a count of lines matching certain conditions.

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, a.approval_person as approval_person, a.approval_status as approval_status, a.approval_time as approval_time, a.approval_remark as approval_remark, a.master_slave as master_slave, a.parent_line_code as parent_line_code, a.remarks as remarks, a.operator_time, a.same_stowage as same_stowage, b.start_org_id, b.start_org_name, b.start_province_id, b.start_province_name, b.start_city_id, b.start_city_name, b.start_node_code, b.start_node_name, b.end_org_id, b.end_org_name, b.end_province_id, b.end_province_name, b.end_city_id, b.end_city_name, b.end_node_code, b.end_node_name, b.depart_time, b.arrive_time, b.depart_wave_code, b.arrive_wave_code, b.line_code, b.line_type, b.transport_type, IF(a.store_enable_time > b.enable_time, IF(a.store_enable_time > c.enable_time, a.store_enable_time, c.enable_time), IF(b.enable_time > c.enable_time, b.enable_time, c.enable_time)) as insect_start_time, IF(a.store_disable_time < b.disable_time, IF(a.store_disable_time < c.disable_time, a.store_disable_time, c.disable_time), IF(b.disable_time < c.disable_time, b.disable_time, c.disable_time)) as insect_end_time FROM (select * FROM line_store_goods WHERE yn = 1 and master_slave = 1) a join (select start_org_id, start_org_name, start_province_id, start_province_name, start_city_id, start_city_name, start_node_code, start_node_name, end_org_id, end_org_name, end_province_id, end_province_name, end_city_id, end_city_name, end_node_code, end_node_name, depart_time, arrive_time, depart_wave_code, arrive_wave_code, line_code, line_type, transport_type, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource where line_code in (select line_code from line_store_goods WHERE yn = 1 ) and yn=1 group by line_code) b ON a.line_code = b.line_code and a.start_node_code = b.start_node_code join (select line_code,start_node_code, min(enable_time) as enable_time, max(disable_time) as disable_time from line_resource WHERE yn = 1 group by line_code) 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;

Step 2 – Layer Decomposition

Break the query into logical layers and rewrite each as a separate SELECT, exposing redundant temporary tables.

level0 & level1: select count(*) total_count from t_total level2:

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

level3: join_table – the core join of tables a, b, c

level4: individual sub‑queries a, b, c

Step 3 – Refactor 1: Merge Layers

Combine level0 and level1 into a single count(*) directly on the base table, eliminating an unnecessary temporary table.

select count(*) total_count from temp where a = "1"

Step 4 – Refactor 2: Push Down Conditions

Move the filter start_node_code = '311F001' into the lowest‑level sub‑query to reduce intermediate result size and memory usage.

Step 5 – Refactor 3: Join Optimization

Replace sub‑queries with explicit joins, remove redundant joins, and drop unused columns, improving readability and execution plan.

Step 6 – Validation

Run count queries on the refactored statement to ensure functional equivalence and measure performance gains.

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 7 – Index Tuning & Testing

Further index optimization is recommended, and both functional and performance testing are required to confirm the refactoring benefits.

4. Conclusion

SQL refactoring follows the same principles as code refactoring: improve readability, reduce temporary objects, push filters early, and simplify joins. Properly refactored queries are easier to understand, maintain, and execute faster, though verification through testing remains essential.