Bods Performance Issue.

[Former Member]

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

[Former Member]

Vinay,

You better go with either Lookup or Join, but consider lookup only if the lookup table volumes are minimal as it is going sit onto the virtual memory until it finishes the lookup job, else please go with Join.

Venkata

[former_member246853]

Hi Vinay,

it will be better if you join the two table and pull the data from them rather than using sql statement.

According to the above sql statement for each and every record it hits the DB so BODS will not have any burden on it and DB server will handle. But it takes time if your data volume is high.

So I suggest you to use JOINS rather than SQL statement push-down.

Thanks,

Jagadeesh Borra

[Former Member]

Hi Vinay,

If look up is being done by using lookup or lookup_ext functions, this may increase load on the server.

For data volumes like yours, better we use join to join source table and look up table (so that we push down operations to DB level) . Once join is done, any required transform can be done in the query before loading it to target table. 

Thanks

Sameera.

[Former Member]

Hi All,

We are loading data from 2 tables

how to get the count of table (where condition should check each and every row) for every record.

How can we achieve this bods or any alternative way to achieve this.

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').

Can anyone help me out how to write join condition for the above SQL..

[former_member187605]

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]

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....