performance improvement for excel to mysql data transfer

[Amitg2]

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?

[Anonymous]

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

[Amitg2]

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.

[tnewbie · Accepted solution]

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 🙂

[Amitg2]

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

[tnewbie]

Glad it worked for you

[Anonymous]

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