Update data activity is taking too long to update bulk amount of data in recipient table.

[#1]

Update data activity is taking too much time to run in Adobe Campaign while updating bulk amount of data in recipient table. Around 30 mins for updating 900000 number records. The table contains around 30 M records. The reconciliation key is having an index on it (it is the primary key column).Is there any scope for improvement?

[#2 · Accepted solution]

Is your database in good health? Check the level of fragmentation of indexes and table and then decide if it needs a vacuum.

Always perform these bulk updates during a period of low activity as it can lead to page level locks causing blocking queries.

Check if there aren't too many indexes on this table as every insert/update also requires update or the index. If the index is too bloated additional slowness will be observed.

[#3]

Hi Vipul,

This is on cloud.

Regards,

Debabrata Tripathy

[#4]

If you instance is on-prem a good this will be check at infra level.

Disk IOPS is another factor. If the disk is responding slow nothing much can be done from the application standpoint.

Check if there is latency over the network just in case application and database are on different servers.

Use a better quality hard disk to see the performance gain.

[#5]

Hi Vipul,

Thanks for your input. I am doing it in low activity period only and other than out of the box indices, there are two more indices in the extended schema and I am only performing update. Next thing is the DB health, let me check that.