topic Re: Update with tMSSQLOutput very slow!! in Talend Studio

Update with tMSSQLOutput very slow!!

snow157 — Sat, 16 Nov 2024 14:13:12 GMT

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?

Re: Update with tMSSQLOutput very slow!!

Anonymous — Fri, 09 Jan 2009 21:40:17 GMT

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 ?

Re: Update with tMSSQLOutput very slow!!

Anonymous — Fri, 09 Jan 2009 21:41:17 GMT

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

Re: Update with tMSSQLOutput very slow!!

Anonymous — Thu, 15 Jan 2009 21:52:23 GMT

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