17 May 2006 07:53 tonyrogerson

IFNULL (location_id, 99) = COALESCE(@my_location_id, IFNULL(location_id, 99))

Hopefully this will put to bed the lobby that recommends you use COALESCE for queries that have optional specified parameters, the real world example would be a search form where the user can optionally choose from a number of drop down lists and then execute and what you then have to do in the query is to ignore the columns (parameters) they don't want to search upon.

The statement in the title was taken from one of --CELKO-- posts who is the biggest culprit doing this, apparently there are some tricks in DB2 and Ingress to allow a more efficient plan but in SQL Server no such features exist. Even the OPTION( RECOMPILE ) which sniffs the parameter values has no effect here.

Enter our test data - the scraped posts from all the SQL Server usenet groups which at the time I took my database copy was 753868 rows which amounts to 134MBytes, this table doesn't contain the messages but just the tree and some information around the post, create table below:-

CREATE TABLE [dbo].[mb_message](
   [id] [int] NOT NULL,
   [entry_date] [datetime] NOT NULL,
   [mb_forum_id] [int] NOT NULL,
   [parent_mb_message_id] [int] NULL,
   [parent_entry_date] [datetime] NOT NULL,
   [flattened_tree] [varchar](900) COLLATE Latin1_General_CI_AS NOT NULL,
   [message_depth] [tinyint] NOT NULL,
   [message_reads] [int] NOT NULL,
   [last_posting] [datetime] NULL,
   [nntp_message_id] [varchar](200) COLLATE Latin1_General_CI_AS NULL,
   [top_parent_mb_message_id] [varchar](900) COLLATE Latin1_General_CI_AS NULL,
   [is_private] [char](1) COLLATE Latin1_General_CI_AS NOT NULL,
   [author_id] [int] NULL
) ON [PRIMARY]

There are no indexes on the table, its just a heap, so for the purpose of this example we create two indexes, one for each parameter we are going to use, note I'm not going to bother putting a clustered index on the table because I am simply illustrating the problem using COALESCE and its effect on index usage and having a clustered index will simply confuse the picture.

create index t1 on mb_message( mb_forum_id )
create index t2 on mb_message( top_parent_mb_message_id )

Query 1 - The query built dynamically would end up like this

declare @fid int
declare @mid varchar(20)
set @fid = 9
set @mid = '102389'

select top 10 *
from mb_message
where mb_forum_id = @fid
   and top_parent_mb_message_id = @mid

As you can see from the execution plan below it correctly uses the index and does an 'Index Seek' and the Logical Reads is just 5 pages (40KBytes).

2 Writing it using COALESCE

declare @fid int
declare @mid varchar(20)
set @fid = 9
set @mid = '102389'

select top 10 *
from mb_message
where mb_forum_id = coalesce( @fid, mb_forum_id )
    and top_parent_mb_message_id = coalesce( @mid, top_parent_mb_message_id )
option( recompile )

The option( recompile ) is in there for good measure, it was introduced in SQL Server 2005 and does parameter sniffing but is of little use in this because of COALESCE use.

As you can see from the execution plan below it does not use the index, instead a table scan and costs us 13031 Logical Reads (101MBytes).

3. And finally the query using the ISNULL....

declare @fid int
declare @mid varchar(20)
set @fid = 9
set @mid = '102389'

select top 10 *
from mb_message
where isnull( mb_forum_id, 99 ) = coalesce( @fid, isnull( mb_forum_id, 99 ) )
    and isnull( top_parent_mb_message_id, 99 ) = coalesce( @mid, isnull( top_parent_mb_message_id, 99 ) )
option( recompile )

Gives the same plan and logical reads as (2) above.

Summary

The only way in SQL Server, even 2005, to get an efficient, scalable and performant solution is to either use a ton of IF ELSE control of flow blocks (one per parameter combination) or use dynamic SQL. Erland Sommarskog gives an indepth discussion on dynamic SQL on his website.

Unfortunetly --CELKO-- fails to realise whilst a solution may look 'tidy', tidyness comes at a price and that price is performance and scalability. Would you rather your query read 40KBytes or 104,248KBytes, and all those extra pages that require reading may well be locked by people inserting/updating and deleting.

Reference

--CELKO-- reply to post on 17 May 2006 on microsoft.public.sqlserver.programming

--CELKO-- reply to post on 25 Apr 2006 on comp.databases.ms-sqlserver

Filed under: ,

Comments

# re: IFNULL (location_id, 99) = COALESCE(@my_location_id, IFNULL(location_id, 99))

17 May 2006 10:28 by Colin Leversuch-Roberts

I've run into this "style" of programming in a couple of places I've worked and it's mentioned in my best practices, as you say it produces crap plans and to my mind is not all that elegant. I always wondered where devs got this idea from - now I know.

btw I like the conversion of pages to MB, I've been doing this for a while in an attempt to illustrate to developers exactly what high i/o does to your sql server - telling someone they just moved over 7gb of data on their server to return a 6 row result set often does the trick - of course you do have to convince them an i/o = a page = 8kb.

# re: IFNULL (location_id, 99) = COALESCE(@my_location_id, IFNULL(location_id, 99))

06 July 2007 04:03 by shiju

If both the input is a string. you can do something like this

select name,type into table1 from sys.objects

create clustered index idx_name on table1(name)

create  index idx_type on table1(type)

create proc #proc

@name varchar(100)=null,

@type char=null

as

select @name = isnull(@name,'%')

select @type = isnull(@type,'%')

select * from table1 where type like @type and name like @name

exec #proc @name='sysdbfiles'

exec #proc @type='P'

both does seek.

I am wondering if there is similar trick to do this for other datatypes.