How AI Powers an Intelligent SQL Assistant for Query Optimization

In the era of deep digital evolution, to meet the growing enterprise database management needs for query optimization, performance diagnostics, and operational efficiency, Yunzhou Observation has developed an AI‑based Intelligent SQL Assistant.

SQL Generation Convert statements of different SQL dialects into standard SQL or target‑database syntax.

SQL Diagnosis Detect potential errors and performance issues in SQL statements and provide optimization suggestions.

SQL Explanation Explain the execution plan of a SQL statement to help users understand the query process.

Architecture Design

The assistant adopts a layered/module design consisting of an Interaction Layer, a Core Engine Layer, and a forthcoming RAG module, enabling efficient processing from user interaction to core functionality.

Interaction Layer Implements non‑blocking APIs using Spring WebFlux, which improves response performance under high concurrency and supports streaming responses.

Core Engine Utilizes Spring AI ChatClient together with PromptTemplate for dynamic prompt construction based on user intent.

2.1 Routing Module (Intent Recognition)

The routing module identifies user intent from input parameters and routes the request to the appropriate processing module; currently it uses simple parameter‑based strong recognition.

<code>private Prompt createSystemPrompt(SQLIntentAssistant nameByIntent, ChatParamRequest chatParamRequest, String schema) {
    String templateKey = switch (nameByIntent) {
        case EXPLAIN -> PromptConstant.SQL_EXPLAIN;
        case DIAGNOSIS -> PromptConstant.SQL_DIAGNOSTIC;
        case GENERATE -> PromptConstant.TEXT_TO_SQL;
    };
    return createPrompt(templateKey, chatParamRequest, schema);
}
</code>

Based on the SQLIntentAssistant enum, different prompt templates are selected, e.g., PromptConstant.SQL_EXPLAIN for explanation, PromptConstant.SQL_DIAGNOSTIC for diagnosis, and PromptConstant.TEXT_TO_SQL for generation.

2.2 SQL Generation Module

2.2.1 Metadata Injection Example

<code>{
    "getYunZhouMetaInfo": {
        "url": "http://xxx.xxx.xx.x/openapi/api/logstore/getSchema",
        "paramsSchema": {"taskId": {"type": "String"}},
        "authorization": "Basic xxx"
    },
    "getCdwMetaInfo": {
        "url": "http://xxx.xxx.xx.x/api/cdw/table",
        "paramsSchema": {"datasourceId": {"type": "Integer"}},
        "authorization": ""
    }
}
</code>

These functions retrieve metadata from different data sources, providing necessary information for SQL generation.

2.2.2 ToT Multi‑Stage Prompt Engineering

<code>// prompts/sql-gen.st
Table structure: {{meta_data}}
User request: {{query}}
Requirements: Follow a tree‑of‑thought process, e.g.,
1. Table structure analysis
2. Syntax requirements
3. Output specifications
</code>

The template guides the AI to generate SQL step‑by‑step, improving accuracy.

2.2.3 Dynamic Context Switching (Prompt Variable Injection)

<code>PromptTemplate promptTemplate = new PromptTemplate(templateKey);
return promptTemplate.create(Map.of(
    "meta_data", schema.replace("\"", ""),
    "query", chatParamRequest.getQuery()
));
</code>

This injects runtime metadata and user queries into the prompt, tailoring the AI response to the current context.

2.3 SQL Explanation Module

<code>public static String SQL_EXPLAIN = """
You are a Trino SQL explanation expert. Analyze the input SQL {{query}} and provide:
1. Statement parsing (SELECT/FROM/JOIN, tables, fields)
2. Execution logic (data flow, join diagram, bottlenecks)
3. Optimization suggestions (indexes, partitioning, caching)
If no SQL is detected, output: ``` No SQL to explain ```
Return the result in Markdown without code fences.
""";
</code>

The module parses the SQL, visualizes the execution flow, identifies performance bottlenecks, and offers optimization advice.

2.4 SQL Diagnosis Module

<code>public static String SQL_DIAGNOSTIC = """
You are a Trino SQL diagnostic expert. Analyze the input SQL {{query}} with priority:
1. Error classification (syntax, missing objects, type conflicts, resource limits)
2. Diagnostic steps (syntax check, sub‑query isolation, DDL verification, execution plan review)
If no issues are found, output: 'No SQL errors or logical defects detected'.
Return results in Markdown without code fences.
""";
</code>

The module classifies errors, follows a stepwise diagnostic process, and provides clear error reasons and solutions.

3 Technical Capability Analysis

3.1 Integrating AI into Traditional Operations

Embedding AI as a first‑class citizen in the operations workflow automates intent recognition, SQL generation, explanation, and diagnosis, dramatically improving efficiency and reducing manual errors.

3.2 Natural‑Language‑to‑SQL Compilation

Non‑technical users can describe data needs in natural language; the assistant compiles these requests into executable SQL, lowering the barrier to data analysis.

3.3 Multi‑Product Platform Embedding

The modular design follows a standard SQL‑assistant integration protocol, allowing easy embedding across various product platforms.

Future Optimizations and Directions

4.1 RAG System Expansion

Incorporating Retrieval‑Augmented Generation with FAQs and technical manuals will enrich the AI’s context, improving accuracy for complex queries.

4.2 Multi‑Dialect Support

Beyond TrinoSQL, the assistant will extend to other dialects such as FlinkSQL, HiveSQL, and PQL, forming a SQL‑BOX suite for diverse data processing frameworks.

4.3 Intelligent Query Answering

Advancing from natural language → SQL → data return will enable fully intelligent data questioning, streamlining analysis and decision‑making.