One Star

MySQL and bulk

Hello,
i've got some troubles with Mysql in talend 5.0.1 Data integration Professional Edition.
I would like to insert or update my database. The tMySQLOutput components is awesome for that. But when you've got 100 k lines to "update or insert" (or "insert or update") it's kinda slow. I tried wit "Replace" and it duplicate lines :/
So i tried tMySQLOutputBulk and then tMySQLBulkExec. Faster!
But in "Advanced settings > Action on data" we have a set of intresting choices and... still no good.
- When i use a replace it act like an simple insert and duplicate lines
- When i use an update, it only update existing lines and ignores new ones
Any advices for speeding up that process?
6 REPLIES
Community Manager

Re: MySQL and bulk

Hi
tMysqlBulkExec component don't support the insert and update action at the same time, if you stick to use tMysqlBulkExec component, as a workaround, you can read the source file and do an inner join with target table on primary key, get the rows which need to be insert, and the rows which need to updated, and use two tMysqlBulkExec components for each action. For example:
tFileInputDelimited(read source file)--main--tMap--main--tMysqlBulkExec_1(for inserting)
| --main--tMysqlBulkExec_1(for updating)
lookup
|
tMysqlInput(read primary key from target table)
on tMap component, do an inner join on primary key, get the matched rows that need to be updated, get the unmatched rows that need to be inserted.
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: MySQL and bulk

Thank you!
That's a good idea. I'm going to try this.
I was thinking of that... Thanks to you it's a bit clear now.
One Star

Re: MySQL and bulk

Hi ,
The job which i have configured has to load 6 crore and contains 43 col into a mysql table ,the flow is as follows
tFileInputDelimited--->tmap--->tSort--->tUniq---->tMysqlOutputBulkExec
The job takes around 3 and half hours to load into table . Can anyone tell how to speed up the job and reduce time of execution.
Community Manager

Re: MySQL and bulk

Hi ,
The job which i have configured has to load 6 crore and contains 43 col into a mysql table ,the flow is as follows
tFileInputDelimited--->tmap--->tSort--->tUniq---->tMysqlOutputBulkExec
The job takes around 3 and half hours to load into table . Can anyone tell how to speed up the job and reduce time of execution.

A quick question, what about the performance if you remove tSort--->tUniq from your job?
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: MySQL and bulk

hi shong,

The time it took to load is almost same without tSort and tUniq
One Star

Re: MySQL and bulk

Hi
Any suggestions on reducing time of execution of job while loading into mysql table