Optimizing tReplicate with lookups in big tables

teal — Fri, 15 Nov 2024 21:56:25 GMT

I have a job that updates and deletes records in multiple tables based on the same input data.

I'm comparing the rm table in the v24 MSSQL db and in the v26, if a record was deleted in the v24, it has to be deleted in the v26.

This is what's happening with tMap_3, it finds the matching bl_id, fl_id and rm_id as keys.

But before deleting that record in the rm table, the three keys are used as foreign keys in other tables records and have to be deleted or updated.

So in "rmpct" the records that have these three foreign keys are deleted, while in the other tables such as activity_log for example, the values of these three keys have to be emptied. So I use a tMap with a lookup to find the corresponding primary key and then update the values of these records.

To do that for the different tables, I use a tReplicate but now the problem is that it takes a really long time (30 min).

Any idea on how I could optimize this?

Thanks!

Re: Optimizing tReplicate with lookups in big tables

Anonymous — Tue, 04 Apr 2023 06:29:19 GMT

All tMap output is executed in parallel, which takes up more memory resources.

You can optimize job design from the following aspects:

Store the lookup data on disk instead of memory if the volume of lookup data is big. (see below image)
Allocate more memory to the job execution.

Regards

Shong

topic Optimizing tReplicate with lookups in big tables in Talend Studio

Optimizing tReplicate with lookups in big tables

Re: Optimizing tReplicate with lookups in big tables