how to get max performance using upsert mode?

[SURA]

Hi there

I developed good number of jobs and I Iam happy the way how I developed the jobs. But when I develop a job using "upsert" mode, I am not having the full satisfaction. Jobs are running 1000+/- rows per sec.

Once I raised a PMR with IBM in this relates. But the discussion lead the topic in to performance and end up with no result.

I do agree, this is related to performance, but I wish to know is there is any thumb rule do we need to follow in Datastage point of view?

Or

Is there is any other clever approach to achive this in a very good manner?

Please dont reply the factor of Network, Hardware, DB, No of records, Index, Partition etc.

[vmcburney]

So which transactions are slow - inserts or updates? Have you tried pumping through just updates and just inserts to compare the speed? What is the mix of inserts to updates? Is it worth diverting inserts into a bulk load path? Are you doing update then insert or insert then update? Have you experimented with array size/transaction size?

Since this is a performance problem on just one table you are going to have to tell us whether there are primary, foreign or index keys on that table or triggers. I know Index is an out of bounds topic for you but this is database performance 101.

We can assume given your other jobs are running well that network, hardware and server sizing/partitioning are not the problem. Is there anything upstream of the database stage that could be slowing the job down? Aggregation stage? Transformation code?

This isn't a PMR for IBM - it's not their problem that this database table is slow.

[SURA]

Hi vmcburney


I will not go to off for a week. Will update the status soon.

Thanks for your reply.

[akarsh]

Hi Sura,

Did you found the resolution.

I am also facing the same issue in insert then update job.

[soumya5891]

Guessing that your data volume is high. Separate out the insert and update job. In the update job push down the update totally in the DB server in the following way.

create a temp table and then load it in bulk load mode with the updated records. In the after sql write a query to update the target table with the help of this table. This may increase the performence