Getting Rid of ISNULL

Question

Getting Rid of ISNULL

chandan_jha18

SSChampion

Points: 11330
More actions
May 15, 2012 at 8:20 am

#261349

Hi,
I had a procedure where there was a condition in where clause:
where ISNULL(d.[RejectReason], 0) IN (240,241,300,301)
The column RejectReason is int,null.
After going through several posts in this forum, I started looking the porcedure of this function in where clause and found that this was causing an index scan.
So I re-wrote this as:
where d.RejectReason in(240,241,300,301)
because when we compare null values with 240 or 241 it will always be false, so why bother about them. Why not directly find values mentioned on right hand side.
PS: I did not change any ANSI_NULLS setting on the server. I am on 2008 SP1.
Please suggest if my logic is correct and if there could be any pitfalls in future.
Thanks
Chandan

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

chandan_jha18 (5/15/2012)
Hi,
I had a procedure where there was a condition in where clause:
where ISNULL(d.[RejectReason], 0) IN (240,241,300,301)
The column RejectReason is int,null.
After going through several posts in this forum, I started looking the porcedure of this function in where clause and found that this was causing an index scan.
So I re-wrote this as:
where d.RejectReason in(240,241,300,301)
because when we compare null values with 240 or 241 it will always be false, so why bother about them. Why not directly find values mentioned on right hand side.
PS: I did not change any ANSI_NULLS setting on the server. I am on 2008 SP1.
Please suggest if my logic is correct and if there could be any pitfalls in future.
Thanks
Chandan

Looking at the how isnull is being used, to change null to 0, removing the isnull function is the correct step.

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

Lynn Pettis (5/15/2012)
Looking at the how isnull is being used, to change null to 0, removing the isnull function is the correct step.

+1 yes this looks correct assuming we are seeing the whole thing. Not only is it easier to read, it will perform a lot better too since it is now a sargable predicate.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

GSquared SSC Guru Points: 260824 More actions · Answer 3

Definitely the correct handling in this case. Good work.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 4

chandan_jha18 (5/15/2012)
Hi,
I had a procedure where there was a condition in where clause:
where ISNULL(d.[RejectReason], 0) IN (240,241,300,301)
The column RejectReason is int,null.
After going through several posts in this forum, I started looking the porcedure of this function in where clause and found that this was causing an index scan.
So I re-wrote this as:
where d.RejectReason in(240,241,300,301)
because when we compare null values with 240 or 241 it will always be false, so why bother about them. Why not directly find values mentioned on right hand side.
PS: I did not change any ANSI_NULLS setting on the server. I am on 2008 SP1.
Please suggest if my logic is correct and if there could be any pitfalls in future.
Thanks
Chandan

After you changed it to remove the ISNULL, did you see a change in the query plan?

It's possible that the query optimizer might chose an index scan anyway. especially if the cardinality of the RejectReason column is low.

I would make the change in any case, since the ISNULL serves no useful purpose and at least adds extra overhead.

chandan_jha18 SSChampion Points: 11330 More actions · Answer 5

Michael Valentine Jones (5/15/2012)
chandan_jha18 (5/15/2012)
Hi,
I had a procedure where there was a condition in where clause:
where ISNULL(d.[RejectReason], 0) IN (240,241,300,301)
The column RejectReason is int,null.
After going through several posts in this forum, I started looking the porcedure of this function in where clause and found that this was causing an index scan.
So I re-wrote this as:
where d.RejectReason in(240,241,300,301)
because when we compare null values with 240 or 241 it will always be false, so why bother about them. Why not directly find values mentioned on right hand side.
PS: I did not change any ANSI_NULLS setting on the server. I am on 2008 SP1.
Please suggest if my logic is correct and if there could be any pitfalls in future.
Thanks
Chandan
After you changed it to remove the ISNULL, did you see a change in the query plan?
It's possible that the query optimizer might chose an index scan anyway. especially if the cardinality of the RejectReason column is low.
I would make the change in any case, since the ISNULL serves no useful purpose and at least adds extra overhead.

Execution plan looked same initially because index scan was there in both places. But when I created an indexe on this column, I ran both queries separately. The original query with isnull function was still doing scans

whereas the query modified as above approach did an index seek and became more efficient with very very less logical reads.:-P

I hope this helps someone!

Thanks

Chandan

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 6

chandan_jha18 (5/15/2012)
Please suggest if my logic is correct and if there could be any pitfalls in future.

I agree with the others and your own findings. It's a tried an true method because a NULL isn't going to match anyway.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)