Hi all, In my app we add records to an individual to make sure they’ve done a particular task. We have a microflow that runs as a scheduled event once an hour to check if the task has been done. If nothing has been done for 24, 48 or 72 hours, notifications will be generated to the required people within the app and in some cases, an email is sent (these are custom options for the users). We have about 300 people this check runs for and eventually it will increase to 4,500 people. The issue I am having is that when this process runs it takes ages, there are a lot of retrieves involved and it maxes out the database CPU, causing critical alerts and generally slowing the app down. The emails are set up to queue rather than send immediately which is what I thought was taking so much time. But the microflow can still take over an hour to run. Does anyone have any advice or best practises for running checks like this? The microflow will currently: Retrieve all the people who have this option enabled Iterate over those people to check; 2a. Are they new? If they have not started adding records for the task that is being checked they don’t need to be notified. They only start getting notified when they’ve added at least one task. 2b. Has the task been completed at least once in the 72 hours? If yes, it will check 48 hours and then 24 hours. If it says yes for 24 hours it will move on to the next iteration. If it hits no for any of the time periods, the app will then retrieve all the people that need to be notified and send them a notification. There are two groups of people, so two retrieves here. I would share the microflow but model share is gone :( The microflow is quite large and I just wanted to know if anyone has done something similar, or knows of a better way that does not cause 100% database CPU warnings. There are a lot of retrieves in the microflow which I presume does not help, i’m just at a loss about a better way of doing it.
asked
Garion Swann
1 answers
3
The first thing to do is try to optimize the current logic, and there are two things that immediately come to mind:
In step 1, retrieve only people who are not new
In step 2b, retrieve all relevant tasks at once and check the time periods in memory
This will probably not help too much, but it’s a quick win. You then get into performance optimization, with strategies such as:
Time the retrieves: if they are slow, optimize constraints or set indexes
Create an easily queryable data structure to send the emails, update this data structure when a user completes a task and nightly (completing a task e.g. sets DoneInLast24Hours attribute to true, during the night you set DoneInLast48Hours to true if DoneInLast24Hours is true and set DoneInLast24Hours to false)
You can remove a lot of logic and retrieves by rethinking the problem and doing this in a single query: given a task, return all users who have not completed this task in the last 24 hours (and repeat for 48 hours and 72 hours): this leads to 3 retrieves per task you are checking, instead of three retrieves per user! A further optimization could be to use OQL, although this reduces maintainabilty due to not many Mendix developers having OQL experience.