topic Re: MySQL and bulk in Talend Studio

MySQL and bulk

Anonymous — Tue, 16 Oct 2012 13:37:16 GMT

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?

Re: MySQL and bulk

Anonymous — Wed, 17 Oct 2012 13:24:44 GMT

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.

Re: MySQL and bulk

Anonymous — Wed, 17 Oct 2012 13:33:43 GMT

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.

Re: MySQL and bulk

_AnonymousUser — Fri, 14 Jun 2013 12:46:08 GMT

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.

Re: MySQL and bulk

Anonymous — Fri, 14 Jun 2013 18:24:27 GMT

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

Re: MySQL and bulk

_AnonymousUser — Mon, 17 Jun 2013 06:00:17 GMT

hi shong,

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

Re: MySQL and bulk

_AnonymousUser — Wed, 19 Jun 2013 07:24:40 GMT

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