Using isnull in where clause is expensive in SQL Server

Today I had to work on a stored procedure which was taking more than 20 seconds to run. On analyzing the stored procedure I found the cause of the slowness is due to the usage of isnull in where clause. The statement was similar to this :

Example

Select * from CusomerMaster where StateId = isnull(@StateID, [StateId]);

After replacing isnull with ‘Case’  like below, the statement executed faster and the stored procedure ran in few mille seconds.

Select * from CusomerMaster where StateId = (Case @StateID when null then [StateId] else @StateID End);

I know that using ‘case’ in where clause itself is expensive and it’s not a recommended performance suggestion. But in my scenario ‘Case’ is better than isnull. I’m not sure why case is better than isnull in my situation.

Reference

  • About IsNull in MSDN.


3 thoughts on “Using isnull in where clause is expensive in SQL Server”

  1. Hi,
    As you suggested, I changed the isnull condition in where condition to case statement.Performance was increased dramatically.However, no of records returned was different than in isnull case(if map.i_book_map_id or map.h_book_map_id is null in temp table)
    sample query
    ———–
    SELECT h.deal_date h_date,i.deal_date i_date,h.source_deal_header_id source_deal_header_id_h,h.per per_h,i.source_deal_header_id source_deal_header_id_i,i.per per_i,COALESCE(map.fas_book_id,i.fas_book_id,h.fas_book_id,-1) fas_book_id,
    INTO #perfect_match
    FROM #hedge h INNER JOIN #item i ON h.term_start=i.term_start
    AND h.term_end=i.term_end AND h.volume=i.volume AND h.buy_sell=i.buy_sell
    AND h.used=0 AND i.used=0 AND h.no_indx=i.no_indx AND h.no_terms=i.no_terms
    and h.initial_per_ava>=0.01 and i.initial_per_ava>=0.01
    inner join #no_dice_deal nd on i.source_deal_header_id=nd.source_deal_header_id
    inner join (select distinct * from #map_n_curve) map on h.curve_id=map.h_curve_id and i.curve_id=map.i_curve_id
    –and h.book_map_id=isnull(map.h_book_map_id,h.book_map_id)
    –and i.book_map_id=isnull(map.i_book_map_id,i.book_map_id)
    and h.book_map_id=case map.h_book_map_id when null then h.book_map_id else map.h_book_map_id end
    and i.book_map_id=case map.i_book_map_id when null then i.book_map_id else map.i_book_map_id end

    please suggest what can I do in such case

    Reply
  2. This is a very interesting observation. I’d like to add something. Using “CASE WHEN” clause has the same behavior as ISNULL (stops using indexes), however using “CASE @variable WHEN” clause makes use of indexes, as if it wasn’t there. This distinct difference is not to be forgotten. Thank you!

    Reply

Leave your thoughts...

This site uses Akismet to reduce spam. Learn how your comment data is processed.