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

Discussion in 'SQL Server' started by Richard To, Apr 12, 2021.

  1. Richard To

    Richard To Member

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

    [​IMG]

    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

    [​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