Performance issues with isnull

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Performance issues with isnull

Author

Topic

paulwfromtheden
Starting Member

9 Posts

Posted - 2010-08-10 : 06:03:57

Hi,
I have an optional parameter that is passed into a stored procedure (@umr_id) and I am finding that in a 'where' clause if I try to use isnull or coalesce in the usual fashion, that the performance goes through the floor. If I replace the isnull/coalesce with a nasty case statement (below), it runs fine.
This seems most strange as to my knowledge they three different constructs should achieve the same result in the same amount of time??
Has anyone else had issues of this nature?
I have referred the execution plan to our DBA's before anyone shouts, but any feedback would be most welcome.
Paul

and pl.policylinkid =
--coalesce(@umr_id,pl.policylinkid)
--isnull(@umr_id,pl.policylinkid)
case when @umr_id is null
then pl.policylinkid
else @umr_id
end

Sachin.Nand

2937 Posts

Posted - 2010-08-10 : 06:16:33

What happens if you use something like this
where (pl.policylinkid =@umr_id or @umr_id is null)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

paulwfromtheden
Starting Member

9 Posts

Posted - 2010-08-10 : 06:30:06

quote:
Originally posted by Idera

What happens if you use something like this
where (pl.policylinkid =@umr_id or @umr_id is null)

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

Thanks, it performs as per the case statement. One for the DBA's I think....Hope it does not indicate an issue with the server.

Sachin.Nand

2937 Posts

Posted - 2010-08-10 : 07:01:52

One thing always to be remebered is avoid using functions in where clauses.Since you are using functions isnull and coalesce in the filter conditions the query will perform a scan even if an index exists on the column in where clause.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

paulwfromtheden
Starting Member

9 Posts

Posted - 2010-08-10 : 07:37:20

Interesting, I was not aware of that, many thanks for your help.

Kristen
Test

22859 Posts

Posted - 2010-08-10 : 08:58:48

Note that:

and pl.policylinkid = coalesce(@umr_id, pl.policylinkid)

is not quite the same as

and (pl.policylinkid = @umr_id or @umr_id is null)

The first will NOT(**) match where @umr_id IS NULL (I presume user's intention is "anything") and pl.policylinkid IS NULL. Perhaps pl.policylinkid is a NOT NULL column anyway, but the general distinction is important.

My preference is for

and (pl.policylinkid = @umr_id or @umr_id is null)

style for two reasons: first it is more index-friendly, and secondly if will match any value of pl.policylinkid, including NULL, if the provided parameter @umr_id is NULL - which I think matches the user expectation of "anything"

(**) a match CAN be forced, using [non-default] ANSI_NULL settings, but I don't think that is desirable.

paulwfromtheden
Starting Member

9 Posts

Posted - 2010-08-11 : 02:59:53

Thanks Kristen, I will take your advice.
Paul

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources