How to prioritize inserts,update records in same table?

[rp2018]

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.

[fdenis]

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

good luck.

[TRF]

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?

[ThWabi]

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

[PhilHibbs]

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.

[fdenis]

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