Optimize Data Handling

In the context of a microflow, when utilizing a count operation on a list of products, Mendix Documentation indicates that the generated SQL query may not be optimized to a SELECT COUNT. Instead, the dataset is retrieved, and the count is obtained by looping through the list at Mendix Runtime. The optimization into a SELECT COUNT is only feasible when the list being counted is not used elsewhere in the microflow. If there is a subsequent change made to the list after the count operation, the query is not optimized. How does the compiler discern, during the counting of products in the list, that the list will be modified later in the microflow, particularly before encountering the change operation within a loop?
2 answers

This isn't an optimization at runtime. Mendix simply checks if the microflow has any other operations on that list during buildtime. In your case iterating the list is already enough.

Counting the list isn't a costly operation by itself. So in your case it doesn't really matter that it's not optimized to an SQL COUNT since you require the list content later on.

There are cases where you actually don't need all data in the list you're counting. Then it's better to first do retrieve+count and then retrieve the actual data you need separately.


Good question: The performance best practices also refer to the same.

The only thing I can imagine is that it is related to this statement:

"Microflows are executed as a single all-or-nothing transaction, which means that if any part of the microflow fails, the entire transaction is rolled back. Nanoflows, on the other hand, execute progressively with each activity, so if one activity fails, only that activity is undone" 

So, the microflow "knows" the list will be updated/used before the retrieve action is actually executed.