In my application is an entity with 80,000 objects in it - Products. The application also has an associated entity - Category - with 200 objects in it. I built a microflow to change the subcategory of some of the Products. The problem I am having is that the performance of this microflow is extremely slow. I have limited the microflow so that it only changes 500 records at a time. Running this on the MendixCloud, the microflow takes about 2.5 seconds to change each object (22 minutes to change 500 objects). What steps can I take to improve performance of this and similar activities? Any tips or pointers?
Performance is these cases is mainly determined by your database queries. Having a microflow that takes 2.5 seconds to change 1 object seems way to slow to me.
Things you can take into account to optimizing your queries are:
Build your xpath so that it first limits your set as much as possible before joining it to another table. e.g. [status = 'complete' and General.OrderLineOrder = $order] instead of [General.OrderLineOrder = $order and status = 'complete']
Add indexes to the attributes you use to retrieve your objects.
Instead of commiting each object, change your objects first and then commit the list.
Pieter van de Braak
Normally updating association is fast.
You can also take a look at loops. Limit the retrieved list in stead of getting all and have a split inside the loop. Looping thru 200 records is slower than getting 20 objects you want and update them.
The other point is the transaction size. Is the 500 update slower than 500 times one update? Server memory may be in the way. Monitor that.
Maybe your database needs truncating, reindexing, vacuum cleaning, rebuilding?
Chris de Gelder
Add an autonumber attribute to your product entity and sort by it on your retrieval.