topic performance of tMSSqlBulkExec in Talend Studio

performance of tMSSqlBulkExec

Anonymous — Sat, 16 Nov 2024 12:04:10 GMT

Hello there
I've been using Talend Open Studio for a couple of weeks, and it's been doing alright so far
But now I have some issue with tMSSqlBulkExec component
There were a few other times that I've used this component, and it worked fine
This time, I've used it to transfer a 1GB csv file into a database
It took about 30 mins to finish, and also increased the log file of the target DB by 11 GB
(the target DB was set to Simple recovery mode)
This obviously does not look like BULK INSERT performance, rather it behaves
like regular INSERT transaction
Is there anything I can do to improve its performance?
I believe that it should have similar performace as BCP, and it takes only 2~3 minutes
to import the same file with BCP utility
I am using OnSubjobOk trigger to invoke tMSSqlBulkExec after the subjob that creates
the csv file
"Action" is set to "Bulk Insert" from Advanced settings tab
Thanks in advance!

Re: performance of tMSSqlBulkExec

Anonymous — Sat, 30 Mar 2013 12:48:32 GMT

Hi
Which version are you using? Can you please upload a screenshot of your job design?
Shong

Re: performance of tMSSqlBulkExec

Anonymous — Mon, 01 Apr 2013 17:35:10 GMT

I am using version 5.0.2, and here is the screenshot of the job
It is a fairly simple design

Re: performance of tMSSqlBulkExec

Anonymous — Thu, 04 Apr 2013 17:13:06 GMT

I've tried to copy BULK INSERT statement from the Java source code, and run it on my SQL server directly
As the result, it takes almost the same amount of time as the Talend job (maybe 2~3 mins less)
It seems like the query was not run minimally logged.. If I add TABLOCK option to the BULK INSERT statement,
then it takes about 10 mins, and there is no increase in log file size
So, guess there is no other way to improve BULK INSERT performance within TOS..?
Perhaps it would be a good idea to allow users to include TABLOCK in the BULK INSERT, without having to modify Java code