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

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 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog