Madhivanan's TSQL Blog

Getting Started with Adobe After Effects - Part 6: Motion Blur


	Blogs

Upload Image

Close it

Select File


	Like

Invite Friends

Browse by Tags · View All

sql_server	217
t-sql	211
tsql	116
sqlserver	96
BRH	78
#SQLServer	66
#TSQL	56
SQL Server	34
function	11
SSMS	9

Archive · View All

August 2007	17
August 2010	8
June 2012	7
June 2011	7
November 2007	7
August 2012	6
May 2012	6
November 2011	6
August 2011	6
October 2011	6

Home · Posts · Most Liked · Most Discussed · Ask a Question · Fans (421) · Preferences

Performance comparison - IIF logical function Vs CASE expression

Jul 30 2013 12:00AM by Madhivanan

Both IIF and CASE expressions are used to make some decision based on an expression and return one of the two values. While CASE expression is supported in all versions, IIF is supported from version 2012 onwards. Here is the performance comparison of these two.

Consider the following set of data with 10 millions rows. The column gender will have either 0 or 1.

create table #genders (gender bit)

insert into #genders (gender)
select 
	top 10000000
		checksum(newid())%2 as gender_flag
from 
	sys.objects as so1 cross join
	sys.objects as so2 cross join
	sys.objects as so3 cross join
	sys.objects as so4

The requirement is to return 'Female' if gender=0, else return 'Male'

declare @gender varchar(6)

SET STATISTICS TIME ON

select 
	@gender=case when gender=0 then 'Female' else 'Male' end  from 
#genders 

select 
	@gender=iif(gender=0 ,'Female','Male') from 
#genders 
SET STATISTICS TIME OFF

Here is the result

 SQL Server Execution Times:
   CPU time = 1591 ms,  elapsed time = 1599 ms.

 SQL Server Execution Times:
   CPU time = 1607 ms,  elapsed time = 1601 ms.

As you see CASE expression is slightly faster than IIF logical function

UPDATE:

IIF is internally converted to CASE expression. I was under assumption that this internal conversion may slightly take extra time. But as said by Dave ballantyne (refer the comments), the difference may be few nano seconds and it will have no impact once the execution plan is cached

Tags:

Previous: Grouping Date and time values with DATEADD and DATEDIFF functions
Next: Question of the month August 2013 - Find out problamatic query

Madhivanan

2 · 40% · 12993

Liked

Lifesaver

Refreshed

Learned

Incorrect


Like this
Post this to my facebook wall
Tweet this
This was a true life saver
Thanks, this helped me to refresh my memory
Very informative, I just learned something new
I disagree, this information is incorrect


	Submit

13 Comments

Hi,

Good post. I personally recommend "case" over "iif", just because of "Case" is available in most of all sql server version. We all knew that still most of organization are working on sql 2005 or 2008.

Vinay Kumar

commented on Jul 30 2013 12:14AM

Vinay Kumar

660 · 0% · 51

How can we find the performance of a stored procedure....

Thanks Ribu

commented on Jul 30 2013 12:53AM

ribu.rajan

1622 · 0% · 12

Thanks Vinay Kumar. Yes we can make use of CASE expression in all versions and in other RDBMSs as well.

commented on Jul 30 2013 1:19AM

Madhivanan

2 · 40% · 12993

Ribu, can you provide more information on what you want to know about performance of stored procedure?

commented on Jul 30 2013 1:20AM

Madhivanan

2 · 40% · 12993

I disagree with your conclusion. The resolution of the SQL server clock is 16.6667 ms, so any numbers that are 15-17 ms apart are only one time unit away from each other. I would say from the results you got that they are in fact identical. Could you run the test several times?

commented on Jul 30 2013 2:02AM

ErikEckhardt

65 · 3% · 898

Actually, as I show here : http://sqlblogcasts.com/blogs/sqlandthelike/archive/2011/07/19/denali-iif-and-choose.aspx

IIF is a macro for case, so they are the same.

One the subject of the perf comparison, you should really run multiple time over multiple threads ( with something like SQL Query Stress) before drawing any conclusion.

commented on Jul 30 2013 4:26AM

dave ballantyne

110 · 1% · 466

Hi ErikEckhardt and dave ballantyne , Thanks for the feedback. I initially ran it three times and shown the result of third run. Here are the results when I executed the code multiple times.

 SQL Server Execution Times:
   CPU time = 1622 ms,  elapsed time = 1631 ms.

 SQL Server Execution Times:
   CPU time = 1654 ms,  elapsed time = 1655 ms.


 SQL Server Execution Times:
   CPU time = 1622 ms,  elapsed time = 1630 ms.

 SQL Server Execution Times:
   CPU time = 1669 ms,  elapsed time = 1677 ms.


 SQL Server Execution Times:
   CPU time = 1996 ms,  elapsed time = 2019 ms.

 SQL Server Execution Times:
   CPU time = 2013 ms,  elapsed time = 2035 ms.


 SQL Server Execution Times:
   CPU time = 2028 ms,  elapsed time = 2031 ms.

 SQL Server Execution Times:
   CPU time = 2028 ms,  elapsed time = 2018 ms.


 SQL Server Execution Times:
   CPU time = 1966 ms,  elapsed time = 1972 ms.

 SQL Server Execution Times:
   CPU time = 1981 ms,  elapsed time = 1987 ms.


 SQL Server Execution Times:
   CPU time = 1981 ms,  elapsed time = 1983 ms.

 SQL Server Execution Times:
   CPU time = 1981 ms,  elapsed time = 1983 ms.


 SQL Server Execution Times:
   CPU time = 1965 ms,  elapsed time = 2017 ms.

 SQL Server Execution Times:
   CPU time = 2013 ms,  elapsed time = 2005 ms.


 SQL Server Execution Times:
   CPU time = 1997 ms,  elapsed time = 2036 ms.

 SQL Server Execution Times:
   CPU time = 1997 ms,  elapsed time = 1997 ms.


 SQL Server Execution Times:
   CPU time = 2012 ms,  elapsed time = 2010 ms.

 SQL Server Execution Times:
   CPU time = 2028 ms,  elapsed time = 2046 ms.


 SQL Server Execution Times:
   CPU time = 1997 ms,  elapsed time = 2001 ms.

 SQL Server Execution Times:
   CPU time = 1981 ms,  elapsed time = 1987 ms.


 SQL Server Execution Times:
   CPU time = 1997 ms,  elapsed time = 2007 ms.

 SQL Server Execution Times:
   CPU time = 1996 ms,  elapsed time = 2037 ms.


 SQL Server Execution Times:
   CPU time = 1997 ms,  elapsed time = 1988 ms.

 SQL Server Execution Times:
   CPU time = 1997 ms,  elapsed time = 1994 ms.


 SQL Server Execution Times:
   CPU time = 1997 ms,  elapsed time = 2013 ms.

 SQL Server Execution Times:
   CPU time = 2012 ms,  elapsed time = 2033 ms.

commented on Jul 30 2013 4:51AM

Madhivanan

2 · 40% · 12993

I'm not convinced there is any difference, certainly not enough to care about.

commented on Jul 30 2013 5:33AM

Jonathan Roberts

76 · 2% · 745

No there is no difference , They are the same

Look at the properties for the compute scalar operation in the execution plan,

what does the "Defined Values" say ??? Oh, a CASE statement, IIF is a CASE macro, nothing else.

commented on Jul 30 2013 8:01AM

dave ballantyne

110 · 1% · 466

Totally agree on explanation given by dave ballantyne

commented on Jul 31 2013 4:45AM

harsh

452 · 0% · 87

Thanks dave ballantyne. I know that IIF is internally converted to CASE expression but I was under assumption that this internal conversion may slightly take extra time.

commented on Aug 1 2013 6:24AM

Madhivanan

2 · 40% · 12993

Hi Madhivanan,

Only a few nano seconds and I would be surprised if that was reliably measurable, even then we are talking about having to dive into Xperf to get those metrics. In any case, once the plan is cached then there will be no difference as it doesent recalculate.

commented on Aug 2 2013 3:23AM

dave ballantyne

110 · 1% · 466

I agree with you Dave ballantyne. I am updating this post to give additional information.

commented on Aug 2 2013 3:51AM

Madhivanan

2 · 40% · 12993

Your Comment

Sign Up or Login to post a comment.

"Performance comparison - IIF logical function Vs CASE expression" rated 5 out of 5 by 3 readers +Madhivanan