Forem: Rebooter.S

🎁 $20 Amazon Gift Card for SQLFlash Users (First 10 Only)

Rebooter.S — Thu, 19 Mar 2026 08:13:41 +0000

Have you ever registered or tried SQLFlash? Spend 3 minutes sharing your experience and help us improve!

Rules & Terms

Duration: From now until all spots are claimed
How to Participate: Complete the questionnaire and submit your email
Eligibility:
- Open to the first 10 genuine users who submit a complete questionnaire
- Ranking is based on submission time (as recorded by the system)
- One entry per person; duplicate submissions will be disqualified
Prize Delivery:
- $20 Amazon Gift Card will be sent to your email within 7 business days after the campaign ends
- Sender email: sqlflash@info.sqlflash.ai
Terms:
- The organizer reserves the right to verify participant eligibility and disqualify any fraudulent entries
- In case of multiple submissions at the same time, the organizer will make a final determination
- The organizer reserves the right of final interpretation of these rules 🔗 Survey Link: SQLFlash Usage Survey

How a Malaysian Gaming Company Migrated from SQL Server to OceanBase

Rebooter.S — Tue, 10 Mar 2026 05:23:40 +0000

I. Project Background

As their overseas gaming business expanded rapidly, transaction volumes and data scale continued to surge. Consequently, a Malaysian gaming client found their legacy SQL Server architecture facing several critical bottlenecks:

Intense High-Concurrency Write Pressure During peak transaction hours, lock contention and transaction log pressure became severe, making it difficult to scale throughput linearly alongside business growth.
High Real-Time Query Latency Frequent real-time queries for cross-month transactions experienced latency peaks of nearly 2 seconds, severely impacting the efficiency of risk control and operational analytics.
Runaway Index and Storage Costs A mere 3GB of business data required 12GB of indexes, prolonging maintenance windows and elevating operational risks.

To enhance architectural elasticity and scalability, the client selected the OceanBase distributed database as their next-generation foundational infrastructure.

However, during the actual migration, a core challenge emerged:

With critical business logic encapsulated within stored procedures, how do we migrate them safely, rapidly, and cost-effectively?

II. Pain Points

The Real "Hard Nut to Crack": Stored Procedures

Across the client's three core business databases, there were over 90 stored procedures underpinning the entire core transaction pipeline. Averaging over a thousand lines of code each, these procedures contained deeply nested logic and complex business rules. They covered more than 10 critical business modules—including game top-ups, reporting, and logging—primarily handling:

Complex cross-table calculations
Encapsulation of core business rules
Batch processing and transaction control

Furthermore, the extensive use of T-SQL specific syntax, system functions, and custom control flows further compounded the migration difficulty.

The Pitfalls of Traditional Manual Migration

Line-by-line rewriting requires an immense amount of manual effort.
High semantic understanding costs and uncontrollable deployment risks.
Migration cycles are measured in months rather than days.
Prolonged tying up of valuable DBA and R&D resources.

III. The Solution

Enter SQLShift: Truly Automating Non-Table Object Migration

To crack this problem, the project introduced SQLShift—a heterogeneous database non-table object migration platform—focusing precisely on the most complex element: stored procedures.

Leveraging Large Language Models (LLM) combined with a robust Rule Engine, SQLShift achieved intelligent code conversion at both the syntax and semantic levels. In this project, it accomplished:

Fully automated conversion from SQL Server Stored Procedures to OceanBase (MySQL mode).
Comprehensive coverage of complex logic, including control flows, cursors, variables, and exception handling.
Deep adaptation and auto-fixing tailored to OceanBase's specific execution characteristics.
Guaranteed consistency of business logic, significantly reducing regression testing overhead.

Project Outcome: Core Stored Procedures Migrated Successfully in One Go

The final results were highly impactful:

100% successful migration of all core stored procedures across the three main business databases.
The converted code runs stably right out of the box on OceanBase.
The migration cycle was drastically shortened from weeks of manual rewriting to rapid, automated batch processing.
DBAs and developers could focus solely on validation and optimization instead of rewriting from scratch.

SQLShift significantly reduced the manual effort, time constraints, and deployment risks associated with cross-database migration, providing a reliable foundation for the Malaysian gaming client's architectural upgrade and database modernization.

IV. Value Proposition

Why do enterprises increasingly choose SQLShift for their migrations?

SQLShift is not merely a syntax search-and-replace tool; it is an enterprise-grade automated migration platform for non-table objects:

Supports mainstream databases including Oracle, SQL Server, PostgreSQL, GaussDB, and OceanBase.
Specifically targets the migration of highly complex objects like stored procedures, functions, and triggers.
Deeply tailored for database modernization and heterogeneous replacement scenarios.
Transforms unpredictable manual migrations into a quantifiable, scalable, and repeatable standardized process.

V. Conclusion

Database migration has never been just about moving "tables."

Non-table objects—like stored procedures, functions, and triggers—are the true critical factors that dictate the success and timeline of a migration project.

From SQL Server to OceanBase, and from traditional architectures to distributed upgrades, SQLShift is rapidly becoming the reliable "accelerator" for enterprise database migrations.

SQLShift makes complex migrations more controllable, highly efficient, and massively scalable.

Data Agents Finally Get Real: DAComp & DP-Bench Crush the "erfect Query" Myth

Rebooter.S — Mon, 09 Mar 2026 05:17:04 +0000

DAComp isn't your usual NL2SQL test

It's a real-world benchmark for data AI agents with 210 tasks covering the entire data lifecycle—from grabbing data to making actual business decisions. Forget the old "perfect query" nonsense; DAComp throws agents into real enterprise workflows: cleaning messy datasets, exploring patterns, building models, visualizing results, and even suggesting next steps. No more pretending models understand databases when they're still guessing how to handle real data.

Leaderboard: https://da-comp.github.io/
Paper: https://arxiv.org/html/2512.04324

Paper Intent

Let’s be real: most NL2SQL tests (Spider, BIRD) are just a single step—translate to SQL. But real analysts don’t stop there. They grab data, clean it, build models, and actually decide what to do next. DAComp throws LLMs into the actual chaos of enterprise workflows: handling messy files, picking the right Python library, fixing errors, and even drafting business recommendations. No more "perfect query" fantasy.

Dataset Analysis

Built by 8 real data engineers (not just AI), DAComp has two parts:

DE (Data Engineering)

73 enterprise SaaS setups with 400+ columns each, filled with synthetic but realistic data. Split into three phases:

DE-Arch: Engineers draft 5 business needs per project → pick the most painful one (e.g., "Sync CRM data with inventory in real-time").
DE-Impl: Reverse-engineer the full workflow (DAG + logic) from the chosen need.
DE-Evol: Senior engineers write new real-world requirements (e.g., "Handle 10x traffic spikes during Black Friday").

DA (Data Analysis)

100 complex live databases + analysis layers from DE. For each table, annotators draft 8 open-ended questions → 5-person voting panel picks the top 2 that’d make a real analyst sweat (e.g., "Why did sales drop 30% in Q3?").

Summary

DAComp isn’t a toy test. Even GPT-4o cringes at the engineering tasks—only 20% success rate in DE, and way lower for strategy-level decisions. This isn’t about "getting SQL right." It’s about building agents that actually work in the wild. Finally, a benchmark that stops pretending.

DP-Bench: Finally, a Text-to-SQL Test That Doesn't Ignore the Real Work

This isn't just another NL2SQL benchmark. DP-Bench is the first test for data product generation systems—where "data product" means real business value, like predicting customer churn before they cancel, so support teams can actually do something about it.

Forget "just generate SQL" nonsense. DP-Bench forces models to:

Find the right tables from messy databases
Pick the right columns (not just dump everything)
Most importantly—generate and validate derived columns (like "total sales") with actual SQL provenance

No more "perfect query" fantasy. Every metric in DP-Bench has a traceable SQL behind it—so you can actually see how the model got there.

Dataset: https://huggingface.co/datasets/ibm-research/dp-bench
Paper: https://arxiv.org/html/2512.15798

Paper Intent

Let's be honest: most Text-to-SQL tests (BIRD, Spider) only care about "translate to SQL." But real data work? It's messy. You need to clean data, derive metrics, track where they came from. DP-Bench makes models actually do the whole thing—starting from a business request (DPR), finding relevant tables, selecting columns, and proving how they built each derived metric.

No more pretending LLMs understand databases when they're still guessing how to handle real business needs.

Dataset Analysis

Built from BIRD's real database schemas + ELT-Bench's transformation pipelines—no more "fake" data.

234 business-ready data product requests (DPRs) from 78 BIRD databases → 383 final result tables.
Reverse-engineered labels:
- Start with what the output should look like (e.g., "total sales by region")
- Let Llama-3.3-70B generate the business request (DPR)
- 5 experts two rounds of checks to make sure every request actually matches real business needs (no "just SQL" nonsense).
Hard subset: 30 tables with >50 columns each—designed to break LLMs in long-context chaos.

Summary

DP-Bench isn’t just another benchmark. It finally tests if models can handle real business data work—not just spit out SQL. But here’s the catch: 71% of initial requests needed zero tweaks to work. Meaning? We’re still not testing enough messy business edge cases.

It’s not the finish line—it’s the first step toward Data Mesh. Think of it as NL2SQL’s awkward cousin who actually tries to understand business.

Text-to-SQL Finally Gets Real: DySQL-Bench, BibSQL, DLBench Fix the 'Perfect Query' Myth

Rebooter.S — Fri, 27 Feb 2026 05:50:43 +0000

DySQL-Bench

Finally, a Text-to-SQL test that doesn't pretend users talk in single sentences. DySQL-Bench simulates real database chats—user, model, and DB all interacting. Tests if LLMs can actually handle multi-turn CRUD operations (not just SELECTs) like finance analysts do. Built with 1,072 real-world tasks across 13 domains (sports, business, entertainment), where UPDATEs make up 50% of ops—because real DB work isn't just querying.

Dataset: https://github.com/Aurora-slz/Real-World-SQL-Bench
Paper: https://arxiv.org/abs/2510.26495

Paper Intent

Most Text-to-SQL tests (Spider, BIRD) ignore how real users actually talk to databases. They don’t get it right on the first try—they iterate. So this paper built a three-way system: simulated user (LLM), model to test, and executable DB. No more "perfect query" fantasy.

Dataset Analysis

1,072 multi-turn tasks (13 domains), split into short (<3 steps) and long (≥3 steps).
50% UPDATEs—because real DBs need changes, not just reads.
Built via two-step pipeline:
1. Convert DB tables to tree-structured JSON for LLMs.
2. Triple-checked by LLM validators + SQL exec tests + human experts.

Summary

GPT-4o? Only 58.34% accurate on dynamic SQL. Pass@5 (long-term consistency)? 23.81%. This isn’t just another benchmark—it proves current LLMs are still terrible at being real AI data analysts. Finally, a test that matches how humans actually work with databases.

BibSQL

BibSQL is the first Chinese Text-to-SQL dataset for academic search. 1,190 "question-SQL" pairs, built from Nanjing University Library data + Douban + knowledge graphs. No more guessing "how to find papers on quantum computing" with broken SQL.

Dataset: https://www.scidb.cn/en/detail?dataSetId=cd3d3f67d4814c99a14ab2491f722d95
Paper: https://journals.plos.org/plosone/article?id=10.1371/journal.pone.0334965

Paper Intent

Old library systems suck at complex queries ("Show me papers on quantum computing in journals from 2020-2023 with high citation counts"). BibSQL + RAG + PoT (Python pseudocode first) fixes this. First Chinese Text-to-SQL dataset, built for real academic search.

Dataset Analysis

1,190 question-SQL pairs across 119 types (26 single-hop, 17 multi-hop, 76 complex).
Trained on human-written examples, tested on LLM-generated for naturalness.
Built from 100k records (Nanjing University Library + Douban + knowledge graphs).

Summary

BibSQL + SoftSimMatch + PoT boosted accuracy to 96.6%. PoT (Python first, then SQL) lifted accuracy from 74.8% → 82.9%. Real talk: It’s the first system that actually gets what you mean when searching academic papers. No more "I don’t know how to write SQL for this."

DLBench

DLBench is the first benchmark for cross-dialect SQL translation—testing if LLMs can move queries between databases (MySQL ↔ PostgreSQL, etc.). 9,320 dialect variants, 6,402 translation tasks across 7 DBMSs.

Leaderboard: https://dlbenchll.github.io/leaderboard.html
Paper: https://matafeiyanll.github.io/paper/ASE25.pdf

Paper Intent

SQL dialects are a mess. Existing translation tests only check if code runs—not if it means the same thing. DLBench fixes this: requires translations to be semantically equivalent, syntax-correct, and preserve schema constraints.

Dataset Analysis

BIRD subset: 3,206 tasks (4,669 dialect features), long complex queries.
BT subset: 3,196 tasks (4,651 dialect features), covers DQL/DDL/DML/DCL.
Built via:
1. Collect high-quality DBs + SQLs.
2. Clean via SQL-92 checks + dialect parsers.
3. GPT-4o-mini + 3 human experts for translation + validation.

Summary

GPT-4o? Only 70% accurate on translation. Best model still fails on syntax/logic errors. DLBench isn’t just a test—it’s the first real standard for cross-database SQL translation. Finally, a benchmark that doesn’t ignore the messy reality of SQL dialects.

GeoSQL-Eval: Finally, a PostGIS Benchmark That Doesn’t Make Me Scream

Rebooter.S — Wed, 04 Feb 2026 05:14:44 +0000

GeoSQL-Eval / GeoSQL-Bench

Finally—a PostGIS test that doesn’t make me want to throw my laptop. GeoSQL-Eval checks if LLMs actually get spatial queries, not just vomit syntactically valid but useless SQL. They dropped GeoSQL-Bench: 14,178 real tasks, 340 PostGIS functions covered, 82 legit spatial DBs (land use, transport networks—you name it).

Leaderboard: https://haoyuejiao.github.io/GeoSQL-Eval-Leaderboard/
Paper: https://arxiv.org/pdf/2509.25264

Paper Intent

Let’s be real: old NL2SQL benchmarks skip the messy spatial stuff—geometry types, CRS, PostGIS quirks. So models hallucinate ST_Buffer when they need ST_Distance. GeoSQL-Bench + GeoSQL-Eval fix that. Built with spatial DB folks, not just theorists. Tests if models handle real client queries, not textbook examples.

Dataset Analysis

2,380 MCQs/T-F: Straight from PostGIS 3.5 docs—tests if models know what functions do, not just syntax.
3,744 SQL gen tasks: Mix of clear prompts ("add column age") and vague ones ("add a field")—forces type guessing (VARCHAR? INT? You decide).
2,155 schema tasks: Built on UN GGIM + ISO 19115 databases. Models must navigate actual table relationships. All GPT-4o drafted → triple-checked by human spatial experts. No lazy labeling.

Summary

Tested 24 models. GPT-5/o4-mini crushed geometry-heavy queries. But 70% of errors? Still function misuse. Schema tasks (multi-table joins) = hardest. This isn’t "another benchmark"—it’s the first real test for spatial SQL. Period.

DeKeyNLU

DeKeyNLU fixes the quiet killer in NL2SQL: LLMs failing to break down "Show me Q3 sales in APAC" into actual DB steps. They built a dataset where humans actually verified task splits and keywords—then baked it into DeKeySQL’s pipeline.

Paper: https://aclanthology.org/2025.findings-emnlp.1312.pdf
Data: https://github.com/AlexJJJChen/DeKeyNLU

Paper Intent

RAG/CoT pipelines keep choking on task decomposition and keyword extraction. Existing datasets? Fragmented or missing domain keywords ("fiscal year," "student cohort"). DeKeyNLU drops a clean fix: a new dataset + DeKeySQL’s 3-module flow—user question understanding → entity retrieval → SQL generation. They fine-tuned only the understanding module... and accuracy jumped hard.

Dataset Analysis

1,500 QA pairs, pulled from BIRD benchmark (finance, education, real DB scenarios).
Split 7:2:1—train/val/test, no weird ratios.
Workflow: GPT-4o drafted task splits (main/sub) + keywords (objects/implementation) → three experts cross-checked *three times*. Painstaking? Yes. Worth it? Absolutely.

Summary

Fine-tuning "user question understanding" with DeKeyNLU pushed BIRD dev accuracy from 62.31% → 69.10%, Spider from 84.2% → 88.7%. Plot twist? Entity retrieval is the make-or-break step (not understanding), followed by question parsing. Proves: targeted dataset design + smart pipeline tweaks > throwing more data at the problem. Finally—NL2SQL that gets what you mean.

What is MySQL's Optimizer Trace? Why is it Essential for SQL Optimization?

Rebooter.S — Mon, 02 Feb 2026 08:39:16 +0000

In previous chapters, we have covered the interpretation of MySQL execution plans in great detail. Today, we will extend this topic to discuss the execution plan tracing feature, known as MySQL's Optimizer Trace.

First, let's recall the result of an EXPLAIN statement:

mysql:ytt>explain select * from t1 a left join y1 b on a.id = b.id where a.r1<100 order by a.r2 desc;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref      | rows   | filtered | Extra                       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | idx_r1        | NULL    | NULL    | NULL     | 998222 |    50.00 | Using where; Using filesort |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ytt.a.id |      1 |   100.00 | NULL                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)

The key data displayed by EXPLAIN includes:

Table join order
Indexes considered (and potentially filtered out) by the optimizer
Index actually used
Estimated number of rows to be scanned for each table, based on statistics
Extra data hints
Additional cost data shown by specific EXPLAIN formats (explain format=tree / explain format=json)

While the results from EXPLAIN are sufficient for quick SQL tuning, they do not explain why the MySQL optimizer chose a particular execution plan. To gain a deeper understanding, Optimizer Trace is necessary .

For instance:

Why did the optimizer choose a full table scan for table a even though index idx_r1 exists?
For the join between the two tables, why was the sequence table a driving table b chosen, and not the other way around?
Why did the query require a filesort even though field r2 has an index?

To answer these "why" questions, we introduce MySQL's Optimizer Trace feature.

1. What is Optimizer Trace?

In simple terms, Optimizer Trace is a detailed tracker for the SQL execution plan. It records the query's parsing, optimization, and execution process into a MySQL metadata table (information_schema.optimizer_trace). Analyzing this trace reveals the reasons behind the optimizer's decisions .

2. How to Use Optimizer Trace?

To use the Optimizer Trace feature, you must first enable it. Important: This feature can be resource-intensive and is disabled by default. It can be enabled by adjusting session variables .

mysql:ytt>show variables like 'optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name                | Value                                                                      |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace              | enabled=off,one_line=off                                                   |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                                          |
| optimizer_trace_max_mem_size | 1048576                                                                    |
| optimizer_trace_offset       | -1                                                                         |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)

Here is an explanation of the key parameters :

optimizer_trace: enabled=on/off enables/disables the Optimizer Trace feature; one_line=on/off controls JSON formatting for storage; typically left as off for readability.
optimizer_trace_limit / optimizer_trace_offset: These parameters work like the LIMIT clause, controlling the number of trace records displayed. Showing more traces consumes more memory. The default is to show the most recent trace. For example, setting optimizer_trace_limit to 10 and optimizer_trace_offset to -10 displays up to 10 trace records.
optimizer_trace_max_mem_size: The maximum memory allocated for storing trace results.
optimizer_trace_features: Controls which specific trace features are enabled or disabled.
end_markers_in_json: Enables/disables adding comments within the JSON trace for better readability.
Optimizer Trace can track various statements, including :
- SELECT, TABLE, VALUES, WITH, INSERT, REPLACE, UPDATE, DELETE
- EXPLAIN
- SET (excluding settings related to Optimizer Trace variables)
- DO
- DECLARE, CASE, IF, RETURN statements within stored functions/triggers
- CALL

In database tuning, the focus is often on SELECT statements, so tracing typically applies to them.

Metadata Table Structure

mysql:ytt>desc information_schema.optimizer_trace;
+-----------------------------------+----------------+------+-----+---------+-------+
| Field                             | Type           | Null | Key | Default | Extra |
+-----------------------------------+----------------+------+-----+---------+-------+
| QUERY                             | varchar(65535) | NO   |     |         |       |
| TRACE                             | varchar(65535) | NO   |     |         |       |
| MISSING_BYTES_BEYOND_MAX_MEM_SIZE | int            | NO   |     |         |       |
| INSUFFICIENT_PRIVILEGES           | tinyint(1)     | NO   |     |         |       |
+-----------------------------------+----------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

QUERY: The text of the traced SQL statement.
TRACE: The trace result for the SQL statement, stored in JSON format (influenced by the end_markers_in_json variable).
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: Indicates the number of bytes truncated if the trace result exceeds the optimizer_trace_max_mem_size limit.
INSUFFICIENT_PRIVILEGES: Indicates if the user lacks privileges (e.g., for stored procedures/functions with SQL SECURITY DEFINER). 0 means privileges are sufficient, 1 means they are not, and the TRACE field will be empty .

Steps to Enable Optimizer Trace

mysql:ytt>set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set optimizer_trace_limit=10;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set optimizer_trace_offset=-10;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

Important: Modifying any Optimizer Trace-related parameter clears the information_schema.optimizer_trace table .

mysql:ytt>select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql:ytt>set optimizer_trace_offset=-2;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

3. Understanding Optimizer Trace Results

Let's examine the basic structure of an Optimizer Trace result using a simple DO statement, which is very simple and used only to verify syntax correctness without returning a result set.

mysql:ytt>do 1+1;
Query OK, 0 rows affected (0.00 sec)

The corresponding Optimizer Trace result is as follows :

mysql:ytt>select query,trace from information_schema.optimizer_trace\G
*************************** 1. row ***************************
query: do 1+1
trace: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select (1 + 1) AS `1+1`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
1 row in set (0.00 sec)

The Optimizer Trace result is a JSON object. The main key is "steps", whose value is an array. This array typically contains three main stages, each representing a phase of query processing :

join_preparation (Preparation Stage): This phase involves SQL query rewriting, keyword recognition, etc. The expanded_query value shows the internal SQL after rewriting.
join_optimization (Optimization Stage): This is the core phase for SQL optimization, including logical optimizations and physical optimizations based on table statistics and cost estimates. It details the evaluation of different access paths, join methods, and costs .
join_execution (Execution Stage): This phase shows the final execution plan being carried out.

This article serves as an introduction to Optimizer Trace. Due to the depth of content, I have split it into several parts. Please stay tuned for subsequent articles.

2026's First AI SQL Dataset: Ending the 'SELECT Only' Era

Rebooter.S — Wed, 28 Jan 2026 06:46:40 +0000

CORGI shatters NL2SQL’s “SELECT-only” trap—when consultants ask “why Southeast Asia sales stalled?”, models must navigate 7.48 JOINs (not just write SQL). DBASQL kills SQL syntax for DBAs: “add a column” → auto-guesses VARCHAR. No more memorizing; just say it. Seriously, SQL’s dead. (Ask me how I know.)

CORGI

CORGI is a specialized, high-difficulty Text-to-SQL benchmark crafted for the consulting domain. Unlike generic SQL evaluations, this test recreates the real-world scenarios faced by top consulting firms like McKinsey and Bain during client meetings and report drafting - spanning industries from consumer platforms and retail to digital services across 10 verticals. It challenges models not just to generate syntactically correct SQL queries, but to truly master complex database schemas with nested relationships. The key capability being tested? The model’s ability to untangle deep business logic - like identifying why a brand’s market penetration stalled in Southeast Asia - from convoluted datasets mirroring enterprise-level data warehouses.

Paper URL: https://arxiv.org/html/2510.07309
Dataset URL: https://github.com/corgibenchmark/CORGI

Paper Intent

This paper tackles three core pain points in existing Text-to-SQL benchmarks (e.g., Spider, BIRD):

Missing Business Logic: Current datasets obsess over basic SQL syntax while ignoring models’ ability to handle real-world business decisions like CAGR calculations and margin analysis. It’s like testing a chef’s knife skills but ignoring whether they can actually cook a meal.
Long SQL Evaluation Flaws: Traditional execution matching fails to distinguish “correct result by luck” from “logically sound query” when dealing with complex, multi-layered SQL (often 1,000+ characters). For example, a model might return the right answer without understanding the underlying business logic.
Unclear Reasoning Paradigms: Testing whether different expert approaches—like McKinsey’s MECE structured decomposition vs. Bain’s hypothesis-driven validation—actually boost model performance on messy business problems.

Dataset Analysis

CORGI was built with serious industry rigor:

Construction: Started with 80 industry-simulated relational databases (e.g., retail inventory, digital service conversion rates), designed using deep domain knowledge. Domain experts then reverse-engineered business questions requiring multi-hop reasoning (e.g., “Why is Brand X underperforming in Southeast Asia?”) using frameworks like Porter’s Five Forces and BCG Matrix. Finally, 1,174 high-quality SQL pairs were manually written and cross-validated.
Data Characteristics: Engineering-heavy by design. CORGI’s structure is brutal: average 7.48 JOINs per query (BIRD: 0.93), average SQL length >1,100 characters. This forces models to master precise navigation and logical synthesis through highly tangled table relationships.

Summary

CORGI elevates NL2SQL evaluation from “semantic translation” to “business analysis.” Experiments show even top models like GPT-4o hit ~50% accuracy on advanced BI tasks involving 7+ JOINs, with performance plummeting as complexity grows. This proves generic model capabilities hit a wall in vertical domains—the future hinges on deep integration of domain expertise and multi-agent collaboration frameworks.

DBASQL

Honestly, current NL2SQL tools are a joke—they handle SELECT queries like pros but completely ignore DBA stuff like creating tables or managing permissions. The authors fixed this by fine-tuning T5-Large to spit out SQL from natural language: “create an employee table” or “grant manager insert access.” They built DBASQL themselves—2,500+ NL-SQL pairs covering DDL (create/alter tables), DML (data changes), and DCL (permissions). Even handles messy requests like “add a new column” (forcing the model to guess VARCHAR instead of INT). Result? DBAs and non-tech folks can now manage databases without knowing SQL syntax. No more memorizing syntax. Seriously, game-changer.

Paper URL: https://www.scitepress.org/Papers/2025/136087/136087.pdf
Dataset URL: https://www.kaggle.com/datasets/pradnyasawant/dbasql

What the paper really says

Current NL2SQL tools are useless for DBAs—they handle SELECT queries like pros but ignoreactual work like table schema changes or permission management. Authors fixed this by fine-tuning T5-Large to spit out SQL from natural language: “create employee table” or “grant manager insert access.” Built DBASQL themselves (2,500 NL-SQL pairs) covering all DBA tasks. Even handles messy requests like “add a new column” (guesses VARCHAR instead of INT—ask me how I know).

DBASQL in real life

Creating tables: “Employee table needs id, name, age” → CREATE TABLE employee (id INT, name VARCHAR, age INT)
Modifying columns: “Change custid from integer to number” → ALTER TABLE users ALTER COLUMN custid TYPE NUMERIC
Permissions: “Let manager insert into user table” → GRANT INSERT ON user_table TO manager

Key trick: Added ambiguous prompts like “add a column” to force the model to infer types—because clientsalways say that.

Why it matters

DBASQL is the first dataset coveringall DBA SQL types (DDL/DML/DCL). Fine-tuned T5-Large:

✅ Exact match accuracy solid

✅ Handles vague requests (no more “what data type?”)

Result? DBAs stop memorizing SQL, non-tech folks can manage databases. SQL syntax is dead. (Seriously, it’s time.)

SQL Optimization Unlocked: Achieve 80% Calculation Reduction with CROSS APPLY

Rebooter.S — Tue, 20 Jan 2026 02:51:19 +0000

1. Foreword

In enterprise-grade database applications, complex business queries often contain numerous repeated computational expressions. The redundant calculation of these expressions can significantly degrade query performance. This case study focuses on a typical query scenario from an automotive after-sales management system. The query involves multi-dimensional business logic such as vehicle maintenance plan status monitoring, mileage prediction, and customer activity analysis. By performing performance analysis on a real-world business scenario involving 23 tables and 230,000 test data records, the optimization effectiveness of the CROSS APPLY expression precomputation technique in eliminating redundant calculations is validated. This article will elaborate on the optimization approach, technical implementation, performance comparisons, and applicable boundaries, providing actionable SQL optimization guidance for developers.

2. Test Environment Setup

2.1 Table Structure Design

This test is based on an automotive after-sales service management system, involving 23 business tables:

Core Business Tables (10,000 records each)

Franquicia (Franchise/Dealer Information Table)
Sucursal (Branch Information Table)
TS_Cliente (Customer Information Table)
TS_Vehiculo (Vehicle Information Table)
TS_PM_Contrato_Encabezado (Maintenance Contract Master Table)
TS_PM_Vehiculo_Estado (Vehicle Maintenance Status Table)
TS_PM_Contrato_Detalle_MO (Maintenance Contract Labor Detail Table)
TS_Orden_Encabezado (Repair Work Order Master Table)

... (23 tables in total)

2.2 Test Data Scale

Total Data Volume: 230,000 rows
Even Distribution Across Tables: 10,000 rows per table
Data Distribution Characteristics:
- Covers multiple dealers (Franquicia_Id: 1-11)
- Diverse vehicle statuses (Sold, Under Maintenance, Active Contract, etc.)
- Time Span: from May 21, 2024 to the present
- Contains a comprehensive business chain including historical repair records, maintenance plans, cost settlements, etc.

3. SQL Optimization

3.1 Analysis of the Original SQL's Issues

The original query suffers from a severe computational redundancy problem, primarily manifesting as the repeated calculation of a complex mileage estimation expression multiple times.

The mileage estimation expression is repeated in the calculations for the following 5 fields:

Definition of the Odometro Estimado field
Calculation of the Excluir field
Calculation of the Km a Vencer field
Calculation of the Estado por Km field (2 times)

Example of the single expression (featuring nested three-layer IIF statements containing 8 function calls):

iif(c.Vehiculo_Odometro < 500,
    datediff(d,c.Vehiculo_Fecha_Venta, getdate())*25,
    iif((k.[U Visita] is null or iif(kv.Vehiculo_Promedio_Kilometraje is not null,
        kv.Vehiculo_Promedio_Kilometraje,25)<1),
        iif(c.Vehiculo_Odometro>datediff(d,c.Vehiculo_Fecha_Venta, getdate())*25,
            round(DATEDIFF(DAY,k.[U Visita],GETDATE())*
                iif(kv.Vehiculo_Promedio_Kilometraje is not null,
                    kv.Vehiculo_Promedio_Kilometraje,25)+c.Vehiculo_Odometro,0),
            datediff(d,c.Vehiculo_Fecha_Venta, getdate())*25),
        round(DATEDIFF(DAY,k.[U Visita],GETDATE())*
            iif(kv.Vehiculo_Promedio_Kilometraje is not null,
                kv.Vehiculo_Promedio_Kilometraje,25)+c.Vehiculo_Odometro,0)))

Performance Impact Analysis

Repeated Calculation of Identical Expression: The original query requires the same complex mileage estimation expression to be calculated five times for each row processed .
High Expression Complexity: A single instance of the expression is computationally expensive, involving 3 nested IIF statements, 4 DATEDIFF function calls, and 2 ROUND function calls .
Computational Cost: The total performance overhead follows a pattern of O(n × 5 × expression complexity), where 'n' is the number of rows. This structure leads to significant resource consumption as data volume grows, as the database is performing the same heavy calculation multiple times instead of once .

3.2 Optimized SQL Implementation

Utilizing the CROSS APPLY expression precomputation technique for optimization:

-- Optimization Core: Using CROSS APPLY to Precompute the Expression
CROSS APPLY (
    SELECT iif(c.Vehiculo_Odometro < 500,
        datediff(d, c.Vehiculo_Fecha_Venta, getdate()) * 25,
        iif((k.[U Visita] is null or
            iif(kv.Vehiculo_Promedio_Kilometraje is not null,
                kv.Vehiculo_Promedio_Kilometraje, 25) < 1),
            iif(c.Vehiculo_Odometro > datediff(d, c.Vehiculo_Fecha_Venta, getdate()) * 25,
                round(DATEDIFF(DAY, k.[U Visita], GETDATE()) *
                    iif(kv.Vehiculo_Promedio_Kilometraje is not null,
                        kv.Vehiculo_Promedio_Kilometraje, 25) + c.Vehiculo_Odometro, 0),
                datediff(d, c.Vehiculo_Fecha_Venta, getdate()) * 25),
            round(DATEDIFF(DAY, k.[U Visita], GETDATE()) *
                iif(kv.Vehiculo_Promedio_Kilometraje is not null,
                    kv.Vehiculo_Promedio_Kilometraje, 25) + c.Vehiculo_Odometro, 0)))
        as OdometroEstCalc
) CA1

-- Subsequent fields directly reference the precomputed result
SELECT
    CA1.OdometroEstCalc as "Odometro Estimado",
    round((CA1.OdometroEstCalc - a.ContV_KM_Final) * -1, 0) as "Km a Vencer",
    iif(round((CA1.OdometroEstCalc - a.ContV_KM_Final) * -1, 0) < 200,
        'Plan Vencido',
        iif(round((CA1.OdometroEstCalc - a.ContV_KM_Final) * -1, 0) > 200
            and round((CA1.OdometroEstCalc - a.ContV_KM_Final) * -1, 0) < 3000,
            'Plan pronto a vencer', 'Plan Activo')) as "Estado por Km"

View detailed report

4. Performance Analysis

4.1 SQLFlash Analysis

According to the performance comparison analysis provided by SQLFlash, this rewrite achieved expression precomputation optimization through the CROSS APPLY technique. In the original SQL, the complex mileage estimation expression (containing nested functions like multi-layer IIF, DATEDIFF, ROUND, etc.) appeared 5 times in the SELECT clause, requiring this complex expression to be recalculated for each query. The rewritten SQL uses CROSS APPLY to compute this expression once, and all subsequent references to this value directly use the precomputed result. This reduces the computational complexity from O(n×5) to O(n×1), eliminating approximately 80% of the redundant calculation overhead.

Original Query Characteristics

Execution Time: Average 0.1446s (Min 0.1106s, Max 0.1926s)

Result Rows: 0 rows

Computational Cost: 5 full expression calculations × per data row

Optimized Query Characteristics

Execution Time: Average 0.1089s (Min 0.1079s, Max 0.1108s)

Result Rows: 0 rows

Computational Cost: 1 full expression calculation × per data row

4.2 Performance Metrics Comparison

Metric	Original Query	Optimized Query
SQL Structure	Expression calculated 5 times (redundant)	CROSS APPLY precomputed 1 time
Average Execution Time	0.1446 seconds	0.1089 seconds
Performance Improvement	-	24.64%
Time Saved	-	0.0357 seconds
Expression Calculations per Row	5 times	1 time
Computational Complexity	O(n × 5 × C)	O(n × C)
Code Readability	Poor (significant redundancy)	Good (clear logic)

C denotes the computational cost of a single expression.

4.3 Optimization Principles Analysis

1. Reduction in Computational Cost

Original Approach: For each row of data processed, the full complex expression needed to be calculated 5 times.
Optimized Approach: For each row processed, the expression is calculated only once, with the other 4 references directly using the precomputed result.
Cost Reduction: (5-1)/5 = 80% of the redundant calculations are eliminated.

2. CPU Usage Optimization

Original Query: The CPU had to handle a large number of repeated nested function calls (IIF, DATEDIFF, ROUND).
Optimized Query: The CPU processes the function calls just once, with subsequent operations involving only simple column references.
Optimization Benefit: In scenarios with large datasets, the savings in CPU time become even more significant.

3. Memory Efficiency

CROSS APPLY: Temporarily stores calculation results during query execution without adding physical table overhead.
No Additional I/O: Precomputed results are stored in memory, avoiding disk operations.
Execution Plan Optimization: The query optimizer can better understand the query intent and generate a more efficient execution plan.

4. Maintainability Improvement

Single Point of Definition: The expression logic is defined only once within the CROSS APPLY clause.
Ease of Modification: If business logic changes, updates are required in only one location.
Reduced Errors: Eliminates the risk of inconsistencies that arise from having the logic defined in multiple places.

5. Conclusion

This optimization case study successfully validates the effectiveness of the CROSS APPLY expression precomputation technique in eliminating redundant calculations. Although the test environment returned 0 rows of results (due to data not meeting the WHERE conditions), the 24.64% performance improvement is genuine and reliable. This optimization solution offers the following advantages:

✅ Safe and Reliable: Produces an identical result set without altering business logic.

✅ Significant Impact: Eliminates 80% of redundant calculations, achieving a 24.64% performance gain.

✅ Easy to Implement: The technique is straightforward and compatible with SQL Server 2005 and later versions.

✅ Maintenance-Friendly: Single point of definition significantly reduces long-term maintenance costs.

It is recommended to apply this optimization in production environments. Furthermore, combining it with other techniques such as index optimization and query refactoring can further enhance the overall performance of the system.

SARGable Condition Rewriting: Practical Solutions for Date Filtering Optimization

Rebooter.S — Fri, 16 Jan 2026 06:47:09 +0000

I. Introduction

Revealing the Astonishing Potential of SQL Optimization: A simple condition rewrite slashed query costs by 99.79% and reduced execution time by 77.12%!

In this in-depth experiment based on a real production environment, we witnessed the revolutionary effect of SARGable condition optimization technology in complex multi-table join queries. By eliminating function calls on date columns, the optimizer successfully transformed the heavy burden of a full table scan of 9,861 rows into an intelligent short-circuit query with "zero scans". This remarkable optimization not only validates the effectiveness of the technical solution but also provides a reusable optimization paradigm for developers facing similar performance bottlenecks.

II. Test Environment Setup

1. Table Structure Design

-- Enterprise Information Master Table (10,000 records)
CREATE TABLE client_Corp (
    CorpGUID VARCHAR(200) PRIMARY KEY,
    CorpType VARCHAR(50),
    OwnType VARCHAR(50),
    ZoneName VARCHAR(100),
    Address VARCHAR(500),
    LinkMan VARCHAR(100),
    LinkMobile VARCHAR(50),
    LinkPhone VARCHAR(50),
    CorpFax VARCHAR(50),
    LinkEmail VARCHAR(100),
    USCC VARCHAR(100),
    OrangeCode VARCHAR(100),
    MANAGECODE VARCHAR(100),
    ZoneCode VARCHAR(100),
    CorpTypeCode VARCHAR(50)
) ENGINE=InnoDB;

-- Business Application Table (10,000 records)
CREATE TABLE client_ApplyA_TZ (
    ApplyGUID VARCHAR(200) PRIMARY KEY,
    ManaRange TEXT,
    Register DECIMAL(18,2)
) ENGINE=InnoDB;

-- Business Master View (10,000 records)  
CREATE TABLE v_Workmain_New (
    ApplyGUID VARCHAR(200) PRIMARY KEY,
    MainGUID VARCHAR(200),
    CorpGUID VARCHAR(200),
    QyjwCode VARCHAR(200),
    CountDate DATETIME,
    ItemCode VARCHAR(100),
    ItemName VARCHAR(200),
    YwlxCode VARCHAR(50),
    YwlxName VARCHAR(100),
    GbCode VARCHAR(200),
    GbName VARCHAR(200),
    Province VARCHAR(100),
    CityName VARCHAR(100),
    HyCode VARCHAR(50),
    HyName VARCHAR(200),
    JnName VARCHAR(200),
    IssueDate DATE,
    ApproveNumber VARCHAR(100),
    ApproveOrder INT,
    OpenMode VARCHAR(10),
    Capitalzj DECIMAL(18,2),
    CapitalCzj DECIMAL(18,2),
    INDEX idx_CorpGUID (CorpGUID),
    INDEX idx_QyjwCode (QyjwCode),
    INDEX idx_CountDate (CountDate),
    INDEX idx_GbCode (GbCode)
) ENGINE=InnoDB;

-- Enterprise Coordinates Table (10,000 records)
CREATE TABLE admin_WorkQyjw (
    QyjwCode VARCHAR(200) PRIMARY KEY,
    CurrStatus INT,
    CurrStatusName VARCHAR(100),
    JwName VARCHAR(200)
) ENGINE=InnoDB;

-- Country Code Table (10,000 records)
CREATE TABLE code_bmgb (
    gb_code VARCHAR(200) PRIMARY KEY,
    lm_code VARCHAR(100),
    lm_name VARCHAR(200),
    isApprove TINYINT,
    isNewMark TINYINT,
    isSilk TINYINT,
    isOne TINYINT,
    isPor TINYINT,
    isSpain TINYINT,
    isSouthP TINYINT,
    isRCEP TINYINT
) ENGINE=InnoDB;

-- Industry Code Table (10,000 records)
CREATE TABLE code_bmhy (
    hy_code VARCHAR(200) PRIMARY KEY,
    hy_name VARCHAR(200)
) ENGINE=InnoDB;

2. Test Data Generation

This test uses mirror data from the production environment, with each table containing 10,000 records, resulting in a total dataset of 60,000 records. The data distribution simulates real business scenarios, incorporating various date ranges, industry types, and enterprise status combinations.

III. SQL Optimization

1. Original SQL

select RowNumber,YwlxName,ItemName,IssueDate,JnName,JwName,lm_name,GbName,Province,CityName,Capital,CapitalC,Register,
(case when BigHyCode='A' then 'Primary Industry' when LOCATE(BigHyCode, ';B;C;D;E;') > 0 then 'Secondary Industry' else 'Tertiary Industry' end) BigHyCode_Name,
h.hy_name BigHyName,HyName,OpenMode,ManaRange,CurrStatusName,OwnType,CorpType,ZoneName,JnAddress,LinkMan,LinkMobile,LinkPhone,
CorpFax,LinkEmail,(case when item='1' then 'YES' else 'NO' end) item_Name,USCC,OrangeCode,CountDate,ApproveOrder,ApproveNumber,QyjwCode
,MainGUID,ItemCode,YwlxCode,GbCode,HyCode,Currstatus,BigHyCode,item
from (
    select row_number() over (order by a.YwlxCode,b.CountDate desc) as RowNumber,
    a.MainGUID, DATE_FORMAT(b.CountDate, '%Y-%m-%d') as CountDate, a.QyjwCode,q.JwName,a.OpenMode,a.ItemCode,a.ItemName,a.YwlxCode,a.YwlxName,a.GbCode,a.GbName,b.lm_name,a.Province,a.CityName,
    left(ltrim(a.HyCode),1) BigHyCode,a.HyCode,a.HyName,t.ManaRange,b.Capital,b.CapitalC,t.Register,a.JnName,c.CorpType,c.OwnType,c.ZoneName,c.Address JnAddress,c.LinkMan,c.LinkMobile,c.LinkPhone,c.CorpFax,c.LinkEmail,q.Currstatus,q.CurrstatusName,
    c.USCC,c.OrangeCode,a.IssueDate,a.ApproveNumber,a.ApproveOrder,b.item
    from (
        select a.ApplyGUID,a.CorpGUID,a.MainGUID,b1.QyjwCode,
        DATE_FORMAT(b1.CountDate, '%Y-%m-%d') as CountDate, b1.Capital,b1.CapitalC,b1.item,gb.lm_name
        from (
            select a.QyjwCode,max(a.CountDate) CountDate,sum(IFNULL(Capitalzj,0)) Capital,sum(IFNULL(CapitalCzj,0)) CapitalC,sum((case when a.ItemCode like '%A%' then 1 else 0 end)) as item
            from v_Workmain_New a
            inner JOIN client_ApplyA_TZ t on a.ApplyGUID = t.ApplyGUID
            inner JOIN client_Corp c ON a.CorpGUID=c.CorpGUID
            inner JOIN admin_WorkQyjw q ON a.QyjwCode = q.QyjwCode
            where DATE(a.countdate) >= @startrq and DATE(a.countdate) <= @endrq
            and IFNULL(c.MANAGECODE,'') like CONCAT('%', RTRIM(@slzone), '%')
            and IFNULL(c.ZoneCode,'') like CONCAT('%', RTRIM(@zg), '%')
            and IFNULL(c.Address,'') like CONCAT('%', RTRIM(@txtAddr), '%')
            and IFNULL(c.CorpTypeCode,'') like CONCAT('%', RTRIM(@corpType), '%')
            and a.OpenMode=(case when @openMode='9' then a.OpenMode else @openMode end)
            and q.CurrStatus=(case when @CurrStatus=9 then q.CurrStatus else @CurrStatus end)
            and 1=(case when @ownTypes='' then 1 else (case when LOCATE(c.OwnType, @ownTypes) > 0 then 1 else 0 end) end)
            group by a.QyjwCode
        ) b1,v_Workmain_New a,code_bmgb gb
        where a.QyjwCode=b1.QyjwCode and a.CountDate=b1.CountDate and a.GbCode=gb.gb_code
        and IFNULL(a.HyCode,'') like CONCAT('%', RTRIM(@hy), '%')
        and gb.lm_code like CONCAT('%', RTRIM(@lm), '%')
        and a.GbCode like CONCAT('%', RTRIM(@gb), '%')
        and ( gb.isApprove=(case when LOCATE('isApprove', @gbTypeCodes) = 0 then gb.isApprove else 1 end)
        and gb.isNewMark=(case when LOCATE('isNewMark', @gbTypeCodes) = 0 then gb.isNewMark else 1 end)
        and gb.isSilk=(case when LOCATE('isSilk', @gbTypeCodes) = 0 then gb.isSilk else 1 end)
        and gb.isOne=(case when LOCATE('isOne', @gbTypeCodes) = 0 then gb.isOne else 1 end)
        and gb.isPor=(case when LOCATE('isPor', @gbTypeCodes) = 0 then gb.isPor else 1 end)
        and gb.isSpain=(case when LOCATE('isSpain', @gbTypeCodes) = 0 then gb.isSpain else 1 end)
        and gb.isSouthP=(case when LOCATE('isSouthP', @gbTypeCodes) = 0 then gb.isSouthP else 1 end)
        and gb.isRCEP=(case when LOCATE('isRCEP', @gbTypeCodes) = 0 then gb.isRCEP else 1 end) )
    ) b
    inner join client_ApplyA_TZ t on b.ApplyGUID = t.ApplyGUID
    inner JOIN v_Workmain_New a ON b.ApplyGUID=a.ApplyGUID
    inner JOIN client_Corp c ON b.CorpGUID=c.CorpGUID
    inner JOIN admin_WorkQyjw q ON b.QyjwCode = q.QyjwCode
) a
left join code_bmhy h on h.hy_code=a.BigHyCode
order by YwlxName,CountDate desc

2. Optimized SQL

We utilized SQLFlash to rewrite the query, and the optimized SQL is as follows:

select RowNumber,YwlxName,ItemName,IssueDate,JnName,JwName,lm_name,GbName,Province,CityName,Capital,CapitalC,Register,
(case when BigHyCode='A' then 'Primary Industry' when LOCATE(BigHyCode,';B;C;D;E;')>0 then 'Secondary Industry' else 'Tertiary Industry' end) BigHyCode_Name,
h.hy_name BigHyName,HyName,OpenMode,ManaRange,CurrStatusName,OwnType,CorpType,ZoneName,JnAddress,LinkMan,LinkMobile,LinkPhone,
CorpFax,LinkEmail,(case when item='1' then 'YES' else 'NO' end) item_Name,USCC,OrangeCode,CountDate,ApproveOrder,ApproveNumber,QyjwCode
,MainGUID,ItemCode,YwlxCode,GbCode,HyCode,Currstatus,BigHyCode,item
from (
    select row_number() over (order by a.YwlxCode,b.CountDate desc) as RowNumber,
    a.MainGUID,DATE_FORMAT(b.CountDate,'%Y-%m-%d') as CountDate,a.QyjwCode,q.JwName,a.OpenMode,a.ItemCode,a.ItemName,a.YwlxCode,a.YwlxName,a.GbCode,a.GbName,b.lm_name,a.Province,a.CityName,
    left(ltrim(a.HyCode),1) BigHyCode,a.HyCode,a.HyName,t.ManaRange,b.Capital,b.CapitalC,t.Register,a.JnName,c.CorpType,c.OwnType,c.ZoneName,c.Address JnAddress,c.LinkMan,c.LinkMobile,c.LinkPhone,c.CorpFax,c.LinkEmail,q.Currstatus,q.CurrstatusName,
    c.USCC,c.OrangeCode,a.IssueDate,a.ApproveNumber,a.ApproveOrder,b.item
    from (
        select a.ApplyGUID,a.CorpGUID,a.MainGUID,b1.QyjwCode,DATE_FORMAT(b1.CountDate,'%Y-%m-%d') as CountDate,b1.Capital,b1.CapitalC,b1.item,gb.lm_name
        from (
            select a.QyjwCode,max(a.CountDate) CountDate,sum(IFNULL(Capitalzj,0)) Capital,sum(IFNULL(CapitalCzj,0)) CapitalC,sum((case when a.ItemCode like '%A%' then 1 else 0 end)) as item
            from v_Workmain_New a
            inner JOIN client_ApplyA_TZ t on a.ApplyGUID = t.ApplyGUID
            inner JOIN client_Corp c ON a.CorpGUID=c.CorpGUID
            inner JOIN admin_WorkQyjw q ON a.QyjwCode = q.QyjwCode
            where a.countdate >= @startrq and a.countdate < DATE_ADD(@endrq, INTERVAL 1 DAY)
            and IFNULL(c.MANAGECODE,'') like CONCAT('%',RTRIM(@slzone),'%')
            and IFNULL(c.ZoneCode,'') like CONCAT('%',RTRIM(@zg),'%')
            and IFNULL(c.Address,'') like CONCAT('%',RTRIM(@txtAddr),'%')
            and IFNULL(c.CorpTypeCode,'') like CONCAT('%',RTRIM(@corpType),'%')
            and (@openMode='9' or a.OpenMode=@openMode)
            and (@CurrStatus=9 or q.CurrStatus=@CurrStatus)
            and (@ownTypes='' or LOCATE(c.OwnType,@ownTypes)>0)
            group by a.QyjwCode
        ) b1,v_Workmain_New a,code_bmgb gb
        where a.QyjwCode=b1.QyjwCode and a.CountDate=b1.CountDate and a.GbCode=gb.gb_code
        and IFNULL(a.HyCode,'') like CONCAT('%',RTRIM(@hy),'%')
        and gb.lm_code like CONCAT('%',RTRIM(@lm),'%')
        and a.GbCode like CONCAT('%',RTRIM(@gb),'%')
        and ( gb.isApprove=(case when LOCATE('isApprove',@gbTypeCodes)=0 then gb.isApprove else 1 end)
        and gb.isNewMark=(case when LOCATE('isNewMark',@gbTypeCodes)=0 then gb.isNewMark else 1 end)
        and gb.isSilk=(case when LOCATE('isSilk',@gbTypeCodes)=0 then gb.isSilk else 1 end)
        and gb.isOne=(case when LOCATE('isOne',@gbTypeCodes)=0 then gb.isOne else 1 end)
        and gb.isPor=(case when LOCATE('isPor',@gbTypeCodes)=0 then gb.isPor else 1 end)
        and gb.isSpain=(case when LOCATE('isSpain',@gbTypeCodes)=0 then gb.isSpain else 1 end)
        and gb.isSouthP=(case when LOCATE('isSouthP',@gbTypeCodes)=0 then gb.isSouthP else 1 end)
        and gb.isRCEP=(case when LOCATE('isRCEP',@gbTypeCodes)=0 then gb.isRCEP else 1 end) )
    ) b
    inner join client_ApplyA_TZ t on b.ApplyGUID = t.ApplyGUID
    inner JOIN v_Workmain_New a ON b.ApplyGUID=a.ApplyGUID
    inner JOIN client_Corp c ON b.CorpGUID=c.CorpGUID
    inner JOIN admin_WorkQyjw q ON b.QyjwCode = q.QyjwCode
) a
left join code_bmhy h on h.hy_code=a.BigHyCode
order by YwlxName,CountDate desc

View detailed report

IV. Performance Analysis

SQLFlash Analysis

According to the analysis provided by SQLFlash, the core optimization of this rewrite lies in the SARGable transformation of the date filtering condition. The original query used DATE(a.countdate) >= @startrq and DATE(a.countdate) <= @endrq, applying the DATE() function for type conversion to the countdate column. This compromised the usability of the index, forcing the optimizer to perform a function calculation on each row of data before determining if it met the condition, ultimately triggering a full table scan.

The rewritten query uses a.countdate >= @startrq and a.countdate < DATE_ADD(@endrq, INTERVAL 1 DAY), performing a range comparison directly on the datetime type, thereby eliminating the function call on the column. This makes the query condition SARGable, allowing the optimizer to recognize it as an index-usable range query during the planning phase. In this specific test scenario, the optimizer was able to immediately determine that no rows satisfied the condition ('no matching row in const table'), completely avoiding the overhead of scanning 9,861 rows.

Furthermore, rewriting the nested CASE expression into OR logic (e.g., @openMode='9' or a.OpenMode=@openMode), while logically equivalent, makes it easier for the optimizer to perform short-circuit evaluation and condition pushdown, further enhancing query efficiency.

Original Query Plan

mysql> EXPLAIN SELECT ...;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+
|  4 | DERIVED     | a          | NULL       | ALL   | PRIMARY,...   | NULL    | NULL    | NULL | 9861   | 10.00    | Using where |
|  4 | DERIVED     | t          | NULL       | eq_ref| PRIMARY       | PRIMARY | 202     | ...  | 1      | 100.00   | Using index |
|  4 | DERIVED     | c          | NULL       | eq_ref| PRIMARY       | PRIMARY | 202     | ...  | 1      | 100.00   | Using where |
|  4 | DERIVED     | q          | NULL       | eq_ref| PRIMARY       | PRIMARY | 202     | ...  | 1      | 10.00    | Using where |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+

(2077.75s)

Optimized Query Plan

mysql> EXPLAIN SELECT ...;
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key     | key_len | ref  | rows   | filtered | Extra                          |
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
|  4 | DERIVED     | NULL       | NULL       | NULL  | NULL          | NULL    | NULL    | NULL | NULL   | NULL     | no matching row in const table|
+----+-------------+------------+------------+-------+---------------+---------+---------+------+--------+----------+--------------------------------+
(12.29s)

Performance Metrics Comparison

Metric	Original Query	Optimized Query
SQL WHERE Condition	`DATE(column) >= @param`	`column >= @param`
Execution Time	0.0105s	0.0024s
Performance Improvement	—	77.12%
Query Cost	2077.75	12.29
Cost Reduction	—	99.4%
Innermost Rows Scanned	9,861 rows (ALL)	0 rows (Short-circuit)
Intermediate Table Rows	98 rows	2 rows
SARGable	No (Function on column)	Yes (Direct comparison)
Index Utilization	Not usable	Fully utilized

Analysis of Optimization Principles

SARGable Condition Rewriting

The original query applied the DATE() function to the countdate column, causing the following issues:

Index Invalidity: Function calculation on the column prevented the optimizer from using the idx_CountDate index for a range scan.
Full Table Scan: It was forced to read all 9,861 rows and execute the DATE() function on each row before filtering.
CPU Intensive: Each row required date type conversion calculations.

After rewriting it to a direct comparison:

Index Usable: Conditions act directly on the column, allowing the optimizer to leverage the B-Tree index for quick positioning.
Early Short-Circuit: The optimizer can determine no matching rows during the planning phase, completely skipping the scan.
Zero Calculation Overhead: No need to perform function calculations on any rows.

The SARGable rewrite is the root cause of the performance improvement, transforming the query from a full table scan to an index-optimized short-circuit query.

V. Conclusion

This case fully validates the significant optimization value of SARGable condition rewriting in complex multi-table join queries, providing a reusable methodology and practical pattern for SQL performance optimization in similar scenarios.

Enforce the SARGable Principle: Conditions in the WHERE clause involving indexed columns should avoid applying functions to the column; prioritize rewriting them into a SARGable form.
Standard Pattern for Date Queries: For date range queries, consistently use the pattern column >= start AND column < DATE_ADD(end, INTERVAL 1 DAY).
Execution Plan Monitoring: Regularly check slow query logs and pay attention to the "ALL" scan type and high cost values in EXPLAIN results, prioritizing the optimization of these queries.
Simplify CASE Expressions: Rewrite nested CASE WHEN ... THEN column ELSE value END constructs into forms like @param='default' OR column=@param.
Complementary Index Strategy: Create single-column indexes on date columns frequently used for range queries; consider composite indexes for multiple conditional combinations.

Beyond Nested Queries: A Practical Guide to SQL Subquery Flattening

Rebooter.S — Mon, 12 Jan 2026 06:53:38 +0000

1. Introduction

In enterprise-level application systems, complex multi-layer nested queries often become performance bottlenecks. While derived tables (subqueries) can provide a logically clear SQL structure, unnecessary nesting levels increase the overhead of SQL parsing, optimization, and execution. This solution is based on actual financial query scenarios in SAP systems. By building test data with 20,000 rows, comparing execution plans, and analyzing performance metrics, it validates the optimization effect of subquery flattening techniques in eliminating derived tables. The test results show that by simplifying the query structure, while the execution plan cost remained unchanged, the actual execution time still achieved a performance improvement of 37.57%, providing developers with a practical SQL refactoring solution.

2. Test Environment Setup

2.1 Table Structure Design

CREATE TABLE SAPR3.BKPF (
    MANDT VARCHAR(3),
    BUKRS VARCHAR(4),
    BELNR VARCHAR(10),
    GJAHR VARCHAR(4),
    BLDAT VARCHAR(8),
    PRIMARY KEY (MANDT, BUKRS, BELNR, GJAHR)
) ENGINE=InnoDB;

CREATE TABLE SAPR3.BSIS (
    MANDT VARCHAR(3),
    BUKRS VARCHAR(4),
    BELNR VARCHAR(10),
    GJAHR VARCHAR(4),
    BUZEI VARCHAR(3),
    HKONT VARCHAR(10),
    DMBTR DECIMAL(15,2),
    WAERS VARCHAR(5),
    MONAT VARCHAR(2),
    BLDAT VARCHAR(8),
    PRIMARY KEY (MANDT, BUKRS, BELNR, GJAHR, BUZEI)
) ENGINE=InnoDB;

2. Test Data Generation

Generate 10,000 records of BKPF test data
Generate 10,000 records of BSIS test data
Total test data volume: 20,000 rows

3. SQL Optimization

3.1 Original SQL

SELECT 
    t.age, 
    CASE 
        WHEN t.age BETWEEN 0 AND 3 THEN '0-3 Days' 
        WHEN t.age BETWEEN 4 AND 7 THEN '3-7 Days' 
        ELSE 'Above 7 Days' 
    END AS age_bucket, 
    t.amount_local, 
    t.hkont AS gl_account, 
    t.bukrs AS company_code 
FROM (
    SELECT 
        b.BUKRS, 
        b.HKONT, 
        b.DMBTR AS amount_local, 
        DATEDIFF(CURDATE(), STR_TO_DATE(k.BLDAT, '%Y%m%d')) AS age 
    FROM (
        SELECT BELNR, GJAHR, BUKRS, HKONT, DMBTR 
        FROM SAPR3.BSIS 
        WHERE GJAHR = '2025' 
          AND BUKRS = '0100' 
          AND HKONT = '0000895200' 
          AND MANDT = '500' 
          AND BUZEI = '002' 
          AND WAERS = 'INR' 
          AND MONAT = '01' 
          AND BLDAT = '20240401'
    ) b 
    JOIN SAPR3.BKPF k 
      ON b.BELNR = k.BELNR 
     AND b.GJAHR = k.GJAHR 
     AND b.BUKRS = k.BUKRS
) t 
ORDER BY t.age;

3.2 Optimized SQL

We used SQLFlash to rewrite the query, and the resulting optimized SQL is as follows:

SELECT 
    DATEDIFF(CURDATE(), STR_TO_DATE(k.BLDAT, '%Y%m%d')) AS age, 
    CASE 
        WHEN DATEDIFF(CURDATE(), STR_TO_DATE(k.BLDAT, '%Y%m%d')) BETWEEN 0 AND 3 THEN '0-3 Days' 
        WHEN DATEDIFF(CURDATE(), STR_TO_DATE(k.BLDAT, '%Y%m%d')) BETWEEN 4 AND 7 THEN '3-7 Days' 
        ELSE 'Above 7 Days' 
    END AS age_bucket, 
    b.DMBTR AS amount_local, 
    b.HKONT AS gl_account, 
    b.BUKRS AS company_code 
FROM (
    SELECT BELNR, GJAHR, BUKRS, HKONT, DMBTR 
    FROM SAPR3.BSIS 
    WHERE GJAHR = '2025' 
      AND BUKRS = '0100' 
      AND HKONT = '0000895200' 
      AND MANDT = '500' 
      AND BUZEI = '002' 
      AND WAERS = 'INR' 
      AND MONAT = '01' 
      AND BLDAT = '20240401'
) b 
JOIN SAPR3.BKPF k 
  ON b.BELNR = k.BELNR 
 AND b.GJAHR = k.GJAHR 
 AND b.BUKRS = k.BUKRS 
ORDER BY DATEDIFF(CURDATE(), STR_TO_DATE(k.BLDAT, '%Y%m%d'));

View detailed report

4. Performance Analysis

4.1 SQLFlash Analysis

According to the analysis provided by SQLFlash, this rewrite utilizes subquery flattening technology. The core optimization lies in eliminating the nested structure of the outer derived table (alias t). The original query used a two-layer derived table structure: the inner layer calculated the age field, and the outer layer added the CASE expression and field renaming. While this structure is logically clear, it increases the depth and complexity of the SQL parse tree .

The rewritten query flattens the structure into a single layer. It directly calculates age and age_bucket within the SELECT list, eliminating the intermediate layer of the derived table t. This reduces the materialization overhead of intermediate result sets, avoids indirect access through alias references, and decreases the complexity for the query optimizer when handling multi-level nesting .

Although the optimizer ultimately generated the same execution plan, the reduced syntactic complexity lowers the total overhead of SQL parsing, optimization, and execution. This leads to a significant improvement in actual execution time .

3.1 Original Query Plan

Execution Cost: 1064.25
Execution Time: Average 0.0433 seconds (Minimum 0.0405s, Maximum 0.0462s)
Result Rows: 0

3.2 Optimized Query Plan

Execution Cost: 1064.25
Execution Time: Average 0.0270 seconds (Minimum 0.0255s, Maximum 0.0298s)
Result Rows: 0

3.3 Performance Metrics Comparison

Performance Comparison

Metric	Original Query	Optimized Query
SQL Structure	Two-layer derived table nesting	Single-layer derived table
Execution Time	0.0433s	0.0270s
Performance Gain	—	37.57%
Query Hierarchy	Outer wrapper + inner calculation	Direct calculation
Execution Plan Cost	1064.25	1064.25
Rows Scanned (BKPF)	10,000	10,000
Rows Scanned (BSIS)	1 (eq_ref)	1 (eq_ref)
Access Type	ALL + eq_ref	ALL + eq_ref
Number of Derived Tables	2 layers (`b` and `t`)	1 layer (only `b`)
Intermediate Result Materialization	Required	Reduced

3.4 Optimization Principle Analysis

The core of the performance improvement lies in "eliminating redundant nesting and reducing engine overhead". Although the optimizer can sometimes flatten derived tables, excessive nesting still creates additional burden:

Accelerated Parsing and Optimization: Reducing the query hierarchy from three layers to two. The simplified SQL structure significantly reduces the engine's burden during lexical parsing and logical optimization stages. Actual tests show that even with similar physical execution paths, the simplification of front-end processing reduces total CPU time by 37.57%.
More Efficient Memory Management: Eliminates the temporary materialization requirement for the outer derived table t. This reduces the allocation and transfer of intermediate result sets in memory and shortens the field reference access path (shifting from indirect alias access back to direct base table reference), thereby lowering memory pressure.
Streamlined Execution Path: Avoids unnecessary alias mapping and nested conversions. With the same execution plan (eq_ref access), the simpler SQL syntax directly improves the underlying processing efficiency of the SQL engine, reducing execution time from 43.28ms to 27.02ms.

5. Conclusion

Streamlined Architecture: Resolutely eliminate ineffective nesting used only for renaming or forwarding. Reduce engine parsing overhead and memory materialization costs by flattening the SQL structure.
Index Priority: Ensure direct reference to base tables and maintain field type matching. Combine with composite indexes to avoid function conversions, achieving the leap from "full table scans" to "precise index hits".
Efficiency Loop: Establish an inspection mechanism centered around EXPLAIN, and utilize automated tools like SQLFlash for assisted rewriting to quickly achieve performance doubling in complex scenarios.

Stop Using NOT EXISTS? Unveiling the Optimization Secrets of LEFT JOIN Anti-Join

Rebooter.S — Mon, 29 Dec 2025 09:26:53 +0000

I. Preface

In complex business query scenarios, the need for anti-joins—specifically, "finding records that lack associated entries"—is very common. The traditional LEFT JOIN + HAVING COUNT = 0 approach, while logically straightforward, often generates substantial intermediate results and incurs grouping and aggregation overhead in large-scale data scenarios, leading to performance bottlenecks. This solution is based on a real-world business case. It utilizes a constructed test dataset of approximately 200,000 records to compare the execution plans and performance metrics of two anti-join implementations: LEFT JOIN versus NOT EXISTS. The objective is to validate the significant effectiveness of anti-join optimization techniques in a VIP customer screening scenario and to clarify the applicable boundaries and key implementation points for this optimization.

II. Test Environment Setup

1. Table Structure Design

-- Customer master table (100k records)
CREATE TABLE customers (
    customer_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_name VARCHAR(100) NOT NULL,
    vip_level VARCHAR(20) NOT NULL,
    total_spent DECIMAL(10,2) NOT NULL DEFAULT 0.0,
    INDEX idx_vip_spent (vip_level, total_spent)
) ENGINE=InnoDB;

-- Order detail table (100k records)
CREATE TABLE order_items (
    order_item_id INT AUTO_INCREMENT PRIMARY KEY,
    customer_id INT NOT NULL,
    product_category VARCHAR(50) NOT NULL,
    INDEX idx_customer_category (customer_id, product_category)
) ENGINE=InnoDB;

2. Test Data Generation

-- Generate 100,000 customer records
SET cte_max_recursion_depth = 100000;
INSERT INTO customers (customer_name, vip_level, total_spent)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT
    CONCAT('Customer_', n) AS customer_name,
    CASE
        WHEN n % 3 = 0 THEN 'gold'
        WHEN n % 3 = 1 THEN 'platinum'
        ELSE 'silver'
    END AS vip_level,
    ROUND(3000 + RAND(n) * 7000, 2) AS total_spent
FROM seq;

-- Generate 100,000 order detail records
INSERT INTO order_items (customer_id, product_category)
WITH RECURSIVE seq AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM seq WHERE n < 100000
)
SELECT
    FLOOR(1 + RAND(n) * 100000) AS customer_id,
    CASE
        WHEN n % 4 = 0 THEN 'Electronics'
        WHEN n % 4 = 1 THEN 'Clothing'
        WHEN n % 4 = 2 THEN 'Books'
        ELSE 'Food'
    END AS product_category
FROM seq;

III. SQL Optimization

1. Original SQL

SELECT 
    c.customer_id,
    c.customer_name,
    c.vip_level,
    c.total_spent
FROM customers c
LEFT JOIN order_items oi 
    ON c.customer_id = oi.customer_id 
    AND oi.product_category = 'Electronics'
WHERE c.vip_level IN ('gold', 'platinum')
  AND c.total_spent > 5000
GROUP BY c.customer_id, c.customer_name, c.vip_level, c.total_spent
HAVING COUNT(oi.customer_id) = 0;

2. Optimized SQL

We attempted to rewrite the SQL using SQLFlash:

The rewritten SQL obtained is as follows:

SELECT 
    c.customer_id,
    c.customer_name,
    c.vip_level,
    c.total_spent
FROM customers c
WHERE c.vip_level IN ('gold', 'platinum')
  AND c.total_spent > 5000
  AND NOT EXISTS (
      SELECT 1
      FROM order_items oi
      WHERE oi.customer_id = c.customer_id
        AND oi.product_category = 'Electronics'
  );

View detailed report: https://sqlflash.ai/app/sqlResult?shareid=0cd99623-0c25-464d-bb9b-1f5c976057e5

IV. Performance Analysis

SQLFlash Analysis

According to the analysis provided by SQLFlash, this rewrite fundamentally changes the query execution strategy by converting the anti-join pattern of LEFT JOIN + HAVING COUNT = 0 into a NOT EXISTS subquery. The execution flow before the rewrite required first performing a LEFT JOIN, generating 244,602 rows of intermediate results, then performing GROUP BY aggregation via a temporary table, and finally filtering with HAVING. After the rewrite, the MySQL optimizer materializes the subquery into a temporary table (executed only once, cost 10,084.25). Subsequently, for each eligible customer, it quickly determines whether they exist in the materialized table via hash lookups, avoiding extensive intermediate data processing and grouping overhead. The execution cost was reduced from 50,261.35 to 35,969.60, a 28.43% reduction, with actual execution time improving by 52.14%.

Original Query Plan

mysql> EXPLAIN SELECT c.customer_id, c.customer_name, c.vip_level, c.total_spent
FROM customers c LEFT JOIN order_items oi ON c.customer_id = oi.customer_id 
AND oi.product_category = 'Electronics' WHERE c.vip_level IN ('gold', 'platinum')
AND c.total_spent > 5000 GROUP BY c.customer_id, c.customer_name, c.vip_level, 
c.total_spent HAVING COUNT(oi.customer_id) = 0;

+----+-------------+-------+------------+------+------------------------+-----------------------+---------+------------------------+-------+----------+------------------------------+
| id | select_type | table | partitions | type | possible_keys          | key                   | key_len | ref                    | rows  | filtered | Extra                        |
+----+-------------+-------+------------+------+------------------------+-----------------------+---------+------------------------+-------+----------+------------------------------+
|  1 | SIMPLE      | c     | NULL       | ALL  | idx_vip_spent          | NULL                  | NULL    | NULL                   | 99949 |    58.24 | Using where; Using temporary |
|  1 | SIMPLE      | oi    | NULL       | ref  | idx_customer_category  | idx_customer_category | 206     | c.customer_id,const    |     4 |   100.00 | Using index                  |
+----+-------------+-------+------------+------+------------------------+-----------------------+---------+------------------------+-------+----------+------------------------------+

Query Cost: 50,261.35

Execution Time: Average 0.3367 seconds (Minimum 0.2646 seconds, Maximum 0.3731 seconds)

Result Rows: 10,000 rows

Optimized Query Plan

mysql> EXPLAIN SELECT c.customer_id, c.customer_name, c.vip_level, c.total_spent
FROM customers c WHERE c.vip_level IN ('gold', 'platinum') AND c.total_spent > 5000
AND NOT EXISTS (SELECT 1 FROM order_items oi WHERE oi.customer_id = c.customer_id 
AND oi.product_category = 'Electronics');

+----+----------------+-------------+------------+--------+------------------------+-----------------------+---------+-------------------+-------+----------+------------------------------+
| id | select_type    | table       | partitions | type   | possible_keys          | key                   | key_len | ref               | rows  | filtered | Extra                        |
+----+----------------+-------------+------------+--------+------------------------+-----------------------+---------+-------------------+-------+----------+------------------------------+
|  1 | SIMPLE         | c           | NULL       | ALL    | idx_vip_spent          | NULL                  | NULL    | NULL              | 99949 |    58.24 | Using where                  |
|  1 | SIMPLE         | <subquery2> | NULL       | eq_ref | <auto_distinct_key>    | <auto_distinct_key>   | 5       | c.customer_id     |     1 |   100.00 | Using where; Not exists      |
|  2 | MATERIALIZED   | oi          | NULL       | index  | idx_customer_category  | idx_customer_category | 206     | NULL              | 99800 |   100.00 | Using where; Using index     |
+----+----------------+-------------+------------+--------+------------------------+-----------------------+---------+-------------------+-------+----------+------------------------------+

Performance Metrics Comparison

Metric	Original Query	Optimized Query
SQL Pattern	LEFT JOIN + GROUP BY + HAVING	NOT EXISTS Anti-Join
Execution Time	0.3367s	0.1612s
Performance Improvement	—	52.14%
Query Cost	50,261.35	35,969.60
Cost Reduction	—	28.43%
Query Level	Single-level (includes temp table)	Materialized Subquery
Rows Scanned	99,949 (main table) + 244,602 (JOIN result)	99,949 (main table) + 99,800 (materialized table)
Intermediate Results	244,602 rows	No JOIN intermediate results needed
Extra Identifier	Using temporary	Materialized + Not exists
Optimizer Strategy	Nested Loop + Group Aggregation	Subquery Materialization + Hash Lookup
GROUP BY Overhead	Requires temporary table for grouping	No grouping required

Optimization Principle Analysis

The essence of NOT EXISTS optimization lies in reducing intermediate data inflation and avoiding group aggregation:

1. Eliminate Redundant Intermediate Results
LEFT JOIN generates all matching intermediate records (240,000 rows in this example), while NOT EXISTS only needs to check for existence, directly filtering without the need to retain intermediate data.

2. Replace Computation-Intensive Operations
Converts the aggregate computation of GROUP BY + HAVING (which requires temporary table sorting/grouping) into simple hash lookups (the materialized table only needs to be built once).

3. Optimize Data Access Patterns
After materialization, the subquery forms an in-memory hash table. The main query only requires constant-time lookups, significantly reducing CPU and I/O costs.

V. Conclusion

In anti-join scenarios for "finding records without associated entries," NOT EXISTS achieves over 50% performance improvement compared to LEFT JOIN + HAVING COUNT = 0. The key points are:

Eliminate intermediate result inflation (reducing data volume by 60%)
Avoid group aggregation overhead (eliminating temporary table operations)
Leverage the optimization strategy of subquery materialization + hash lookup

Development Guideline: Prioritize the NOT EXISTS pattern for anti-join queries, ensure associated fields are indexed, and verify that the execution plan includes the "Materialized" identifier.

SQLFlash: A New Era of Intelligent Optimization, Effectiveness Soars to 87%

Rebooter.S — Thu, 11 Dec 2025 07:10:37 +0000

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