Update taking too much time

[Rathesh]

I am trying to build a job, which will insert or update record. Records count is roughly around 50000 records. Insert or Update option in tMSSQLOutput took too much time. So i have used tMap to identity the insert or update record, and then used two separate components, one for insert and one for update. If record count is less than 20000, then it is working faster. If it crosses that, update is taking too much time(around 30 min). Any other alternate solution for this?

[Anonymous]

For the update records, delete and insert them.

[Rathesh]

What are you referring? .I didn't understand.

[Prakhar1]

It means if a record is getting updated , then first delete the record and then insert that again instead of updating it.

[Rathesh]

That table is having references, I can't delete it every time. Can you suggest other solution.

[Anonymous]

Hi Rathesh
Generally speaking, performance depends on your data capacity and network bandwidth. If your database is installed locally, the performance is definitely faster than when you access a remote database. Have a try the following optimization to see if the performance can be improved
- Create a new DB connection on tDBOutput component instead of using an existing DB connection.
- Allocated more memory to job execution.
- Store the lookup data to disk instead of memory if you are using tMap to do join.

Regards
Shong