topic Re: How to prioritize inserts,update records in same table? in Talend Studio

How to prioritize inserts,update records in same table?

rp2018 — Mon, 20 Aug 2018 22:28:33 GMT

Have tmap outputting rows which are new rows, expiring rows and inserting new updated rows. It is taking more than 1/2 hour to load 12,000 records to the database. How do we prioritize that it will insert new rows,expire rows then insert new updated rows. It is using the same table to insert,expire and insert new updated rows rows.

Re: How to prioritize inserts,update records in same table?

fdenis — Tue, 21 Aug 2018 07:58:03 GMT

split your job one for insert with a lookup, the second one with a lookup to update only unmatched row.

good luck.

Re: How to prioritize inserts,update records in same table?

TRF — Tue, 21 Aug 2018 08:05:39 GMT

30' for 12,000 records, this means less than 7 per second - for sure you have a big problem.
What's your database engine?
What's an "expired" record?
Can you share your job design?

Re: How to prioritize inserts,update records in same table?

ThWabi — Tue, 21 Aug 2018 10:05:31 GMT

Hello rp2018,

you can change the tMap output links order using the context menu on the output connection. But I think that does not guarantee that really all new rows are inserted before all update rows are modified. If you definitely want to process the outputs in a certain order, please see the suggestion of @fdenis.

Concerning the processing speed, have you tried to set the "extent insert size" or "batch size" of your database output components? Have you also tried to activate "parallel execution"? (Both settings are under the "advanced settings" tab.)

Best regards,

Thomas

Re: How to prioritize inserts,update records in same table?

PhilHibbs — Tue, 21 Aug 2018 10:24:20 GMT

Sounds like you are using a type 2 dimension, where you "update" a data item by setting an expiry date on the current row and insert a new version that has a "valid from" or "current version" flag. If so, separating the "expiry" and "update insert" is a bad idea as you probably need those to be an atomic transacton.

Re: How to prioritize inserts,update records in same table?

fdenis — Tue, 21 Aug 2018 10:41:38 GMT

the best way to use historisation with from to date is db triggers.
if it's the case use it.
Regards,