How to Tune SQL Statement with EXISTS Subquery for SQL Server I ?

sql server tuning tools

The following is an example that shows a SQL statement with an Exists subquery. The SQL retrieves records from the DEPARTMENT table that DPT_ID is found in emp_dept of employee table with emp_id > 2700000.

SELECT *
FROM DEPARTMENT
where exists (select ‘x’
         from employee
         where emp_id > 2700000
         and emp_dept=DPT_ID)

Here the following is the query plan in the Tosska proprietary tree format, it takes 2.23 seconds to finish.

The query plan shows two Hash Match from [EMPLOYEE].[EMPLOYEE_PK] to [EMPLOYEE].[EMPS_DPT_INX] and then Merge Join to a sorted [DEPARTMENT] table. This query plan looks reasonable, but the number of records scan from [EMPLOYEE] is too expensive at the first stage, can we use the small [DEPARTMENT] table to scan back the [EMPLOYEE] table to improve the SQL.

Let me rewrite the EXISTS subquery into an IN subquery in the following, but the query plan is not changed as expected.

select  *
from DEPARTMENT
where  DPT_ID in (select   emp_dept
         from     employee
         where  emp_id > 2700000)

I further rewrite the SQL and add the dummy function “isnull(emp_dept,emp_dept)” in the select list, but it cannot stop the operation of Hash Match to [EMPLOYEE].[EMPS_DPT_INX].

select  *
from DEPARTMENT
where  DPT_ID in (select    isnull(emp_dept,emp_dept)
         from      employee
         where   emp_id > 2700000)

To further enforce the restriction for stoping the operation “Hash Match to [EMPLOYEE].[EMPS_DPT_INX]”, I try to add a dummy “group by emp_dept” operation in the subquery.

select  *
from DEPARTMENT
where  DPT_ID in (select    isnull(emp_dept,emp_dept)
         from      employee
         where   emp_id > 2700000
         group by emp_dept)

Here the following is the query plan after the final rewrite, SQL server first uses a Table Scan of [DEPARTMENT] table and Nested Loop of “EMPS_DPT_INX index seek to RID Lookup of [EMPLOYEE]” with the Top 1 operation, so each record from [DEPARTMENT] table will match at most one record from [EMPLOYEE] only. The speed now is 0.024 seconds and is much faster than the original SQL.

Although the steps to the final rewrite is a little bit complicated, this kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 90 times faster than the original SQL.  

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited

How to Tune SQL Statement with IN Operator with an Expression List for SQL Server?

sql performance tuning

The following is an example shows a SQL statement with an IN List expression. The SQL retrieves records from EMPLOYEE table that EMP_DEPT should match any value in a list of values.

select EMP_ID
from EMPLOYEE
WHERE EMP_DEPT IN (‘AAD’,‘COM’,‘AAA’)
AND EMP_SALARY<10000000

Here the following are the query plans in the Tosska proprietary tree format, it takes 2.4 seconds to finish.

The query plan shows three Hash Match with EMPLOYEE’s indexes. For indexes EMPLOYEE_PK and emps_salary_inx are processing with EstimateRows up to 3000000, it seems too expensive since this condition EMP_DEPT IN (‘AAD’,’COM’,’AAA’) should rapidly trim down the return records. Let me rewrite the IN list into multiple UNION conditions in the following:

select  EMP_ID
from     EMPLOYEE E1
where  exists ( select ‘x’
                 where  E1.EMP_DEPT = ‘AAD’
                 union
                 select ‘x’
                 where  E1.EMP_DEPT = ‘COM’
                 union
                 select ‘x’
                 where  E1.EMP_DEPT = ‘AAA’)
      and EMP_SALARY < 10000000

This rewrite can force the IN list operation to be processed first before the condition EMP_SALARY < 10000000 takes place. Here the following is the query plan after rewrite, SQL server now can utilize Merge Join of 3 Nested Loop of “EMPS_DPT_INX index seek to RID Lookup of employee”. The speed now is 0.191 seconds and is much faster than the original SQL.

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for SQL Server automatically, it shows that the rewrite is more than 12 times faster than the original SQL.  

Tosska SQL Tuning Expert (TSES™) for SQL Server® – Tosska Technologies Limited