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 :
(This is just an 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.