SQL Performance Tip #1: Avoid Case or isnull function from where clause

Introduction

I have performance issue with our query and if a query is not designed carefully, the performance will see the big difference.

The query having performance has two issues; 1. case statement from “where” clause to implement dynamic query and 2. null data handling.

Original Query

select * from tablename
where CASE WHEN @includeICL=1 THEN 1 ELSE isnull(gbkmut.debnr,0) END not in (100989,100990,100991,100992)

When this query was run, it took 4 secs to complete this query no matter what the value of @includeICL.

Removing case statement

If you try to search about dynamic query performance issue, you will see lots of recommendation. In general, my recommendation is “Don’t use case statement for conditional filter”. There are couple of good articles how to replace case statement with boolean operation.

In this case, the case statement can be replace with following statement
Where
(@includeICL=1 or isnull(gbkmut.debnr,0) not in (100989,100990,100991,100992))

Good thing for this query is that if @includeICL is 1, the query speed will be really fast because this result is always true.

The speed when @includeICL was 1 was dropped from 4 sec to 0.5 sec because there are no needs to run “not in” statement. But when @includeICL was 0, the speed still slow because of this statement

Removing isnull function

isnull function is really performance killer and the best practice is to set with blank string or default value.

To improve this speed, it was really tough journey and finally find out the solution.

First approach was;
Where
(@includeICL=1 or gbkmut.debnr not in (100989,100990,100991,100992)) or gbkmut.debnr is null

The reason to use isnull function is to include null data for the calculation. With this approach, the actual time was much longer than original query.

After testing and testing, the query was changed to
Where
(@includeICL=1 or gbkmut.debnr is null or gbkmut.debnr not in (100989,100990,100991,100992))

After modifying this rule, the time was reduced to less than 1sec. The entire query was calculating financial KPI and the original query took more than 20 secs to calculate them. After modifying with this way, the result came up less than 4 sec. HUGE saving on the query time.

Conclusion

I’ve been wrote the query without considering any performance penalty, but during writing query for the financial number from the ERP system, there was huge penalties from case statement and isnull function from the where clause. If you can apply boolean operation properly, the speed will be much faster than you think.

Written with StackEdit.