Posted on

How to Tune SQL with “IN subquery” for PostgreSQL ?

Below is an example of a SQL statement using an IN subquery. This query retrieves records from the Grade table where the grd_id matches any grd_id in the employee table for rows where emp_id exceeds 3,000,000:

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000)

Here is the query plan; it takes 12.6 seconds to complete.

The query plan shows a Nested Loop joining a sequential scan of the Grade table and an index scan of the Employee table. However, this plan uses each record in the Grade table to trigger an index scan on the Employee table for rows where emp_id > 3,000,000. While the total number of records in the Employee table is around 4 million, fewer than 1 million satisfy the emp_id > 3,000,000 condition.

To address this, I rewrote the SQL by adding a GROUP BY clause in the subquery:

select *
  from grade
 where grd_id in (select b.emp_grade
                    from employee b
                   where b.emp_id > 3000000
                   group by b.emp_grade)

The revised query plan shows the following changes:

  • The GROUP BY clause forces the subquery to execute first.
  • An index scan on the Employee table quickly narrows down the subquery result set.
  • A grouping operation is performed on the emp_grade column.
  • The Grade table is then joined via a Hash Join with the grouped emp_grade subset.

The rewritten SQL achieves 0.54 seconds execution time, demonstrating significantly improved performance.

The rewritten SQL is more than 23 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert for PostgreSQL. On the following screen, there are some SQL alternatives with better performance, but that are not suitable to discuss in this short article. Maybe we can discuss this topic later.

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

Posted on

How to Tune SQL Statement with CASE Expression for PostgreSQL ?

Here the following is a simple SQL statement with a CASE expression syntax.

SELECT *
FROM   employee
WHERE 
       CASE
         WHEN emp_salary< 1000
         THEN ‘low’
         WHEN emp_salary>100000
         THEN ‘high’
       END = :a

The following are the query plans for this SQL statement, which completes in 1.01 seconds with the bind variable ‘:a’  equal to ’low’. The query involves a Full Table Scan of the EMPLOYEE table because the CASE expression cannot utilize the emp_salary index.

We can refactor the CASE expression into the following syntax using multiple OR conditions

select *
  from employee
 where (emp_salary < 1000 and
        ‘low’ = :a or
        ((not (emp_salary < 1000)) or
         emp_salary is null) and
        emp_salary > 100000 and
        ‘high’ = :a)

Rewriting multiple OR conditions with an AND operation (e.g., ‘low’ = :a) can efficiently disable unnecessary data searches on the EMPLOYEE table.

Here is the query plan of the rewritten SQL and the speed is 0.005 seconds. It is around 200 times better than the original syntax. The new query plan shows an Bitmap Index Scan on emp_salary index.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for PostgreSQL automatically, there are other rewrite with even better performance, but it is not suitable to discuss in the short article, maybe I can discuss later in my blog.

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

Posted on

How to Tune SQL Statement with CASE Expression for openGauss ?

openGauss is an open-source relational database management system (RDBMS) that originated from PostgreSQL. It is designed for high performance, high availability, and enterprise-grade features. Initially developed by Huawei, openGauss was later open sourced to the community.

Here is a common problem that developers may encounter: writing a dynamic SQL statement with a CASE expression that hardcodes a = ‘low’ instead of using a bind variable like = :var, as shown below:

SELECT *
FROM   employee
WHERE 
       CASE
         WHEN emp_salary< 1000
         THEN ‘low’
         WHEN emp_salary>100000
         THEN ‘high’
         ELSE ‘Normal’
       END = ‘low’

Below is the query plan for this SQL statement, which takes 2.20 seconds to complete. The query performs a sequential scan (Seq Scan) on the EMPLOYEE table because the CASE expression cannot utilize the emp_salary index.

We can rewrite the CASE expression using the following syntax with multiple OR conditions.

select *
  from employee
 where (emp_salary < 1000 and
        ‘low’ = ‘low’ or
        (not (emp_salary < 1000)) and
        emp_salary > 100000 and
        ‘high’ = ‘low’ or
        (not (emp_salary < 1000 or
              emp_salary > 100000)) and
        ‘Normal’ = ‘low’);

If the emp_salary field is nullable, the SQL query should be written like this:

select *
  from employee
 where (emp_salary < 1000 and
        ‘low’ = ‘low’ or
        ((not (emp_salary < 1000)) or
         emp_salary is null) and
        emp_salary > 100000 and
        ‘high’ = ‘low’ or
        ((not (emp_salary < 1000 or
               emp_salary > 100000)) or
         emp_salary is null) and
        ‘Normal’ = ‘low’)

Here is the query plan for the rewritten SQL, which executes in 0.002 seconds. This is 1100 times faster than the original syntax. The new query plan utilizes a Bitmap Index Scan on the emp_salary index.

This type of rewrite can be achieved automatically using Tosska SQL Tuning Expert Pro for openGauss. There are other rewrites that deliver even better performance, but they are not suitable to discuss in this short article. I may explore them in more detail in a future blog post.

Tosska SQL Tuning Expert Pro (TSEG Pro™) for OpenGauss® – Tosska Technologies Limited

Posted on

How to Tune SQL with “Exists subquery” for openGauss ?

openGauss is an open-source relational database management system (RDBMS) that originated from PostgreSQL. It is designed for high performance, high availability, and enterprise-grade features. Initially developed by Huawei, openGauss was later open sourced to the community.

One common problem that often arises in most database SQL optimizers is the inefficient handling of EXISTS subqueries. Below is an example of a SQL statement with an EXISTS subquery. This query retrieves records from the emp_subsidiary table where the salary, grade_id, and department_id match those in the employee table.

select *
  from emp_subsidiary a
 where exists (select ‘x’
                 from employee b
                where a.emp_salary = b.emp_salary and
                      b.emp_grade = a.emp_grade and
                      a.emp_dept = b.emp_dept)

Here is the query plan; it takes 10.35 seconds to complete.

The query plan shows a Hash Inner Join with a sequential scan of the emp_subsidiary table and a sequential scan of the employee table. The performance of this query plan seems reasonable and cannot be improved without introducing new indexes.

Someone might suggest rewriting the SQL to use an IN statement to see if the query can be improved, as shown below:

select *
  from emp_subsidiary a
 where (a.emp_salary, a.emp_grade, a.emp_dept) in (select b.emp_salary,
                                                          b.emp_grade,
                                                          b.emp_dept
                                                     from employee b)

This rewritten SQL does not generate a new query plan, and no performance improvement is observed. To address the issue, let me further rewrite the original SQL statement into a subquery with an INTERSECT statement. Clearly, this rewritten SQL introduces an additional operation in the subquery, where the employee table intersects with the emp_subsidiary table. This means only the intersected set of records (based on emp_salary, emp_grade, and emp_dept) between the employee and emp_subsidiary tables will be returned. As a result, the result set of the subquery is drastically reduced, significantly lowering the high-cost Hash Aggregate operation.

select *
  from emp_subsidiary a
 where (a.emp_salary, a.emp_grade, a.emp_dept) in (select b.emp_salary,
                                                          b.emp_grade,
                                                          b.emp_dept
                                                     from employee b
                                                   intersect
                                                   select a.emp_salary,
                                                          a.emp_grade,
                                                          a.emp_dept
                                                     from emp_subsidiary a)

The rewritten SQL takes 4 seconds to complete, with the following query plan. The new plan appears slightly more complex compared to the original query plan. However, the cost of the Hashed Aggregate operation is significantly lower than in the original SQL, and the actual execution time reflects this improvement.

This syntax rewrite method is only useful if the overhead introduced by the INTERSECT operation is offset by the significant reduction in the result set of the subquery achieved through the INTERSECT operation.

The rewritten SQL is more than 2 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert for openGauss.

Tosska SQL Tuning Expert Pro (TSEG Pro™) for OpenGauss® – Tosska Technologies Limited

Posted on

How to Tune SQL with “IN subquery” for openGauss ?

openGauss is an open-source relational database management system (RDBMS) that originated from PostgreSQL. It is designed for high performance, high availability, and enterprise-grade features. Initially developed by Huawei, openGauss was later open-sourced to the community. The SQL optimizer in openGauss is based on PostgreSQL but has been significantly enhanced and modified to improve performance, scalability, and support for enterprise workloads. While the exact version of PostgreSQL from which openGauss inherited its initial codebase is not explicitly stated in the official documentation, it is widely believed that openGauss originated from PostgreSQL 9.2.4. As a result, some legacy SQL optimizer issues from older PostgreSQL versions may still persist in the current version of openGauss.

One common problem that often arises in less mature SQL optimizers involves the inefficient handling of IN subqueries. Below is an example of a SQL statement with an IN subquery. This query retrieves records from the employee table where the salary matches the salary in the emp_subsidiary table for rows with the same emp_id.

select *
  from employee a
 where a.emp_salary in (select b.emp_salary
                          from emp_subsidiary b
                         where a.emp_id = b.emp_id)

Here is the query plan; it takes 7.2 seconds to complete.

The query plan shows a sequence scan of the employee table and an index scan of the emp_subsidiary table. However, this query is not suitable for a scenario where the ratio of employee to emp_subsidiary is 30:1. If openGauss had a more robust SQL optimizer, it should include an internal SQL syntax rewrite mechanism to transform the IN statement into a JOIN or EXISTS statement, allowing for the exploration of more efficient query plans.

Let me rewrite the SQL using an EXISTS statement, as shown below:

select *
  from employee a
 where exists (select ‘x’
                 from emp_subsidiary b
                where a.emp_salary = b.emp_salary and
                      a.emp_id = b.emp_id)

This rewritten SQL takes 0.34 seconds to complete and uses a Merge Semi Join in the query plan—a plan that cannot be generated with the original IN syntax. We can see that the cost of the rewritten SQL is significantly lower than that of the original SQL statement. This indicates that the Merge Semi Join plan is not within the plan space explored by openGauss during the SQL optimization stage for the original query

The rewritten SQL is more than 20 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert for openGauss.

Tosska SQL Tuning Expert Pro (TSEG Pro™) for OpenGauss® – Tosska Technologies Limited

Posted on

How to use Query Rewriter Plugin to tune SQL in MySQL database II

The Query Rewriter Plugin in MySQL is a component that allows you to modify incoming SQL queries before execution. It provides the ability to transform, route, filter, or expand queries based on specific requirements.

If you have installed the plugin, the following SQL statements can be used to define your SQL replacement rules and error message handling.

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

The query_rewrite.rewrite_rules table in MySQL stores the rules used by the Query Rewriter Plugin to rewrite SQL queries. The table has two columns:

Pattern – This column represents the pattern or condition that triggers the rewriting of a SQL query. It defines the specific query or query pattern to match.

Replacement – This column specifies the replacement or transformation that should be applied to the matched query or query pattern.

When a SQL query is executed, the Query Rewriter Plugin checks the query_rewrite.rewrite_rules table for matching patterns. If a pattern matches the executed query, the plugin rewrites the query using the corresponding replacement. This allows you to modify the query structure, optimize it, or add custom logic based on specific patterns or conditions.

Here is an example of SQL statement with a hardcoded literal, the SQL takes 2.1 seconds to execute with the following query plan.

SELECT   *
FROM     employee
WHERE    emp_id in (SELECT emp_id id
                    FROM   emp_subsidiary
                    where  emp_dept < ‘D’)
ORDER BY emp_id LIMIT 1;

The following screen displays an alternative SQL statement with significantly improved performance identified by the Tosska DB Ace SQL tuning tool. This optimized SQL query is over 300 times faster than the original SQL due to the injection of JOIN ORDER hints.
Now, we need to deploy this alternative SQL to the database alongside the original SQL. However, a challenge arises with the condition “emp_dept < ‘D'” which does not match the SQL text when using a SQL statement with a different hardcoded literal like “emp_dept < ‘E'”. Therefore, we must utilize Value Placeholders to substitute the hardcoded literals with a Placeholder “?” as shown below.

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

Original_SQL

SELECT   *
FROM     employee
WHERE    emp_id in (SELECT emp_id id
                    FROM   emp_subsidiary
                    where  emp_dept < ?)
ORDER BY emp_id LIMIT 1


Rewrite_SQL

select   /*+ QB_NAME(QB1) JOIN_ORDER(`employee`@QB1, `emp_subsidiary`@QB2) */ *
from     employee
where    emp_id in (select /*+ QB_NAME(QB2) */ emp_id id
                    from   emp_subsidiary
                    where  emp_dept < ?)
order by emp_id limit 1

Tosska DB Ace for MySQL simplifies this process by automating the discovery of an optimized SQL alternative and the deployment of SQL with value placeholder replacements in a fully automated manner.
The Query Rewriter Plugin is powerful and easy to use. The most challenging aspect is finding a replacement SQL for your poorly performing SQL statement. Tosska DB Ace Enterprise for MySQL can assist you in automating this process, from identifying poorly performing SQL statements to rewriting SQL syntax and deploying replacement rules.

Tosska DB Ace Enterprise for MySQL – Tosska Technologies Limited

DBAM Tune Rewriter demo – YouTube
Posted on

How to use Query Rewriter Plugin to tune SQL in MySQL database I

The Query Rewriter Plugin in MySQL is a component that allows you to modify incoming SQL queries before execution. It provides the ability to transform, route, filter, or expand queries based on specific requirements. This plugin operates at the SQL layer and can be utilized for optimizing query performance, enforcing security policies, implementing data partitioning strategies, or adding additional business logic to queries. With the Query Rewriter Plugin, you have the power to customize and shape SQL queries to meet your specific needs, providing flexibility and control over query execution within the MySQL server.

The Query Transformation feature enables you to rewrite or transform the original query into an equivalent or more efficient form. This can be useful for optimizing performance, simplifying complex queries, or enforcing certain query plans.

You must install Query Rewriter Plugin before using this feature, the concept of Query Rewriter is simple, it is a set of predefined SQL statements that is used to replace a certain pattern of SQL statements that is fired from your application programs.

If you have installed the plugin, the following SQL statements can be used to defined your SQL replacement rules and error message handling.

INSERT INTO query_rewrite.rewrite_rules (message, pattern, replacement)
VALUES(Unique_ID, Original_SQL, Rewrite_SQL);

The query_rewrite.rewrite_rules table in MySQL stores the rules used by the Query Rewriter Plugin to rewrite SQL queries. The table has two columns:

Pattern – This column represents the pattern or condition that triggers the rewriting of a SQL query. It defines the specific query or query pattern to match.

Replacement – This column specifies the replacement or transformation that should be applied to the matched query or query pattern.

When a SQL query is executed, the Query Rewriter Plugin checks the query_rewrite.rewrite_rules table for matching patterns. If a pattern matches the executed query, the plugin rewrites the query using the corresponding replacement. This allows you to modify the query structure, optimize it, or add custom logic based on specific patterns or conditions.

I utilize the message column to define a temporary unique id for the SQL replacement rule, so the actual rule id can be extracted with the following SQL.

SELECT id into :SID FROM query_rewrite.rewrite_rules where message=Unique_ID;

When you make changes to the query rewrite rules in the query_rewrite.rewrite_rules table, those changes are not immediately applied. Instead, MySQL caches the rules in memory for better performance. However, if you want to ensure that the updated rules take effect immediately, you can call the query_rewrite.flush_rewrite_rules() function.

CALL query_rewrite.flush_rewrite_rules();

If a load error occurs, the plugin also sets the Rewriter_reload_error status variable to ON and the error message will be stored in the Message column.

SELECT message FROM query_rewrite.rewrite_rules where id=:SID;

Actually, the Query Rewriter Plugin is powerful and easy to use. The most challenging aspect is finding a replacement SQL for your poorly performing SQL statement. Tosska DB Ace Enterprise for MySQL can assist you in automating this process, from identifying poorly performing SQL statements to rewriting SQL syntax and deploying replacement rules.

Tosska DB Ace Enterprise for MySQL – Tosska Technologies Limited

DBAM Tune Rewriter demo – YouTube

Posted on

How to tune SQL in a Database Object with Plan Guide for SQL Server ?

Leveraging Plan Guides in MS SQL Server can prove beneficial for optimizing the performance of a particular query within database objects like stored procedures and trigger applications. This technique allows you to enhance query efficiency without requiring modifications to the application’s source code.

Here are the steps to use Plan Guides to tune third-party applications SQL in MS SQL Server without changing the source code:

  1. Identify the SQL that are causing performance issues in the database object.
  2. Create a Plan Guide that offers an optimized execution plan for the specified query by incorporating query hints to influence the decision-making process of the optimizer.
  3. Test the Plan Guide to ensure that it provides the desired performance improvements and does not cause any unintended side effects.
  4. Deploy the Plan Guide to production and monitor the performance of the application to ensure that the Plan Guide is being used and is providing the desired performance improvements.

Before optimizing an database object’s SQL statement executed by an application program without modifying the source code, it is crucial to understand how the SQL statement matches the one specified in the Plan Guide, which includes whitespace and comments. Additionally, it is important to match the database object that execute the SQL statement.
Below is an example that demonstrates how to optimize a SQL statement from a database object called getRD2. The SQL statement is selected and highlighted by the user.

Creating a plan guide may initially seem complex, but it is a valuable method for improving SQL performance without modifying the source code or lacking the necessary permissions. The most time-consuming part involves finding the best query hint for the SQL statement using the @hints = N’OPTION(query_hint [ ,…n ]) parameter in the sp_create_plan_guide system procedure. If you don’t have extensive knowledge of SQL tuning techniques or enough time for experimentation, a solution is available that simplifies the process. It captures SQL, identifies the SQL source type, automatically optimizes query hints, and facilitates easy deployment of plan guides. The following product automatically identifies a Plan Guide, as depicted in the accompanying screenshot, which can enhance SQL performance by 75.81%.

After identifying the optimal Plan Guide, we can proceed to deploy it along with the stored procedure to the SQL Server database. This deployment will result in improved performance for the stored procedure named getRD2, all without requiring any modifications to the source code of the stored procedure.

You also have the option to manually create the Plan Guide using the system procedure sp_create_plan_guide once you have obtained a suitable hint solution for the SQL statement.

For detailed information, kindly visit our website and take a look at our demo video.

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Object – YouTube

Posted on

How to tune Ad-hoc SQL with Plan Guide for SQL Server ?

Using Plan Guides to tune third-party applications SQL in MS SQL Server can be a useful technique when you need to optimize the performance of a specific query or set of queries generated by the application, without making changes to the application’s source code.

Here are the steps to use Plan Guides to tune third-party applications SQL in MS SQL Server without changing the source code:

  1. Identify the query or queries that are causing performance issues in the application. You can use SQL Server Profiler or Extended Events to capture and analyze the SQL statements generated by the application.
  2. Create a Plan Guide that provides an optimized execution plan for the identified query or queries. This can involve modifying the query text or providing query hints to influence the optimizer’s decisions.
  3. Test the Plan Guide to ensure that it provides the desired performance improvements and does not cause any unintended side effects.
  4. Deploy the Plan Guide to production and monitor the performance of the application to ensure that the Plan Guide is being used and is providing the desired performance improvements.

Before optimizing an ad-hoc SQL statement executed by an application program without modifying the source code, it is crucial to understand how the SQL statement matches the one specified in the Plan Guide, which includes whitespace and comments. Additionally, it is important to match the source of the SQL statement. The following is the system procedure used to create a Plan Guide.

Today, the focus will be on tuning ad-hoc SQL (@type = N’SQL’) using a Plan Guide. There are two types of SQL: standalone SQL (@module_or_batch = NULL) and SQL within a batch text (@module_or_batch = N’batch_text’). For instance, if an application program sends the following SQL, and it is executed independently without any other code, it falls under standalone SQL.
select top 10 * from  employee;
The example below illustrates a batch text that contains one of the SQL statements listed above, which needs optimization by Plan Guide. This SQL statement is located in the middle of the batch text. Since the same SQL statement can originate from a batch text, we must specify the specific batch text by using the variable @module_or_batch = N’batch_text’. Consequently, two Plan Guides must be created for the same SQL statement, one for ad-hoc SQL and one for batch text. To accurately identify the source of an Ad-hoc SQL, it is recommended to use SQL Profiler to capture the SQL statement that requires optimization by Plan Guide.

select count(*) from employee;
select top 10 * from  employee;
where emp_id in (select emp_id id
                             from emp_subsidiary
                             where emp_dept<‘h’)
order by emp_name;

Microsoft SQL Server Management Studio provides a useful tool that assists users in creating a plan guide for a SQL statement without requiring manual execution of the system stored procedure. However, it is crucial to have knowledge about the type of SQL statement being optimized and the meanings of the corresponding parameters that need to be input.

Although the steps to create plan guide may seem complicated for newcomers, they are worthwhile for improving SQL performance without altering the source code or lacking the permission to modify it. However, the most challenging and time-consuming aspect is finding the optimal query hint for the SQL statement (@hints = N’OPTION(query_hint [ ,…n ])). Unless you have an in-depth knowledge of SQL tuning techniques and enough time to experiment, you may require a product that streamlines the process from capturing SQL, identifying SQL source type, automatically tuning query hints, and facilitates easy deployment of Plan Guides.

Tosska DB Ace Enterprise for SQL Server – Tosska Technologies Limited
DBAS Tune SQL PG Standalone – YouTube
DBAS Tune SQL PG Batch – YouTube

Posted on

The Overlooked Significance of Performance Deployment in Software Deployment

Performance deployment is a critical aspect of software deployment that is often undervalued. While it’s important to ensure that software is deployed correctly, it’s equally important to optimize its performance in the production environment. By recognizing the significance of performance deployment during the software deployment process, developers can ensure that their software performs well and meets the needs of its users. Focusing on performance deployment can help prevent performance issues and improve user satisfaction with the software.

The Missing Link of Performance Deployment Between Testing Database and Production Database
Despite extensive pre-deployment testing, there is still a chance of encountering performance problems in specific development environments during software performance deployment. The following issues may arise :

  1. Inability to copy production data to the testing database.
  2. Significant differences in hardware and software configuration between the testing and production databases.
  3. Inability to test software in the production database due to security restrictions.
  4. The utilization of DML SQL statements in the new software that may damage the data integrity of the production database.

It is not unusual for users to face performance issues or encounter application errors following a release of new application code.

Ensuring Performance Deployment with a Pre-Deployment Process
The subsequent instructions present a novel approach for guaranteeing performance reliability while deploying software. The idea is uncomplicated: since it is not feasible to run the new application code on the production database, why not obtain query plans for every SQL statement in the production database? This way, we can assess the performance of each SQL statement in the application code that is intended for deployment on the production database.

Suppose there are 10 SQL statements in the new application code that we need to identify in the testing database. In that case, we need to clear the shared pool and execute the new application in the testing database first to isolate these 10 statements. This process will enable us to capture and analyze the 10 SQL statements and obtain their query plan from the production database. The table below presents various potential outcomes resulting from the query plan comparison.

Observation Possible reasons
Explain Plan error in the production database The SQL statements requiring access to objects not present in the production database.
Query plan changes Significant statistical differences between the testing and production databases, including differences in the database schema. These schema differences may involve missing or new partitions and other changes affecting the database’s structure and organization. Benchmarking the SQL may be necessary due to the potential significant changes in performance.
Unused indexes Some indexes used in the testing database are not used in the production database. Benchmarking the SQL may be necessary due to the potential significant changes in performance.
New used indexes Some indexes used in the production database are not used in the testing database. Benchmarking the SQL may be necessary due to the potential significant changes in performance.
Total cost changes Changes in the overall query plan cost for the 10 SQL statements. If the production database has a larger data volume than the testing database, the cost change will be higher.

DBAO SQL Performance Tracker – YouTube
Tosska DB Ace Enterprise for Oracle – Tosska Technologies Limited