cancel
Showing results for 
Search instead for 
Did you mean: 

Bods Performance Issue.

Former Member
0 Kudos
403

HI Experts,

I am trying to load 1.2 million records from Source table to target.

Source Table- Temp1

target Table - Temp2

Lookup table- Temp3

I am pulling the 11 columns from source table and doing some lookups to load data to target table.

When i am doing the lookup on 11 columns job is taking almost 8 hrs to complete.

Below is the Sql operation which i am doing on  some columns in Query Transformation before loading into target.

sql( 'unv_ds_kks','select COL_SEG_ID from UNV_DS.TEMP3 where SEG_TYPE like \'ABC%\' and SEG_CODE =\'' || Case_EESBA_KEMS_Case_EESBA_KEMS_EXISTS.ESS_SEG_ID_4 || '\' and KOMP_FILE_POSITION = 5').

Above SQl is implemented on 11 columns with different where conditions.

Is there any way to improve performance.

Thanks

vinay

View Entire Topic
former_member187605
Active Contributor
0 Kudos

You must never use a sql function in a Query transform. Use a lookup function instead with the appropriate cache setting, or (often even better) an outer join.

mohan_salla
Participant
0 Kudos

SQL function in Query Transform is a bad practice and it takes ages to load the data.. Lookup function also alters your job performance... Better Join the tables....