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
