CODEWITHSUNDEEP
sql-server-2008
duckmike
duckmike

Reputation: 1036

ISNULL slows down query

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.

Upvotes: 6

Views: 8279

Answers (4)

styx
styx

Reputation: 421

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.

Upvotes: 3

Kinexus
Kinexus

Reputation: 12904

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

Upvotes: 2

JNK
JNK

Reputation: 65157

You can also bypass the functions entirely by using:

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

Upvotes: 8

Rob Allen
Rob Allen

Reputation: 17719

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?

Upvotes: 12

Related Questions