7 posts tagged with "SQL Optimization"

Introduction​

As Large Language Models (LLMs) become increasingly prevalent in technical domains, assessing their capabilities in specific technical tasks grows more important. This research focuses on evaluating three leading reasoning models—DeepSeek r1, GPT-4o, and Claude 3.7 Sonnet—specifically examining their abilities in SQL comprehension and analysis, particularly in determining SQL query equivalence.

Evaluation Methodology​

We designed a challenging test case: using an original SQL query from the TPC-H benchmark, alongside a rewritten version optimized through the professional SQL optimization tool PawSQL. While these queries exhibit significant syntactic differences, they are semantically equivalent. We tasked the 3 models with analyzing whether these queries are equivalent and evaluated their reasoning processes and conclusions.

Test Case Link：https://www.pawsql.com/statement/1897947325217640449

Test Queries​

Original SQL Query:

select ps.ps_partkey,
      sum(ps.ps_supplycost * ps.ps_availqty) as value
from partsupp as ps, supplier, nation
where ps.ps_suppkey = supplier.s_suppkey
     and supplier.s_nationkey = nation.n_nationkey
     and nation.n_name = 'JAPAN'
group by ps.ps_partkey
having sum(ps.ps_supplycost * ps.ps_availqty)
     > (select sum(ps_supplycost * ps_availqty) * 0.0001
        from partsupp, supplier, nation
        where ps_partkey = ps.ps_partkey
            and ps_suppkey = s_suppkey
            and s_nationkey = n_nationkey
            and n_name = 'JAPAN')
order by value desc

Optimized SQL Query: Scalar Subquery Decorrelation

select ps.ps_partkey,
     sum(ps.ps_supplycost * ps.ps_availqty) as value
from partsupp as ps, supplier, nation, (
    select ps_partkey, sum(ps_supplycost * ps_availqty) * 0.0001 as null_
    from partsupp, supplier, nation
    where ps_suppkey = s_suppkey
      and s_nationkey = n_nationkey
      and n_name = 'JAPAN'
    group by ps_partkey) as SQ
where ps.ps_suppkey = s_suppkey
  and s_nationkey = n_nationkey
  and n_name = 'JAPAN'
  and SQ.ps_partkey = ps.ps_partkey
group by ps.ps_partkey
having sum(ps.ps_supplycost * ps.ps_availqty) > sum(SQ.null_)
order by value desc

Model Analysis Results​

DeepSeek r1 (Reasoning Time: 59 seconds)​

DeepSeek r1 took approximately 59 seconds for reasoning and correctly determined that the two queries are logically equivalent.

Key points in its analysis include:

1. Query Logic Comparison: Distinguished between the first query using a correlated subquery and the second query using an inline view for pre-calculation.
2. Equivalence Verification: Provided detailed analysis of subquery scopes, calculation consistency, and filtering conditions, noting that sum(SQ.null_) in the second query is actually equivalent to using SQ.null_ directly since each ps_partkey is unique within SQ.
3. Performance Differences: Identified that the second query avoids multiple scans through pre-calculation and join optimization, likely resulting in improved execution efficiency. PawSQL's optimization results indeed confirm that overall performance improved from 192 seconds to 0.065 seconds, representing a performance boost of over 1000 times [1].

GPT-4o (Reasoning Time: 26 seconds)​

GPT-4o took 26 seconds for reasoning and also correctly concluded that the two queries are equivalent.

Its analysis focused on:

1. Subquery Approach Differences: Identified that the first SQL uses a correlated subquery while the second SQL places the calculation logic in an inline subquery.
2. Execution Method Differences: Pointed out that despite differences in execution plans, the semantics and final results of both queries are identical.

GPT-4o's analysis was concise and direct, capturing the essence of query optimization: improving performance by pre-calculating aggregate values in the derived table while maintaining result consistency.

Claude 3.7 Sonnet​

Claude 3.7 Sonnet incorrectly concluded that the queries are not equivalent.

Its analysis focused on:

1. Subquery Processing Methods: Correctly identified differences in subquery implementation between the two queries.
2. HAVING Clause Condition Comparison: Mistakenly believed that sum(SQ.null_) in the second query would reaggregate the derived table results, changing the comparison semantics.
3. Modification Suggestions: Proposed modifying the HAVING condition in the second query to directly use SQ.null_ rather than sum(SQ.null_).

Claude's analysis revealed limitations in understanding SQL aggregate function behavior in different contexts.

Evaluation Conclusions​

1. DeepSeek r1 and GPT-4o demonstrated strong SQL semantic comprehension, not only providing correct conclusions but also accurately explaining semantic preservation during optimization.
2. Claude 3.7 Sonnet showed notable limitations in handling complex SQL transformations and subquery optimizations, particularly in understanding interactions between aggregate functions and JOIN operations.
3. Model performance differences likely reflect variations in the quality and quantity of SQL-related content in training data, as well as differences in model understanding of database query execution mechanisms.

Implications​

Despite significant progress in LLMs' SQL understanding and analysis capabilities, this research highlights the irreplaceable nature of professional SQL optimization tools (like PawSQL) in practical database optimization work.

1. Optimization Accuracy and Reliability: Professional tools based on database theory and practice ensure semantic equivalence in query transformations, while LLMs still exhibit uncertainty in understanding complex SQL queries.
2. Systematic and Comprehensive Performance Optimization: Professional tools can optimize based on database engine characteristics and statistics, considering index usage, join order, predicate pushdown, and other multi-dimensional optimizations to generate predictable, consistent optimization results.
3. Stability and Controllability in Production Environments: In production environments, SQL query optimization must consider predictability and consistency, compatibility with existing application systems, and robust handling of edge cases. Professional tools offer higher reliability and controllability in these aspects.
4. Tool Collaboration: Developing methods for LLMs to work collaboratively with professional SQL optimization tools may be an effective approach to improving database optimization efficiency and reliability.

Summary​

Through SQL equivalence analysis tasks, this study reveals capability differences among current top-tier reasoning models when addressing problems in specialized technical domains. While some models demonstrate SQL semantic understanding abilities, the value of professional SQL optimization tools in actual production environments remains irreplaceable. As technology evolves, combining LLMs with professional tools may become the best practice for future database optimization.

The latest version of PawSQL now provides comprehensive support for SQL Server-specific syntax, delivering a more intelligent and efficient SQL optimization experience for database developers and administrators. This article explores the key syntax differences between SQL Server and MySQL, and demonstrates how PawSQL addresses the challenges posed by these differences through enhanced parsing capabilities.

SQL Server vs MySQL Syntax Differences Overview​

Identifier Quotation and Statement Separators​

1. Identifier Quotation​

In SQL Server, identifiers (such as table names and column names) are typically quoted using square brackets [], especially when identifiers contain spaces or special characters. For example:

SELECT [User ID], [First Name] FROM [Users];

In MySQL, identifiers are typically quoted using backticks `:

SELECT `User ID`, `First Name` FROM `Users`;

PawSQL's parser intelligently recognizes identifier quotation methods in both SQL Server and MySQL, ensuring accurate parsing without misinterpretation.

2. Statement Separators​

Additionally, SQL Server's unique GO keyword is used to separate batch commands and can also split multiple SQL statements. For example:

CREATE TABLE TestTable (ID INT)
GO
INSERT INTO TestTable VALUES (1);

MySQL doesn't have a similar keyword; all SQL statements end with a semicolon ;:

CREATE TABLE TestTable (ID INT);
INSERT INTO TestTable VALUES (1);

PawSQL's parser accurately recognizes and processes SQL Server's GO keyword, ensuring correct execution of batch statements.

Object Definition (DDL)​

3. Temporary Table Syntax​

SQL Server supports local and global temporary tables using # and ## prefixes respectively. For example:

CREATE TABLE #TempTable (ID INT);
CREATE TABLE ##GlobalTempTable (ID INT);

In MySQL, temporary tables are created using CREATE TEMPORARY TABLE:

CREATE TEMPORARY TABLE TempTable (ID INT);

PawSQL's parser accurately handles temporary table definitions in both SQL Server and MySQL, ensuring error-free execution.

4. Auto-increment Fields​

In SQL Server, auto-increment fields are defined using the IDENTITY keyword:

CREATE TABLE Users (ID INT IDENTITY(1,1), Name NVARCHAR(50));

MySQL uses AUTO_INCREMENT to define auto-increment fields:

CREATE TABLE Users (ID INT AUTO_INCREMENT, Name VARCHAR(50));

PawSQL's parser easily recognizes the differences between the two and correctly parses SQL Server's IDENTITY keyword.

5. Large Text Fields​

SQL Server uses NVARCHAR(MAX) or VARCHAR(MAX) to store large text data:

CREATE TABLE Articles (ID INT, Content NVARCHAR(MAX));

MySQL uses TEXT or LONGTEXT to store large text:

CREATE TABLE Articles (ID INT, Content LONGTEXT);

PawSQL's parser supports SQL Server's large text field types and can correctly handle similar data.

6. Boolean Types​

SQL Server uses the BIT type to represent boolean values:

CREATE TABLE Logs (ID INT, IsSuccess BIT, Timestamp DATETIME);

MySQL uses TINYINT(1) to represent boolean values:

CREATE TABLE Logs (ID INT, IsSuccess TINYINT(1), Timestamp TIMESTAMP);

PawSQL's parser accurately handles boolean value differences between SQL Server and MySQL.

Queries and Data Operations​

7. Pagination Queries​

SQL Server uses OFFSET and FETCH NEXT to implement pagination:

SELECT * FROM Users ORDER BY ID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

MySQL uses LIMIT and OFFSET for pagination:

SELECT * FROM Users ORDER BY ID LIMIT 10 OFFSET 10;

PawSQL accurately parses both pagination query syntaxes, ensuring smooth execution of paginated queries.

8. TOP and LIMIT​

SQL Server uses the TOP keyword to limit the number of rows in query results:

SELECT TOP 10 * FROM Users ORDER BY ID;

MySQL uses LIMIT to achieve this functionality:

SELECT * FROM Users ORDER BY ID LIMIT 10;

PawSQL's parser correctly recognizes and processes both syntaxes, ensuring accurate query execution.

9. Multi-table Updates​

SQL Server supports using UPDATE statements with JOIN for updating multiple tables:

UPDATE Users
SET Users.Name = Departments.DepartmentName
FROM Users
INNER JOIN Departments ON Users.DepartmentID = Departments.ID;

MySQL also supports JOIN updates, but with slightly different syntax:

UPDATE Users
INNER JOIN Departments ON Users.DepartmentID = Departments.ID
SET Users.Name = Departments.DepartmentName;

PawSQL's parser recognizes the differences between both approaches, ensuring correct execution of multi-table update statements.

10. CROSS APPLY and OUTER APPLY​

SQL Server supports CROSS APPLY and OUTER APPLY, which can be used to dynamically join table-valued functions or subqueries:

SELECT u.ID, o.OrderID
FROM Users u
OUTER APPLY (SELECT TOP 1 * FROM Orders WHERE UserID = u.ID) o;

MySQL doesn't support APPLY; similar functionality can be achieved through subqueries or JOIN:

SELECT u.ID, o.OrderID
FROM Users u
LEFT JOIN (SELECT * FROM Orders WHERE UserID = u.ID LIMIT 1) o ON TRUE;

PawSQL recognizes SQL Server's CROSS APPLY and OUTER APPLY and parses them correctly.

11. Query Hints​

SQL Server uses the OPTION clause to add query hints:

SELECT * FROM Users WITH (NOLOCK) OPTION (RECOMPILE);

MySQL uses /*+ ... */ syntax:

SELECT /*+ MAX_EXECUTION_TIME(1000) */ * FROM Users;

PawSQL's parser recognizes SQL Server's query hint syntax and processes it correctly during parsing.

Locking and Transactions​

12. Locking Syntax​

SQL Server uses WITH (ROWLOCK) to specify row-level locking:

SELECT * FROM Users WITH (ROWLOCK);

MySQL uses FOR UPDATE to implement locking:

SELECT * FROM Users FOR UPDATE;

PawSQL's parser supports SQL Server's locking syntax and ensures accurate parsing.

13. Execution Plan Retrieval​

SQL Server uses SET SHOWPLAN_XML ON or EXPLAIN to retrieve execution plans:

SET SHOWPLAN_XML ON;
SELECT * FROM Users;

MySQL uses EXPLAIN:

EXPLAIN SELECT * FROM Users;

PawSQL recognizes SQL Server's execution plan retrieval syntax, ensuring users can correctly analyze query performance.

Summary​

PawSQL's SQL parser helps developers work seamlessly across platforms through comprehensive support for SQL Server-specific syntax. Whether dealing with identifier quotation, batch commands, temporary table definitions, auto-increment fields, pagination queries, or large text fields, PawSQL ensures accurate parsing and efficient execution of SQL statements. Through enhanced parsing capabilities, PawSQL makes SQL development and optimization more intelligent and convenient, significantly improving developer productivity.

OceanBase database has been widely adopted in finance, e-commerce, and government sectors due to its high availability, strong consistency, and high performance. As business scale expands and data volume surges, optimizing OceanBase database query performance becomes increasingly important. PawSQL provides comprehensive performance optimization support for OceanBase database, helping users fully unleash OceanBase's performance potential.

I. Core Optimization Technologies​

PawSQL introduces two specialized capabilities for OceanBase database:

1. Deep OceanBase SQL Syntax Support

• Dual-mode compatibility: Full support for MySQL/Oracle syntax systems
• Complete parsing of OceanBase-specific DDL syntax:

  CREATE TABLE nation_d (
      n_nationkey INTEGER NOT NULL PRIMARY KEY,
      n_name CHAR(25) NOT NULL,
      n_regionkey INTEGER NOT NULL,
      n_comment VARCHAR(152)
  ) duplicate_scope = cluster/none;
  
  CREATE TABLE part (
      p_partkey int NOT NULL PRIMARY KEY,
      p_name VARCHAR(55) NOT NULL,
      p_mfgr CHAR(25) NOT NULL,
      p_brand CHAR(10) NOT NULL,
      p_type VARCHAR(25) NOT NULL,
      p_size NUMBER NOT NULL,
      p_container CHAR(10) NOT NULL,
      p_retailprice NUMBER(15,2) NOT NULL,
      p_comment VARCHAR(23)
  ) PARTITION BY HASH(p_partkey) PARTITIONS 16;
  

2. Rule System Upgrade

• Three new distributed design standards:
  • Avoid table join fields that are not distribution keys
  • DML in distributed databases should avoid table joins
  • DML operations in distributed databases lack sharding field equality conditions
• Three new distribution key design standards:
  • Local tables are not recommended in distributed databases
  • Multiple fields are not recommended for distribution keys
  • Distribution keys should use highly distinctive fields
• Two new distribution strategy design standards:
  • Replicated distribution is not recommended for large tables
  • Hash distribution is recommended for distribution method

II. Product Matrix of SQL Lifecycle​

2.1 Development and Testing Phase: Intelligent SQL Optimization​

PawSQL Optimization Platform is a one-stop online SQL optimization tool for application developers and testers. It integrates industry best practices for relational database query optimization, helping application developers and database administrators solve SQL performance issues through query rewriting optimization and intelligent index recommendations. PawSQL Optimization Platform has completed integration with common IDEs, allowing developers to perform SQL optimization without leaving their development environment.

The PawSQL Optimization Platform is an online SQL optimization tool for developers and DBAs, incorporating industry-leading query optimization technologies, including:

• Intelligent Query Rewriting: Automatically optimizes inefficient SQL statements
• Index Recommendation Engine: Precisely recommends optimal index combinations
• Distributed Optimization Strategies: Provides specialized optimization suggestions for OceanBase's distributed characteristics

2.2 Integration Phase: Intelligent SQL Review​

PawSQL Review Platform, with its leading core technologies such as self-developed SQL parser, syntax tree-based rule matching, and context information updates, provides comprehensive and precise (accuracy over 95%) intelligent SQL review capabilities for SQL quality management teams. It conducts comprehensive checks from multiple dimensions including syntax specifications, performance efficiency, and security, providing targeted optimization suggestions to help enterprises improve SQL performance and application efficiency.

For OceanBase database's distributed characteristics, PawSQL provides specialized distributed query optimization suggestions, with applicable rules exceeding 190.

2.3 Operating Phase: Performance Patroller​

PawSQL Database Performance Patroller automatically captures slow queries generated in the database periodically and provides SQL optimization suggestions, including automatic SQL rewriting, intelligent index recommendations, and existing redundant index analysis. It automatically inspects database objects periodically, identifying potential performance, security, and maintainability issues, and provides optimization suggestions.

III. Summary​

PawSQL for OceanBase database provides you with a one-stop performance optimization solution. From daily query optimization to complex distributed scenario handling, PawSQL can help you tackle challenges with ease. 🚀 Experience PawSQL now and unlock OceanBase database's performance potential!

💡 Introduction​

PawSQL for Jetbrains (PawSQL Client) is a powerful Jetbrains plugin that seamlessly integrates PawSQL engine's intelligent SQL optimization capabilities into your development environment. It supports smart index recommendations, query rewriting, and automated performance validation. Working in conjunction with PawSQL Cloud (https://pawsql.com) or enterprise private deployment of the PawSQL optimization platform, it provides developers with one-click SQL performance optimization capabilities.

🎯 One-Click SQL Optimization in IDEA/DataGrip​

Select the SQL text to be optimized, right-click, and choose which workspace to perform SQL optimization

✨ Core Features​

• 🚀 One-Click Optimization - Intelligently analyze and optimize SQL query performance
• 📊 Index Optimization - Smart recommendations for optimal index combinations, comprehensively improving query efficiency
• 🔄 Query Rewriting - Automatically generate equivalent SQL statements with better performance
• 📈 Performance Validation - Precisely quantify optimization effects, providing detailed performance improvement data

🔧 Installation Guide​

Install from Jetbrains Marketplace​

1. Launch Jetbrains IDE tool (such as IDEA/DataGrip)
2. Search for "PawSQL Client" in Plugins and install

Install from Disk​

1. Download this plugin from Jetbrains Marketplace

2. Install it from Disk

⚙️ Initial Configuration​

1. Fill in the following information in the configuration interface:

• PawSQL Server address (e.g., https://pawsql.com)
• Account information
• Password

2. Click save to complete configuration

🌐 About PawSQL​

PawSQL focuses on database performance optimization automation and intelligence, providing solutions covering the entire process of SQL development, testing, and operations. It widely supports various mainstream commercial and open-source databases including KingbaseES, offering developers and enterprises a one-stop innovative SQL optimization solution. It effectively solves database SQL performance and quality issues, improves database system stability, application performance, and infrastructure utilization, saving enterprises significant maintenance costs and time investment.

Introduction​

After automatically optimizing TPC-H benchmark Query 9, PawSQL improved its performance by 1195.14%. This article will explore PawSQL's effective strategies and actual results for SQL performance optimization based on its optimization process. By comparing the query structure, index design, and execution plans before and after optimization, we will explain how to achieve significant performance improvements.

This case can be viewed online at: https://pawsql.com/statement/1837384704875499522

TPC-H Query 9 Introduction: The 9th query of TPC-H calculates the total profit of all ordered parts by country and year. Its query characteristics include:

• Grouping, sorting, and aggregation operations
• Multiple table joins and subqueries
• Uses a full-match LIKE operator

1️⃣ Query Rewriting Optimization​

Original Query:

SELECT profit.nation, profit.o_year, SUM(profit.amount) AS sum_profit
FROM (
    SELECT nation.n_name AS nation, EXTRACT(YEAR FROM orders.o_orderdate) AS o_year,
           lineitem.l_extendedprice * (1 - lineitem.l_discount) - partsupp.ps_supplycost * lineitem.l_quantity AS amount
    FROM part, supplier, lineitem, partsupp, orders, nation
    WHERE supplier.s_suppkey = lineitem.l_suppkey
      AND partsupp.ps_suppkey = lineitem.l_suppkey
      AND partsupp.ps_partkey = lineitem.l_partkey
      AND part.p_partkey = lineitem.l_partkey
      AND orders.o_orderkey = lineitem.l_orderkey
      AND supplier.s_nationkey = nation.n_nationkey
      AND part.p_name LIKE '%dim%'
) AS profit
GROUP BY profit.nation, profit.o_year
ORDER BY profit.nation, profit.o_year DESC;

Optimized Query:

SELECT profit_nation.n_name AS nation, EXTRACT(YEAR FROM profit_orders.o_orderdate) AS o_year,
       SUM(profit_lineitem.l_extendedprice * (1 - profit_lineitem.l_discount) - profit_partsupp.ps_supplycost * profit_lineitem.l_quantity) AS sum_profit
FROM part AS profit_part, supplier AS profit_supplier,
     lineitem AS profit_lineitem, partsupp AS profit_partsupp,
     orders AS profit_orders, nation AS profit_nation
WHERE profit_supplier.s_suppkey = profit_lineitem.l_suppkey
  AND profit_partsupp.ps_suppkey = profit_lineitem.l_suppkey
  AND profit_partsupp.ps_partkey = profit_lineitem.l_partkey
  AND profit_part.p_partkey = profit_lineitem.l_partkey
  AND profit_orders.o_orderkey = profit_lineitem.l_orderkey
  AND profit_supplier.s_nationkey = profit_nation.n_nationkey
  AND profit_part.p_name LIKE '%dim%'
GROUP BY profit_nation.n_name, o_year
ORDER BY profit_nation.n_name, o_year DESC;

Query Rewriting Key Points:

• Eliminate subquery: Lift the subquery logic to the main query, simplifying the structure.
• Table alias renaming: Improve query readability and maintainability.
• Preserve calculation logic: Ensure that the business logic of the query remains unchanged.

2️⃣ 🔍 Index Optimization Strategy​

PawSQL's proposed index optimization scheme:

CREATE INDEX PAWSQL_IDX0485218972 ON tpch.lineitem(L_PARTKEY, L_SUPPKEY, L_SHIPDATE);
CREATE INDEX PAWSQL_IDX0214365528 ON tpch.supplier(S_NATIONKEY, S_SUPPKEY, S_NAME, S_ADDRESS);
CREATE INDEX PAWSQL_IDX0327029402 ON tpch.part(P_NAME);

Index Optimization Analysis:

• lineitem table index: Targets join conditions and date filtering fields to reduce I/O operations.
• supplier table index: Covers join and query fields, avoiding table lookups and improving retrieval efficiency.
• part table index: Optimizes fuzzy matching of p_name using the LIKE operator.

3️⃣ Execution Plan Comparison​

Execution Plan Before Optimization:

• Nested Loop Joins: Extensively used Nested loop joins, with low efficiency.
• Full Table Scan: Performed full table scan on lineitem table, involving 60,175 rows.
• Reliance on Primary Key Index: Primarily used primary key index for table joins.

Execution Plan After Optimization:

• Join Order Optimization: Still uses nested loop, but with optimized join order.
• Index Scan: Utilizes the newly created PAWSQL_IDX0327029402 index, reducing part table scan from 60,175 to 114 rows.
• Precise lineitem Table Lookup: Avoids full table scan, uses PAWSQL_IDX0485218972 for precise matching, further reducing processed rows.

Ultimately, the total number of processed rows decreased from 60,175 to 3,513.

4️⃣ Performance Improvement​

• Execution Time: Reduced from 260 milliseconds to 20 milliseconds.
• Performance Improvement: Achieved a 1195.14% improvement.
• Main Factors: Index optimization significantly improved data access methods, reducing the number of processed rows.

🌐 About PawSQL​

PawSQL is dedicated to automatic and intelligent database performance optimization. The products provided by PawSQL include:

• PawSQL Cloud, an online automated SQL optimization tool that supports SQL auditing, intelligent query rewriting, cost-based index recommendations, suitable for database administrators and data application developers.
• PawSQL Advisor, an IntelliJ plugin that is suitable for data application developers and can be installed via the IDEA/DataGrip marketplace by searching for "PawSQL Advisor" by name.

Contact Us​

Email: service@pawsql.com

Website: https://www.pawsql.com