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

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

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    61
    Likes Received:
    20
    Trophy Points:
    8
    Gender:
    Male
    In my last article How to Tune SQL Statement with EXISTS Subquery for SQL Server I? | Go4Expert
    that a SQL statement with an Exists subquery was improved 90 times by the following rewrite.

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

    Query Plan:
    [​IMG]

    Rewritten SQL syntax:

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

    Query Plan:
    [​IMG]

    Syntax Rewrite Solution
    Syntax rewrite technique to improve SQL statements are commonly used by DBA or developers especially for Oracle or MySQL databases, but syntax rewrite is not easy to be applied by users who are using MS SQL Server or IBM Db2 LUW. The reason is that MS SQL Server and IBM Db2 LUW have a strong internal rewrite engine in their SQL optimizer. The internal SQL rewrite engine will try to rewrite a SQL syntax to their internal canonical syntax. It means that no matter how you rewrite your SQL statement, MS SQL Server and IBM Db2 LUW will try to rewrite the SQL back to their internal presumed good syntax, so it is difficult to tune a SQL if the so-called presumed good syntax is not good, since users are not easy to influence database SQL optimizer to generate a better query plan by simple SQL syntax rewrite.

    Query Hints Injection Solution
    To solve this problem, SQL Server provides Query Hints feature for users to help its SQL optimizer generate a better query plan. It is not like the SQL syntax rewrite method, experienced developers may tell what the final query plan will be for a rewritten syntax, Query Hints is a pinpoint solution that a query hint injection is normally applied to the specific step of the entire query plan, but a change to a plan step will incur domino effect to other plan steps in the entire query plan since MS SQL Server must adjust other plan steps to achieve what the user’s expectation for the query hint in the SQL statement. So, the final query plan is not easy to predict by users, especially for complex SQL statements.

    The following SQL with Hints injection generated by Tosska SQL Tuning Expert is around 4 times better than the original SQL and takes 0.639 seconds.

    select *
    from DEPARTMENT
    where exists ( select 'x'
    from employee
    where emp_id > 2700000
    and emp_dept = DPT_ID) OPTION(LOOP JOIN,HASH GROUP)​

    [​IMG]
    There is an even better SQL with Hints injected, it is around 50 times better than original SQL and takes 0.055 seconds. This query plan is pretty close to the rewrite tuning in my last article.

    select *
    from DEPARTMENT
    where exists ( select 'x'
    from employee WITH(INDEX(EMPS_DPT_INX))
    where emp_id > 2700000
    and emp_dept = DPT_ID)​
    [​IMG]

    Syntax Rewrite plus Hints Injection Solution

    For some SQL statements, a separate syntax rewrite method or a hints injection method may not be able to solve a complex SQL performance problem individually, some people may think that will it be possible if we rewrite a SQL and apply hints at the same time to improve a SQL statement? Yes, it is possible in the Tosska SQL Tuning Expert A.I. engine, this technology can solve more SQL performance problems by a computer algorithm ever before. I will discuss this technology later in my blog.

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

    The following screen show Tosska SQL Tuning Expert can generate 178 distinguished query plans after investigated 300 SQL Hints injection, it is far out of what a human expert can achieve within 10 minutes. MS SQL Server is the most sensitive to Query Hints Injection database in the market, SQL Server query hints are normally able to influence SQL optimizer to generate a specific query plan, so the SQL tuning for MS SQL Server is far more challenging than other databases.
    [​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