ETL transform run time is very high

[Sunil25]

Hello Guys, 

We are running an ETL which is taking almost 2 hours just to transform after the data is loaded into staging table. The size of the data is 200K records loading into cmdb tables. There are similar ETL's with same data size and same target tables which are transforming in just minutes. We do not have any complex transforms in the ETL and using OOB provided transforms like cleanse, concat etc.,

Could you please suggest some pointers how to troubleshoot and find the culprit.

Thanks,

Sunil Safare 

[Sunil25 · Accepted solution]

We are able to resolve the issue. We are performing table look ups on CI, Computer and Network tables which has very huge data(in millions) during mapping and hence delay.

Pointer here is make sure you limit table look up operations during mapping and if you have table look up, make sure that table is indexed.

Thanks!

[MarcinM]

Hello @Sunil25 ,

how much faster did your integration get? I'm struggling with the same thing. I've got 180k records that needs to be populated into 5 different tables. All are indexed but a lot is going on in Staging Table stage (lookups, etc but all indexed as well).
I went from 10-12h to 3,5h but this is still not acceptable.

Do you have any hints for me?

Thanks in advance!

MM

[Sunil25]

Hi MM,

I would suggest to check for table look ups. Like remove all the look ups and test how fast data transmission is, then add one after other lookup to test it. In my case, there were 3 table look ups querying for sys_id's. 

I followed the same of removing lookups and check. Hope it helps!

Thanks,

Sunil Safare