How to Tune SQL Statement with CASE Expression for SQL Server I?

Discussion in 'SQL Server' started by Richard To, Jul 19, 2021.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    61
    Likes Received:
    20
    Trophy Points:
    8
    Gender:
    Male
    Here the following is a simple SQL statement with a CASE expression syntax.

    SELECT *
    FROM EMPLOYEE​
    WHERE
    CASE
    when emp_id < 1001000 then 'Old Employee'
    when emp_dept <'B' then 'Old Department'​
    ELSE 'Normal'​
    END = 'old Employee'

    Here the following are the query plans of this SQL, it takes 2.23 seconds in a cold cache situation, which means data will be cached during the SQL is executing. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_id index or emp_dept index.
    [​IMG]
    We can rewrite the CASE expression into the following syntax with multiple OR conditions.

    select *
    from EMPLOYEE
    where emp_id < 1005000
    and 'Old Employee' = 'Old Employee'​
    or not ( emp_id < 1005000 )
    and emp_dept < 'B'
    and 'Old Department' = 'Old Employee'​
    or not ( emp_id < 1005000 )
    and not ( emp_dept < 'B' )
    and 'Normal' = 'Old Employee'​

    Here is the query plan of the rewritten SQL and the speed is 0.086 seconds. It is 25 times better than the original syntax. The new query plan shows an Index Seek of EMP_ID index.
    [​IMG]
    This SQL rewrite is useful when the CASE expression is equal to a hardcoded literal, but if the literal “ ='Old Employee' ” replaced by a variable “ = :var ”, this rewrite may not be useful, I will discuss it in my next blog.

    This kind of rewrite can be achieved by Tosska SQL Tuning Expert for SQL Server automatically.

    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