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

We have discussed how to tune a CASE expression SQL with hardcoded literals in my last blog:

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

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

If I change the hardcoded literal to a @var, what will be the performance of the last blog’s rewritten SQL?

SELECT *
FROM EMPLOYEE
 WHERE
 CASE
  when emp_id  < 1001000 then ‘Old Employee’ 
  when emp_dept <‘B’     then ‘Old Department’
 ELSE  ‘Normal’
 END =  @var

I use the same method in my last blog to rewrite this SQL into the following multiple OR syntax, but the SQL Server optimizer change back to a full table scan of the EMPLOYEE table. It is because the SQL Server cannot do a good cardinality estimation of the variable of @var.

select *
from  EMPLOYEE
where emp_id < 1005000
     and ‘Old Employee’ = @var
     or not ( emp_id < 1005000 )
     and emp_dept < ‘B’
     and ‘Old Department’ = @var
     or not ( emp_id < 1005000 )
     and not ( emp_dept < ‘B’ )
     and ‘Normal’ = @var

We can rewrite the CASE expression into the following syntax with multiple UNION ALL statements, this syntax is more complicated than the rewrite with multiple OR conditions in my last blog. But it can make SQL Server improve the query plan to be more efficient.

select *
from  EMPLOYEE
where emp_id < (select 1005000)
     and ‘Old Employee’ = @var
union all
select *
from  EMPLOYEE
where ( not ( emp_id < 1005000 )
       and ‘Old Employee’ = @var
     or @var is null )
     and emp_id >= 1005000
     and emp_dept < ‘B’
     and ‘Old Department’ = @var
union all
select *
from  EMPLOYEE
where ( not ( emp_id < 1005000 )
       and ‘Old Employee’ = @var
     or @var is null )
     and ( not ( emp_id >= 1005000
         and emp_dept < ‘B’
         and ‘Old Department’ = @var )
       or @var is null )
     and emp_id >= 1005000
     and emp_dept >= ‘B’
     and ‘Normal’ = @var

Here is the query plan of the rewritten SQL and the speed is 0.448 seconds. It is 5 times better than the original syntax. People may think that there are two table scan operations of EMPLOYEE that will slow down the whole process, but actually, the corresponding filter operations will stop the table scan operations immediately due to the filter conditions ‘Normal’ = @var and ‘Old Department’ = @var will not be satisfied. This kind of query plan cannot be generated by SQL Server’s internal SQL optimizer, it means that you cannot use Hints injection to get this query plan.

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

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

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.

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.

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.

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