CODEWITHSUNDEEP
sql-servert-sql
Abhijeet Patel
Abhijeet Patel

Reputation: 6868

Performance of ISNULL in SELECT statement

I have a table with the following columns,

ID(int),
RegardingContactID(int),
RegardingAccountId(int),
RegardingIncidentID(int),
RegardingType(varchar)

RegardingType can be "Contact", "Account", "Incident"

Basically I'd like to abstract all the RegardingXXXID columns like this:

 SELECT 
     ID,
     ISNULL(RegardingContactID, ISNULL(RegardingAccountId, RegardingIncidentID)) AS RegardingID,
     RegardingType

This seems to work as intended but the ISNULL does not appear to be performant with large datasets.

Is there a more elegant/performant way to achieve this?

Upvotes: 0

Views: 644

Answers (3)

michal.jakubeczy
michal.jakubeczy

Reputation: 9469

As the other said, COALESCE is more elegant and might perform better.

But regarding the performance I recommend to benchmark both approaches, it is the only way how to realize it. Also another approach might be to select all 3 columns and choose the right one in application which processes query result.

Upvotes: 0

xacinay
xacinay

Reputation: 909

If we dig a bit more, COALESCE is basically transformed to CASE statement by SQL Server engine into something like that:

select case 
      when RegardingContactID is not null then RegardingContactID
      when RegardingAccountId is not null then RegardingAccountId
      when RegardingIncidentID is not null then RegardingIncidentID
      end AS RegardingID

But you may end with this: 

select case 
      when RegardingType = 'Contact' then RegardingContactID
      when RegardingType = 'Account' then RegardingAccountId
      when RegardingType = 'Incident' then RegardingIncidentId
      end as RegardingID

which, imho, is more relevant to your original target.

PS. ISNULL perfomance is quite not bad. It's just the evaluation chain (3 times) may cause perfomance slowdown.

Upvotes: 1

mehdi lotfi
mehdi lotfi

Reputation: 11571

I suggest you to use COALESCE instead of ISNULL

SELECT ID,
       COALESCE(RegardingContactID, RegardingAccountId, RegardingIncidentID) AS RegardingID,

Upvotes: 1

Related Questions