6

We have a table that has either NULL or "Accepted" as values. My query returns about 250 rows.

If I add a where condition of -

AND Description = 'Accepted'

my 250 rows return in 2 seconds.

However, if I add a where condition of -

ISNULL(Description, '') = 'Accepted'

my 250 rows return in 47 seconds.

Has anyone encountered performance issues with using the ISNULL function? Unfortunately I am programatically limited to having to use ISNULL at this point.

duckmike
  • 1,006
  • 4
  • 16
  • 39

4 Answers4

12

When you include a field inside of a function, it changes how the optimizer has to run and forces it to ignore indexes.

see here: What makes a SQL statement sargable?

Community
  • 1
  • 1
Rob Allen
  • 17,381
  • 5
  • 52
  • 70
8

You can also bypass the functions entirely by using:

WHERE (Description = 'Accepted' OR Description IS NULL)

JNK
  • 63,321
  • 15
  • 122
  • 138
3

Using

ISNULL(Description, '') = 'Accepted'

in your where condition doesnt make any sense in this case. If the description is null, the original where clause of

AND Description = 'Accepted'

will still suffice. You are basically comparing '' with 'Accepted' in every row where the description is null.

Please elaborate on what you are trying to accomplish with the query, i think you might be going in the wrong direction.

styx
  • 421
  • 8
  • 15
  • +1 pointing out the bleeding obvious: both evaluate to false/unknown when NULL which is effectively the same. I'd probably have overlooked this too. – gbn Oct 24 '11 at 15:29
2

If you are trying to use this in the WHERE condition, use IS NULL, not ISNULL

SELECT field FROM table WHERE description is null

or the opposite

SELECT field FROM table WHERE NOT description is null

ChrisBint
  • 12,773
  • 6
  • 40
  • 62