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.
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
How about this?
“Select *
FROM CusomerMaster
WHERE @StateId IS NULL OR StateId = @StateId”
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!