How to prioritize inserts,update records in same table?

Five Stars

How to prioritize inserts,update records in same table?

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.

Twelve Stars

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

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

good luck.

Francois Denis

Tag as "solved" for others! Kudos to thanks!

Fifteen Stars TRF
Fifteen Stars

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

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?

TRF
Seven Stars

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

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

 

 

Seven Stars

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

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.

Twelve Stars

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

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

Francois Denis

Tag as "solved" for others! Kudos to thanks!

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Download