One Star

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?
3 REPLIES
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