All Forums General
varanasianup 14 posts Joined 09/14
19 Jan 2015
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_va rchar_value),W.COLUMN2) as varchar(250)))) as Gmng_Loc_Desc,

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

UPPER(trim(cast(COALESCE(coalesce(S.COLUMN2,Default_va rchar_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_Dat etimestamp_value),W.FMTR_ACCESS_STAMP) as timestamp(6)) as Frnt_Mny_Txn_Acs_Dttm,

(COALESCE(coalesce(S.FMTR_COB_AMOUNT,Default_integer_v alue),W.FMTR_COB_AMOUNT)) as Frnt_Mny_Txn_Bus_Cls_Amt,

UPPER(trim(COALESCE(coalesce(S.FMTR_COB_FLAG,Default_v archar_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;

 

 

 

 

 

Raja_KT 1246 posts Joined 07/09
19 Jan 2015

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.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

varanasianup 14 posts Joined 09/14
19 Jan 2015

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 

Raja_KT 1246 posts Joined 07/09
19 Jan 2015

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.072.57.html
Run explain and see the steps and performance behaviour.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Dixxie 58 posts Joined 12/10
20 Jan 2015

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.
 
 

eejimkos 73 posts Joined 01/12
29 Jan 2015

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.
 
 
 

You must sign in to leave a comment.