Update with tMSSQLOutput very slow!!

Two Stars

Update with tMSSQLOutput very slow!!

Hi,
I tried to do updates on a Date field with an input flow which had 1000 rows on a table with 2138485 records.
I used 2 ways:
First way:
tMap------------->tMMSQLOutput
I used the 3 primary keys of my table to do the update.
Result => this way is very slow, the job treat only 1 update per second
Second way (dirty way):
tMap------------->tFlowToIterate--------------->tMSSQLRow with the SQL request below
"UPDATE EEEA_DLVPLANSALESLINES
SET
ATW_DATE = GETDATE()"
WHERE
EEEA_DLVPLAN_ID ='"+(String)globalMap.get("EEEA_DLVPLAN_ID")+"'
AND SALESID='"+(String)globalMap.get("SALESID")+"'
AND DATAAREAID='"+(String)globalMap.get("DATAAREAID")+"'"

Result => this solution is 10x more faster.
What I'm doing wrong with the tMSSQOutput component?
Had someone already encountered this kind of trouble?
One Star

Re: Update with tMSSQLOutput very slow!!

We have exactly the same problem.
We make a lot of update with tMSSqlOutput and the performance are awful.
Did you get a solution to your problem ?
One Star

Re: Update with tMSSQLOutput very slow!!

And we have also the same problem with insert operation with big volumes
One Star

Re: Update with tMSSQLOutput very slow!!

Hello together,
talking about performance problems is not so easy if you give no detailed information about your job / infrastructure. And in the end we could only give you some hints or points to check but this will not supersede a detailed local analysis.
Here some points to check / optimize:
* Do you open the connection only one time?
* Did you check your keys / indices?
* Are there any network problems to access your db server?
* If you need to insert big volumes, may it be possible to supress transactions or remove (temporary) indices?
I hope this will help you a little bit.
Bye
Volker

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Why Companies Move to the Cloud: 7 Success Stories

Learn how and why companies are moving to the Cloud

Read Now