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

Discussion in 'SQL Server' started by Richard To, Apr 7, 2021 at 11:14 AM.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    51
    Likes Received:
    18
    Trophy Points:
    8
    Gender:
    Male
    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.
    [​IMG]
    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.
    [​IMG]
    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

    [​IMG]
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice