I’ll explore additional solutions for optimizing SQL queries that are use “NOTIN” subqueries for my recent blog post.
Below is an example SQL statement with a NOT IN subquery. It retrieves records from the grade table where grd_id does not match any emp_grade in the employee table for rows with emp_id greater than 3000:
select *
from grade
where grd_id not in (select b.emp_grade
from employee b
where b.emp_id > 3000)
In my last post, rewriting the original query to use NOT EXISTS achieved the best performance in my database. However, this approach is not universally optimal for all schema designs:
select *
from grade g1
where not (exists (select ‘x’
from employee b
where g1.grd_id = b.emp_grade and
b.emp_id > 3000))
I’ll now list other high-performance solutions:
Solution 1 – Adding a GROUP BY clause prompts the optimizer to sort and hash-aggregate the employee table first.
Solution 2 – Adding COALESCE(b.emp_grade, b.emp_grade) prevents potential index usage on the employee table, forcing a Seq Scan. This alters the join order from the original query’s execution path.
Solution 3 – Replacing the literal 3000 with a subquery (SELECT (3000)) prevents the optimizer from using an index on b.emp_id by obscuring the constant value.
Below is an example of a SQL statement using an NOT IN subquery. This query retrieves records from the Grade table where the grd_id does not match any grd_id in the employee table for rows where emp_id exceeds 3,000:
select *
from grade
where grd_id not in (select b.emp_grade
from employee b
where b.emp_id > 3000)
Here is the query plan; it takes an exceptionally long time 50 minutes and 10 seconds to complete.
The query plan shows a Materialize temp storage of the Seq Scan with Emp_id > 3000 on the Employee table and then filter by Grade table Grd_id. This query plan is bad since there are around 3 million records of employee tables that will be scanned for each one of five thousand rows of Grd_id from Grade table.
To address this, I rewrote the SQL to a NOT (EXISTS …) statement:
select *
from grade g1
where not (exists (select ‘x’
from employee b
where g1.grd_id = b.emp_grade and
b.emp_id > 3000))
The revised query plan shows that a Nest Loop from Grade table with an Index Scan(emps_grade_inx) of Employee table.
The rewritten SQL achieves 0.07 seconds execution time, demonstrating significantly improved performance.
The rewritten SQL is more than 43,000 times faster than the original SQL. This type of optimization can also be achieved using Tosska SQL Tuning Expert Pro 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.
Below is a SQL statement from my recent blog post. A reader asked about the performance impact of adding +0 to a subquery’s select list (e.g., b.emp_grade+0). I was pleased to learn that this +0 solution was first introduced by me roughly 20 years ago to resolve a specific subquery issue. However, this method has since been replaced with COALESCE(b.emp_grade, b.emp_grade) because it avoids data type detection and prevents unexpected errors.
Today, I’d like to explore additional solutions for optimizing SQL queries using IN subqueries.
select *
from grade
where grd_id in (select b.emp_grade
from employee b
where b.emp_id > 3000000)
The following screenshot displays SQL alternatives generated by Tosska SQL Tuning Expert Pro using the Rewrite Only option and SQL Tuning Intelligence 2. I’ll share additional SQL rewrite methods to showcase the full spectrum of possibilities, hoping to inspire readers with the remarkable art of SQL tuning techniques
I won’t discuss the details of those SQL statements, but I’m happy to share the remaining query rewrites and their corresponding execution plans below:
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.
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.
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.
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.
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:
Identify the SQL that are causing performance issues in the database object.
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.
Test the Plan Guide to ensure that it provides the desired performance improvements and does not cause any unintended side effects.
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.
To properly evaluate the impact of a set of SQL statements during a database environment change, it’s crucial to have a thorough understanding of how SQL query performance can be impacted. There are two primary types of performance changes that can occur in SQL queries. The first type, which I refer to as “progressive change,” is typically caused by changes in statistics, such as fluctuations in data volume in relevant tables or index pages. If the statistics change is not significant enough to trigger a new query plan, the query plan will remain the same, and the performance of the SQL query will not be significantly altered compared to the original statistics.
The second type of performance change, which I refer to as “quantum change” occurs when a new query plan is introduced due to a significant change in statistics or schema. This type of change can have a major impact on performance, occasionally resulting in performance disasters.
When making changes to the database environment, it is essential to closely monitor the performance of SQL queries and take appropriate measures to optimize the affected statements. To track critical SQL statements before and after environment changes, there are some general steps you can follow :
Extract the SQL statements along with their query plan and performance statistics from SGA or AWR.
Apply the environment changes to the database, such as creating new indexes, gathering statistics, upgrading the database, or forecasting the performance of a software deployment in the production database.
Obtain the query plan from the changed database environment.
Compare the query plan for each SQL statement to identify any differences.
Look for potential issues such as unused indexes, high-cost SQL changes, etc.
Benchmark the query plan for the modified SQL statements to detect any degraded performance.
If you only have a small number of SQL statements to track in terms of performance before and after environment changes, the above steps can be done manually. However, it can be challenging if you have hundreds of SQL statements to monitor without a tool. Tosska DB Ace for Oracle is equipped with a robust tool that can assist you in tracking the performance differences of SQL statements between two databases.
Here is an example SQL that retrieves data from EMPLOYEE and DEPARTMENT table with the employee’s grade code in the GRADE table.
SELECT emp_id,
emp_name,
dpt_name
FROM employee,
department
WHERE emp_dept = dpt_id
AND emp_grade IN (SELECT grd_id
FROM grade
WHERE grd_min_salary < 200000)
and emp_dept < ‘D’
Here the following is the query plan of this SQL, it takes 8.3 seconds to finish. The query plan shows a Hash Join with GRADE and EMPLOYEE and then hash join to DEPARTMENT. It looks like Oracle gave up any Nested Loops operations after the actual number of rows is returned from the GRADE table in this adaptive plan.
In order to ask Oracle to consider the Nested Loops operations, I added an extra Intersect operation in the subquery to rapidly narrow down the result set of grd_id returned from the GRADE table first.
SELECT emp_id,
emp_name,
dpt_name
FROM employee,
department
WHERE emp_dept = dpt_id
AND emp_grade IN (SELECT grd_id
FROM grade
WHERE grd_min_salary < 200000
INTERSECT SELECT e1.emp_grade
FROM employee e1
WHERE emp_dept < ‘D’)
AND emp_dept < ‘D’
The rewritten SQL generates a query plan that is entirely different from the original query plan, The new plan is using “Nested Loops” from DEPARTMENT to EMPLOYEE as the first steps and then Hash Join to the GRADE table. The new plan now takes 0.81 seconds only.
This kind of rewrite can be achieved by Tosska SQL Tuning Expert Pro for Oracle automatically, it shows that the rewrite is more than 10 times faster than the original SQL.
