topic Re: Anyway to enable batch update in tDBOutput? in Talend Studio

Anyway to enable batch update in tDBOutput?

billxu — Fri, 15 Nov 2024 22:00:44 GMT

Hello,

Is there any way to enable batch update in tDBOutput?

I found that the "Batch Size" option is only available when "Action on Data" is "Delete".

Any other "Action on Data" results row based updates, this could cause serious performance issues while dealing with huge amount of data.

If there is no such way to do batch update with tDBOutput, would you please advise other approaches to achieve this?

* I am using Talend to fetch data from AS400 into MySQL.

Thank you in advance!

Bill

Re: Anyway to enable batch update in tDBOutput?

Anonymous — Fri, 10 Mar 2023 02:10:47 GMT

I checked and found that when 'action on Data ' is Update or Delete, the 'Batch size' option is available, I am using v8.0.1.

Regards

Shong

Re: Anyway to enable batch update in tDBOutput?

billxu — Fri, 10 Mar 2023 02:36:47 GMT

Hi Shong

Thank you, using v8.0.1 (TOS for DI), as well.

But "Update or Delete" is not an option in my talend... ?

Any way, I want talend to not only update matched data, but also to do insertion when there is no match. Maybe "Update or Delete" cannot achieve this.

Any other ideas?

Best regards,

Bill

Re: Anyway to enable batch update in tDBOutput?

tnewbie — Fri, 10 Mar 2023 03:09:03 GMT

Hi,

There is a batch size option in tDBOutput even for non Delete Action on Data options.... I choose Update and it gives me the Batch Size option. I checked on 7.3 though

Re: Anyway to enable batch update in tDBOutput?

billxu — Fri, 10 Mar 2023 06:16:36 GMT

Thank you.

It seems that there is not such issue with tDBOracleOutput according to your screenshot. Unfortunately, I am dealing with MySQL database, so I have to use tDBMysqlOutput 😞

Best regards,

Bill

Re: Anyway to enable batch update in tDBOutput?

billxu — Fri, 10 Mar 2023 06:25:12 GMT

Hi @Shicong Hong ,

FYI

I think the batch update (or insert) functionality is just not implemented for MySQL components.

After tried out every option in "Action on Data", I found that there are also "Batch Size" likely (or exactly) options for "Insert" only or "Update" only. But neither of them generate Java codes of calling PreparedStatement.executeBatch (which is commonly used for batch update).

I am now considering using bulk output instead of the normal output.

Best regards,

Bill

Re: Anyway to enable batch update in tDBOutput?

tnewbie — Fri, 10 Mar 2023 16:10:10 GMT

Batch Size option is there In MySQL too. You have both Batch Size and 'Commit every' options for Action on Data ='Update', I also realized that it is not available for Action = Insert or Update