tDBOutput extremely slow

[SheldonMarumoto]

I have a process that pulls from a MySQL database, compares to existing entries in a destination database, and then Inserts or Updates depending on existence.

The Updates are extremely slow, along the lines of ~2 rows/sec. This is fairly consistent regardless of batch size or commit size. Destination has an appropriate index for the key. 

When the job is modified to send Updates to a tLogRow component, output jumps to nearly 8k rows/sec.

Any ideas why this tDBOutput component is going so slow?

[Dave_Simo]

Hello @SheldonMarumoto 

This video does a good job of explaining how to use the tdbOutputBulkExec component for bulk insert/update.

https://www.youtube.com/watch?v=EQvAZPdHOmQ&t=12s

Best Regards

[SheldonMarumoto]

Many thanks!

[SheldonMarumoto]

Turns out the SQL Server Bulk Load command only works server-side. It cannot access a file on a local machine (which is where my locally-executed Talend job is storing the generated .txt file). My SQL Server instance is hosted on AWS RDS. Is it possible for a local running Talend instance to point the data file at an S3 bucket, and how would that be set up in the "File Name" section of the tdbOutputBulkExec component?

[Dave_Simo]

Hello @SheldonMarumoto 

I tested the update with a local file and it works after that it is on a Postgre database.

Normally even if your SQL Server database is hosted on AWS RDS, the job should be able to read the file where it was created.

If you have a subscription to the paid version of Talend, it would be more practical to open a ticket with Qlik/Talend support or try the update with a tELTOutput.

For information

Best Regards