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

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