topic Re: Insert/Update versus table-file-table in Talend Studio

Insert/Update versus table-file-table

Anonymous — Wed, 07 Jun 2017 08:16:04 GMT

Hi ,

I have been using insert/update to update or insert a table in mysql from sql server. The job is set up as a cronjob. The job runs every 8 hours. The number of records in the source table is around 400000. Every 8 hours around 100 records might get updated or inserted.

I run the job in such a away that at the source level, I only take the modified runs between the last run and the current run.

I have observed that just to update / insert 100 rows the time taken is 30 minutes.

However, another way was to dump all of the 400000 in a file and then truncate the destination table and insert all of those records all over again. This process is done at every job run

So, now may I know why does insert/update take so much time?

The job design for insert/update is as follows

Thanks in advance

Rathi

Re: Insert/Update versus table-file-table

TRF — Wed, 07 Jun 2017 08:35:22 GMT

Hi,

30 minutes to insert/update only 100 records seems very long.

Do you know elapsed time for each task?

How long for the tMSSqlInput, tMSSqlRow and tMysqlOutput?

What appens in tMSSqlRow?

Re: Insert/Update versus table-file-table

Anonymous — Wed, 07 Jun 2017 08:45:05 GMT

Ahh, I do not know the elapsed time for each component.

The tmmsqlrow updates the time in a table about the end of time of the job

Thanks

Rathi

Re: Insert/Update versus table-file-table

Anonymous — Wed, 07 Jun 2017 09:09:26 GMT

Hi TRF,

Are there any other kind of statistics that I need to gather to help explain the problem?

Thanks

Rathi

Re: Insert/Update versus table-file-table

TRF — Wed, 07 Jun 2017 09:14:34 GMT

As you have a tStatCatcher subjob, just check the option "tStatCatcher statistics" on Advanced settings for all critical components.

If possible, compare the elapsed time when doing operations outside of Talend.

Re: Insert/Update versus table-file-table

Anonymous — Wed, 07 Jun 2017 09:25:32 GMT

Possible reasons for this (given what you have said) are....

1) You are not updating on a primary key or an indexed key. Do you have any indexes on your insert table? You may only update/insert 100 records but you say you are actually processing 400000 records. That is potentially n(number of records in your source table) x 400000 comparisons (possibly full table scans).
2) Do you have insert/update triggers on your insert table? These will add some latency.....but I wouldn't expect this to be your cause if I am honest.