Reg : Performance of Coalease in Select Statement

General
Enthusiast

Reg : Performance of Coalease in Select Statement

Source: Present in Teradata ( Extract- replicate by golden gate from source to teradata).

Worktable :Present in teradata(Captures all the DML operations done by goldengate on the source table)

For identifying Incremental Data we join worktable and source as below :

My Question/concern here is regarding the COALESCE and other FUNCTIONS in the select statements ? Will it impact the performance ?

select

(case when S.COLUMN1 is null then null else '~' END) AS Default_varchar_value,

(case when S.COLUMN1 is null then null else '-99' END) AS Default_integer_value,

(case when S.COLUMN1 is null then null else cast('9999-12-31 00:00:00.000000' as timestamp)END) AS Default_Datetimestamp_value,

(case when S.COLUMN1 is null then null else cast('9999-12-31' as date)END) AS Default_Date_value,

UPPER(trim(cast(COALESCE(coalesce(S.COLUMN2,Default_varchar_value),W.COLUMN2) as varchar(250)))) as Gmng_Loc_Desc,

UPPER(trim(cast(COALESCE(coalesce(S.COLUMN2,Default_varchar_value),W.COLUMN2) as varchar(100)))) as Gmng_Loc_Nm,

UPPER(trim(cast(COALESCE(coalesce(S.COLUMN2,Default_varchar_value),W.COLUMN2) as char(4)))) as Gmng_Loc_Cd,

UPPER(trim(coalesce(coalesce(S.COLUMN3,Default_varchar_value),W.COLUMN3))) as Frnt_Mny_TTO_Ind,

cast(COALESCE(coalesce(S.FMTR_ACCESS_STAMP,Default_Datetimestamp_value),W.FMTR_ACCESS_STAMP) as timestamp(6)) as Frnt_Mny_Txn_Acs_Dttm,

(COALESCE(coalesce(S.FMTR_COB_AMOUNT,Default_integer_value),W.FMTR_COB_AMOUNT)) as Frnt_Mny_Txn_Bus_Cls_Amt,

UPPER(trim(COALESCE(coalesce(S.FMTR_COB_FLAG,Default_varchar_value),W.FMTR_COB_FLAG))) as Frnt_Mny_Txn_Bus_Cls_Ind      ,

COALESCE(coalesce(S.FMTR_CURR_CR_LIMIT,Default_integer_value),W.FMTR_CURR_CR_LIMIT) as Frnt_Mny_Txn_Cur_Cr_Lmt_Amt           ,

from 

(

sel

COLUMN1,             

FMTR_TRAN_NO  ,                

FMTR_ACCT   ,                  

FMTR_DATE   ,                  

FMTR_TRANSACTION_CODE  ,       

FMTR_TIME   ,                  

COLUMN2   ,                   

FMTR_BY     ,                  

FMTR_FIELD_NO ,                

FMTR_OLD_DATA   ,              

FMTR_NEW_DATA   ,              

FMTR_PRIOR_TRAN_DATE   ,       

FMTR_PRIOR_TRAN   ,            

FMTR_LMT1_AUTH    ,            

FMTR_COB_FLAG     ,            

FMTR_CURR_CR_LIMIT     ,       

COLUMN3     ,            

Sys_Tran_Dttm  

QUALIFY ROW_NUMBER() OVER(PARTITION BY COLUMN1 ,FMTR_TRAN_NO ORDER BY Sys_Tran_Dttm DESC ) = 1

from DEV_LND_TNDM_VW.FMTRANS_16 WHERE Process_FLG='W'

)W

LEFT JOIN

PRD_TNDM_STG_VW.FMTRANS_16 S

ON W.COLUMN1  = S.COLUMN1

AND W.FMTR_TRAN_NO = S.FMTR_TRAN_NO;


Tags (1)
5 REPLIES
Enthusiast

Re: Reg : Performance of Coalease in Select Statement

Many conversion functions!!!! After all , it has to undergo conversion and it is software and not magic. It will hit performance. If it is me, I will study the source system well and decide what I want further downstream. Same columns are converted repititively in different ways. If it can be done once and then do the checking, it may help.My thought!!!. Try to check with explain and fine tune.

Enthusiast

Re: Reg : Performance of Coalease in Select Statement

Thanks Raja for responding !

Can you also explain how coalesce function works internally in teradata ?

And also how it works for the above scenario i posted 

Enthusiast

Re: Reg : Performance of Coalease in Select Statement

I suggest you have a look at this link, about coalesce:

http://www.info.teradata.com/HTMLPubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1145_111A/ch02...

Run explain and see the steps and performance behaviour.

Enthusiast

Re: Reg : Performance of Coalease in Select Statement

Hi varanasi,

When you write : coalesce(coalesce(s.column2,default_varchar_value), w.column2)

The inner coalesce return s.column2 if is not null or default_varchar_value, then never return a null value

The outher coalesce never will return w.column2 because nevwe is null.

Regards.

Enthusiast

Re: Reg : Performance of Coalease in Select Statement

Hi,

Check the previous posts ,  explain && coalesce syntax .

Furthermore , always try to avoid implicits conversions from Teradata , always use cast - even for null-.

This has logical and physical reasons. First you know your data , what kind of datatype you expect. So , no possible extra casting on next steps .

From physical aspect , there is a feature in TD with varchar column which partiticipated on group by / order by ,so on your example when you use varchar(250) then it will exploit that column to 250(or 500 bytes for Unicode)  multiplied by the number of the rows that you have. This leads to more spool ,more CPU/IO in order to get / handle the blocks - data.

So , be precise with the format , this will help you on any function.

Thanks.