How would you handle a single change to 100.000+ records?

1
I just released a new version of one of our applications that required all objects of a single entity to be updated. I had a simple button that would retrieve all objects, changed and then commit that list. That microflow ran for over 20 minutes and had me wondering if I could optimize this, either as a background task, reduce memory usage or a way to reduce time to complete.  So, how would you optimize or handle an action that has to change 100.000+ records at once, other than retrieve, change and commit list? - Alternatively, in the past I built a basic piece of logic that retrieves a subset of all records, updates and then refreshes  the parent entity to restart the datasource that retrieves a subset of records, so it iterates over a different set of records, thus batch-update. Although this worked very nice, doing this extra effort felt redundant.
asked
4 answers
2

Hi Sander!

 

Check out these blog posts about handling large database tables

https://stephanbruijnis.github.io/a-Mendix-blog/content/post/fast-manipulation-of-large-tables.html

https://medium.com/mendix/five-tips-for-working-with-large-database-tables-in-mendix-170210f6f6fd

I recommend doing the batch updates :)

answered
2

Just to complete this thread :-)
You can also build some recursive logic using this Module.

There is a demo app having an example that deletes records. Updating them is the same. The benefit is, that each batch runs in it’s own DB transaction (Just as with the process queue logic). That makes it independent from the overall amount of objects you need to update.

answered
0

I concur with Lukas’ recommendations.  Another perhaps lesser known option is through the use of the Process Queue module found here.  It allows you to trigger any functionality in parallel and queuing it.  Just beware of the caveat on using it on horizontally scaled environments.

answered
0

Sander, if it had to be run only once i would use a microflow an change 500 records at the time, commit that 500, do an end transaction and a start transaction and do the next 500. That will do for a quick change. If it should be used more than once i would use the queueue module

answered