Bulk Updates - Recommendations for Better Process

0
We have several process, where we want to bulk update a single value, such as changing all email addresses on a prod/dev data refresh or flipping a 'sent' flag to Y.  Currently, we're doing this with a microflow that loops through all our Accounts (about 75k) and makes the update, using offsets and committing every 1000 records. Running the update on email addresses takes almost 4 minutes for each 1000 and runs 4-5 hours on whole.  Are there other ways to do mass data updates that don't require looping through in a microflow?  Ultimately we just want to - -  update Account set EmailAddress = 'testemail';  
asked
4 answers
5

You should never commit in an iterator, you should commit the list post iterative process.

 

Also it doesn't appear that you're doing the retrieve with limits on offsets correctly check out this blog I wrote way back when: https://www.mendix.com/blog/limits-and-offsets/

answered
3

Good to know as well is that, even though you nicely commit those 1000 records at a time in that MF, Mendix will NOT entirely commit to the database unless the MF has finished (because a failure in the MF should still give Mendix the option to do a FULL rollback). Meaning your cached objects will skyrocket quickly and the MF will become slower and slower.

If performance is the issue, there are two options, both having to do with the fact that a separate Java transaction will be started and true commits are done:

  1. Add Process Queueing which will call the MF with a 1000 records each, so finalizing a MF after those 1000 records have been committed;
  2. Add an EndTransaction and StartTransaction Java action from community commons after the commit of 1000 records and before the new retrieval with offset and limit.

 

If time is of no issue, forget about it ;).

Regards,

ivo

answered
2

Hi there,

Like Marc said, commiting within a loop should not be done. Always commit the whole list, after you have changed the values you want.

Next it might be useful to add an index on the attributes you are using to retrieve the accounts. This really can boost your performance.

Kind regards.

answered
2

It's also worth checking possible EventHandlers on your object and the need to have these fired off in your script/commit. Maybe these events are not relevant in this case and you could speed up the process with a (batch) commit without events!

answered