Insert/Update versus table-file-table

[Anonymous]

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

[TRF]

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?

[Anonymous]

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

[Anonymous]

Hi TRF, 

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

Thanks

Rathi

[TRF]

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.

[Anonymous]

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.