SQLFlash New Version Release

🎁 30 DAYS FREE ACCESS

Claim your free trial with code:

SQLFLASH-FREE30

Version Highlights: A Leap from Good to Great

We are proud to announce a milestone version update for SQLFlash. By introducing a revolutionary Two-Stage Intelligent Filter System and adding 31 new optimization rules covering core scenarios, the new version achieves a qualitative leap:

Core Hit Rate: Increased by over 117%, achieving double growth.
Rule Accuracy: Improved by 30%-40%, significantly reducing false positives.
Overall Analysis Efficiency: Boosted by 20%-30%, markedly shortening cycles.

This means SQLFlash can now identify and fix more SQL performance issues with higher precision and efficiency, making optimization suggestions almost “every one hits the mark.”

Core Innovation: The Two-Stage Intelligent Filter System

Behind the doubled hit rate is our new rule matching engine: the Two-Stage Intelligent Filter System. It solves the accuracy challenges inherent in the “cast a wide net” approach of traditional optimization tools.

Stage One: Fast Filter
Before SQL parsing, lightweight string and regex matching efficiently excludes 60-80% of inapplicable rules, drastically reducing the burden on subsequent analysis.
Stage Two: Precise Detector
Performs in-depth syntax structure analysis based on the SQL Abstract Syntax Tree (AST), ensuring each recommended rule accurately fits the current SQL’s context and logic, fundamentally eliminating misjudgments.

Thanks to this layered, efficient filtering mechanism, the system successfully overcomes the industry-wide dilemma of “more rules lead to slower analysis.” This allows the overall optimization time to remain stable even as the rule library continues to grow, achieving exceptional system scalability. This innovation delivers three key values: More precise optimization suggestions, faster analysis speed, and more reliable optimization quality.

Comprehensive Performance Leap: Quantifiable Improvements

Based on extensive testing in real business scenarios, the new version demonstrates significant performance gains across various query types, all targeting tangible performance benefits:

Data Slimming Optimizations (Reducing computation & scans): Average performance gain of 15%-78%
Intelligent Rewrite Optimizations (Helping the optimizer understand better): Average performance gain of 10%-65%
Instruction Simplification Optimizations (Removing invalid & redundant operations): Average performance gain of 8%-45%
Structural Reshaping Optimizations (Unlocking advanced techniques): Average performance gain of 12%-75%

Overview of New Optimization Capabilities

This update introduces 31 powerful new optimization rules, significantly expanding SQLFlash’s capabilities. We focused on the following key areas:

Data Slimming: Focuses on aggregate pushdown, pre-compression of conditional aggregates, merging duplicate scans, converting expensive JOIN to lightweight EXISTS, etc., reducing data processing volume at the source.
Intelligent Rewrite: Focuses on SARGable transformations, leveraging uniqueness constraints, semantic equivalent rewrites (e.g., LEFT JOIN to INNER JOIN), using native database efficient syntax (e.g., FILTER clause), etc., guiding the optimizer to choose the optimal path.
Instruction Simplification: Focuses on eliminating unreferenced redundant JOIN, duplicate WHERE conditions, invalid columns and derived tables, redundant DISTINCT and GROUP BY constants, etc., reducing the optimizer’s workload.
Structural Reshaping: Focuses on CTE refactoring and materialization, adjusting complex JOIN order, converting window functions to aggregates, precomputing expressions, etc., solving complex performance bottlenecks by refactoring query patterns.

For detailed functionality of each new rule, please refer to the appendix below.

Database Support & Usage Recommendations

Broad Compatibility: New rules are widely applicable to mainstream databases like MySQL, PostgreSQL, Oracle, SQL Server, MariaDB, TiDB, etc.
Zero Configuration, Intelligent Application: The system automatically analyzes and applies the most suitable optimization rules—no manual intervention required.
Test Before Deployment: We strongly recommend fully validating the optimization effects in a testing environment before deploying to production.

Summary

With this update, SQLFlash elevates the accuracy, coverage, and efficiency of SQL optimization to new heights through a revolutionary core engine upgrade and a comprehensive expansion of optimization capabilities. The effective optimization hit rate increasing by over 117%, achieving a doubling leap, is the best proof of our commitment to providing users with an ultimate performance experience. We believe the new version of SQLFlash will become an even sharper and more reliable data performance optimization tool in your hands.

Appendix: Detailed Breakdown of New Optimization Rules

1. Aggregate Optimization Rules (10)

Aggregate Pushdown & Preposition

Aggregate Preposition after JOIN Optimization
Aggregate Pushdown after UNION ALL Optimization
Window Function to Aggregate Conversion Optimization
Multi-Time Window Conditional Aggregate Optimization

Aggregate Calculation Optimization

Eliminate Redundant DISTINCT in Aggregate Functions using Uniqueness Constraints
Subquery Deduplication Optimization - DISTINCT instead of GROUP BY
Eliminate Redundant DISTINCT on Outer UNION ALL
External NULL Handling Optimization for Aggregate Functions
Conditional Aggregate Pre-compression Rule (Avg. perf. gain: 25.65%)

Conditional Aggregate Optimization

Conditional Aggregate FILTER Clause Optimization (PostgreSQL/Oracle) (Avg. perf. gain: 38.07%)

Aggregate Precomputation Optimization

Nested Subquery CTE Refactoring & Aggregate Precomputation (Avg. perf. gain: 44.25%)

2. JOIN Optimization Rules (7)

JOIN Structure Optimization

Eliminate Redundant JOINs for Unreferenced Tables (Avg. perf. gain: 19-25%)
Star Query Fact Table Priority JOIN Order Optimization
No-Output JOIN to EXISTS Semi-Join Conversion
Outer Join ON Condition Subquery CTE Extraction (Avg. perf. gain: 8.28%)

JOIN Semantics Optimization

Narrow Table First LEFT JOIN Order Optimization (Avg. perf. gain: 26.94%)
LEFT JOIN to INNER JOIN Conversion (Avg. perf. gain: 41.99%)
LEFT JOIN Chain Subquery Separation Optimization
Eliminate Derivable Transitive Equivalence Redundancy in LEFT JOIN

3. Subquery Optimization Rules (9)

Subquery Merge Optimization

Merge Multiple Independent Subqueries Scanning the Same Table
UNION Elimination Optimization
CTE Subquery Merge Optimization
Multiple LEFT JOIN to Same Table Merge Optimization
UNION Multi-Branch Subquery Deduplication Optimization

Subquery Rewrite Optimization

Scalar Subquery to LEFT JOIN Rewrite Optimization
Explicit Materialization for Frequently Referenced CTEs (Avg. perf. gain: 11.72%)

Subquery Structure Optimization

Remove Invalid Column Pruning Derived Tables & Redundant Conditional Aggregates
Nested Subquery CTE Refactoring & Aggregate Precomputation

4. Expression Optimization Rules (8)

Conditional Expression Optimization

Conditional Expression Simplification Optimization
String Matching Optimization (LIKE, REGEXP)
Aggregate Function Condition Optimization
Boolean Expression Absorption Law Optimization (Avg. perf. gain: 10.04%)
LIKE Wildcard Intelligent Optimization

Expression Precomputation Optimization

Formatting & Data Access Function Optimization
Repeated Calculation Elimination & Subquery Optimization
Precompute Expressions in Window Function/GROUP BY/HAVING/SELECT

SARGable Optimization

Eliminate Dynamic Condition Judgement, Use Static SARGable Filtering

5. Query Structure Optimization Rules (7)

Query Execution Optimization

WHERE Clause High-Selectivity Predicate Prepositioning
Multi-Table JOIN Driver Table Selection Optimization
SELECT Column Order Optimization (for temp table memory layout)
Multi-Table Query Table Alias Standardization
Parameter Sniffing & Join Algorithm Hint Optimization (SQL Server) (Avg. perf. gain: 77.56%)

Query Cleanup Optimization

WHERE Clause Duplicate Condition Elimination (Avg. perf. gain: 12.03%)
Remove Query-Level Redundant DISTINCT (Avg. perf. gain: 5.89%)
Use TEXT Type Instead of VARCHAR for JSON Extraction
GROUP BY Redundant Constant Column Elimination