topic Re: Execute process 100 by 100 to insert values in DB in Talend Studio

Execute process 100 by 100 to insert values in DB

Anonymous — Sat, 16 Nov 2024 05:24:24 GMT

Hello people,

I´m with some trouble in my design and development in a way to execute the process i leave in attachment.

So heres the thing, i have a table that contains 1000 rows with a flag so i can say how many of those i will migrate.

Problem:

How can i loop the process 100 by 100 with the flag beeing updated at start of each loop? So at the start, my process should update the first 100 rows ,and migrate information for the first 100 then should deativate the first 100 and go for the next 100 and migrate until reaches 700. how to do this ?

DB - SQL Server.

Talend version - last version.

Regards,

Re: Execute process 100 by 100 to insert values in DB

Anonymous — Tue, 02 Jul 2019 17:13:49 GMT

I think you need a key or unique index in your table

You first select records where unique_column between start and start+100

Then you update your flag and do all your processing

To update a table : tDBOutput, then you choose the option "UPDATE"

Re: Execute process 100 by 100 to insert values in DB

Anonymous — Tue, 02 Jul 2019 18:54:56 GMT

Hi,

Usage of the primary key (if its not present, a unique index) is the best way to update records when you are processing the records in loops.

But the flip side of the loop is that it will take more time to process since the data will be taken as batches. So the batch size will be a crucial element to optimize the flow throughput. I would recommend to increase the batch size as much as possible to reduce the number of iterations (which means less processing time).

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂

Re: Execute process 100 by 100 to insert values in DB

Anonymous — Wed, 03 Jul 2019 16:34:37 GMT

Hi,

You guys are far too kind ty for response.

What i came up with was this i leave in attachment.

What i would like to know is how can i do that with a loop instead of multiple DB? The TRun JOB always call same subjob.

Regards,

L_5097.tmp.PNG

Re: Execute process 100 by 100 to insert values in DB

Anonymous — Thu, 04 Jul 2019 14:43:25 GMT

Hi,

Lets go back to the basics. Could you please share the rationale for doing this process as multiple batches instead of a single batch?

If you increase the memory of the job, you should be able to do the same process as a single batch right?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂