topic Re: performance improvement for excel to mysql data transfer in Talend Studio

performance improvement for excel to mysql data transfer

Amitg2 — Sat, 16 Nov 2024 00:19:44 GMT

Hi, I have created a talend job to transfer data from excel sheet to mysql table. Excel contains ~6 lac records and is ~37MB in size. I removed the tMap component as it was giving out of memory exception and not really necessary. Attached sc of the current job design. I am using Event mode since it consumes less memory.

When I run the job, I see ~100 rows are transferred per sec.

Is there a way to improve this transfer rate to ~1000 rows/s?

Re: performance improvement for excel to mysql data transfer

Anonymous — Fri, 02 Apr 2021 06:11:23 GMT

Hello,

Could you please increase your commit size so that more number of records are committed together? Please also alter the Batch size and verify the output performance. The details of both these parameters are mentioned in Advanced options of tmysqloutput.

For more information, please refer to this online component reference about: TalendHelpCenter: tMysqlConnection

Best regards

Sabrina

Re: performance improvement for excel to mysql data transfer

Amitg2 — Mon, 05 Apr 2021 10:07:23 GMT

Hi Sabrina, thanks for the reply. I am not able to see Commit size and Batch size options in tMysqlOutput component. Attaching a screenshot of all the options I see in advanced setting of tMysqlOutput component.

Re: performance improvement for excel to mysql data transfer

tnewbie — Mon, 05 Apr 2021 21:32:46 GMT

Hi Amitg,

In my view, if your job is so simple with just one Target instance, you don't need a DB connection component explicitly. DB connection comes handy when there are multiple instances of DB components. Taking away the DBConnection would help you see the Batch and Commit Sizes 🙂

Re: performance improvement for excel to mysql data transfer

Amitg2 — Tue, 06 Apr 2021 09:24:29 GMT

Thanks for the reply. I see the transfer rate has increased significantly.

Re: performance improvement for excel to mysql data transfer

tnewbie — Tue, 06 Apr 2021 12:38:34 GMT

Glad it worked for you

Re: performance improvement for excel to mysql data transfer

Anonymous — Wed, 07 Apr 2021 06:30:56 GMT

Hello,

Use Batch option is available only when you have selected, the Update or the Delete option in the Action on data field.

You will get more information from component reference.

Best regards

Sabrina