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.

Related Articles & Tools

3 comments for “Using isnull in where clause is expensive in SQL Server

  1. Madhusudan Adhikari
    May 15, 2017 at 6:09 pm

    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

  2. Jan 20, 2017 at 10:08 am

    How about this?
    “Select *
    FROM CusomerMaster
    WHERE @StateId IS NULL OR StateId = @StateId”

  3. Mar 25, 2015 at 8:51 am

    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!

Leave your thoughts...