Performance issue with Coalesce

Database
Enthusiast

Performance issue with Coalesce

Hi All,

I have one update statement as below:

UPDATE ~DATABASE_NAME_BASE~.~TABLE_NAME~
SET EFFECTIVE_END_DATE = ?CURR_BUS_DATE?,
 DW_UPD_LOAD_TIMESTAMP  = Current_timestamp(6)     
WHERE ~DATABASE_NAME_BASE~.~TABLE_NAME~.ACCM = ~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~.ACCUM
AND
(COALESCE(~DATABASE_NAME_BASE~.~TABLE_NAME~.TYPE_ID,0) <> COALESCE(~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~._TYPE_ID,0)
OR  COALESCE(~DATABASE_NAME_BASE~.~TABLE_NAME~.DINTEREST_RATE,0) <> COALESCE(~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~.DINTEREST_RATE,0)
OR  ~COALESCE(DATABASE_NAME_BASE~.~TABLE_NAME~.CINTEREST_RATE,0) <> COALESCE(~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~.CINTEREST_RATE,0)
)
AND
~DATABASE_NAME_BASE~.~TABLE_NAME~.EFFECTIVE_END_DATE = ?HIGH_END_DATE? ;

I have to put coalesce statements because i am expexting Nulls in those columns and we can't compare NULL to a NULL.

But coalesce is high cpu consuming function and i am scared it might throw performance issues.

Same is the case with my inserts.

Do i have any alternative to these?

Thanks,

Amit

5 REPLIES
Enthusiast

Re: Performance issue with Coalesce

Hi Dnoeth,

Can you suggest anything on this?

Regards,

Amit

Senior Apprentice

Re: Performance issue with Coalesce

Hi Amit,

COALESCE doesn't use much CPU, but it might change the optimizer's plan.

I don't know if rewriting the logic will help:

OR ~DATABASE_NAME_BASE~.~TABLE_NAME~.TYPE_ID <> ~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~._TYPE_ID
OR NOT (~DATABASE_NAME_BASE~.~TABLE_NAME~.TYPE_ID IS NULL AND ~DATABASE_NAME_TEMP~.~TEMP_TABLE_NAME~._TYPE_ID IS NULL)

If ACCM is the PI of the target table and EFFECTIVE_END_DATE is not a partitioning column a MERGE will provide the most efficient plan.

Enthusiast

Re: Performance issue with Coalesce

Hi Dieter,

Thank you for your reply. I had this notion and read at numerous places that Coalesce is troublesome to use, mostly in the join conditions.

Thanks for clarifying that.

Will check with merge once and compare the performance.

Regards,

Amit

Senior Apprentice

Re: Performance issue with Coalesce

Hi Amit,

COALESCE in joins might change the plan, if it's in a join on the PI-columns when NULLs should be treated equal. In that case there's a recommendation to use ON (t1.col = t2.col OR t1.col IS NULL AND t2.col IS NULL) instead of.

Enthusiast

Re: Performance issue with Coalesce

Okay. Got it . Thanks a lot :)