Update activity of 24M records taking 3 days

Question

One of our workflows, started on 08/28 and we estimate it is going to finish on 01/09, 04 days after it started.

It loaded 24M records and it has to update all of them.

The time is spent on the update activity that has 200,000 as batch size. It targets the recipients table.

Is this normal? Is there anything else we can do to improve it?

Manoj_Kumar · Accepted Answer

Hello @god_prophet
Here are things I would look at to optimize the WF:

What fields are actually updated and remove the fields where data is not changing
Can this be changes to incremental load instead of full load?
I would change the batch size to something lower
Do you have data manipulation happening with expression in Update data activity?
Are there any conditions in "Taken into account" section of update data activity?
Use Primary /Foreign Keys in the reconciliation key for updating the data.

MeitMedia · Answer

Hi @god_prophet ,

The workflow duration you mentioned, running from 08/28 and expected to finish on 01/09, indicates a significant processing time, especially when dealing with 24 million records and updating the recipients table. Here's some suggestions for improvement:

1. Batch Size Considerations: The batch size of 200,000 is relatively large. While it reduces the number of batch executions, it also increases the load on the database, potentially leading to locking issues or higher resource consumption.
Recommendation: Try reducing the batch size to something like 50,000 or 100,000. This can lead to more frequent but faster executions, as smaller batches typically lock fewer records and reduce contention.
2. Database Indexing: Ensure that the fields being updated in the recipients table are indexed. Without proper indexing, the database might perform full table scans, significantly slowing down the update process.
Recommendation: Review the fields targeted by the update activity and confirm that appropriate indexes are in place. Consider adding indexes temporarily if needed.
3. Workflow Optimization: Parallelization: If your server has sufficient resources, consider splitting the workload across multiple workflows running in parallel, each handling a subset of the data. This can drastically reduce processing time.
Filtered Updates: If possible, apply filters to minimize the number of records being updated. For example, update only the records that have actually changed rather than all 24 million.
4. Database Maintenance: Large-scale updates can cause database fragmentation or impact performance over time. Ensure regular maintenance tasks like reindexing and optimizing are scheduled to maintain optimal database performance.
5. Monitoring and Logs: Monitor the database and workflow logs to identify any specific bottlenecks or issues during the update process. This can help pinpoint whether the delay is due to database performance, workflow logic, or other factors.
6. System Resources: Ensure that the system resources (CPU, memory, I/O) are not being maxed out. If they are, consider scaling up the resources temporarily to handle the heavy processing load.

While updating 24 million records is intensive and can take time, adjusting the batch size, ensuring proper indexing, and possibly parallelizing the workload should help improve performance. Keep an eye on database and system resources to ensure they are not becoming bottlenecks.