Mendix Iterations Best Practices from a Painter’s Perspective

Why is it not a good idea to put database transactions inside of a loop?

It is often said that it is not a good idea to put database transactions inside of a loop (In Mendix iterations). But why is that actually?

Let’s take a real life example. Imagine you need to paint a wall. The repetitive job is as follows:

1. Open can with paint
2. Mix the paint properly
3. Dip the roller in the paint
4. Put in on the wall.

Those 4 steps will be repeated as long as the wall is not done.

The source for this job is paint and we need to buy (retrieve) it at the store. When you go to the store and buy a can for one iteration (executing the 4 steps), you have to get back to the store every single iteration. This way you waste a lot of time on retrieving paint from the store.

In addition, in The Netherlands we cannot just put empty cans in the garbage, but we have to bring it to a special department of the city. So imagine, this will be step 6; bring empty can to the city.

This gets us the following iteration

1. Go to store and buy paint
2. Open can with paint
3. Mix the paint properly
4. Dip the roller in the paint
5. Put it on the wall.
6. Bring empty paint can to city department

So now we have two stops, once at the store and once at the city department. It’s very likely that you will run out of time, and you will not finish the wall on time (TimeOut).

So instead what do you do? Well, I think it’s obvious. You retrieve enough the paint for all the repetitive paint actions (iterations) at once before you start painting. In addition you put all the empty cans next to each other on the other side of the room (build a sequence; list) and once you are done painting, you bring all the cans in one time where they belong (eg commit).

Now at last there is one thing left to mention; how much can I retrieve at once? Well, let’s have another real life example. When you buy groceries, you buy a batch for mostly a week and not a year. This is mainly because you don’t have enough space on your end (client) for a whole year of groceries, let alone the money. The refrigerator and the freezer, determine how much you can process and store. Once the groceries batch is processed into nice meals, it’s time to retrieve a new batch of groceries at the store that fits in your storage space.

Conclusion; make sure you retrieve what you need for completing the iteration and put the processed items where they belong once you are done. At the same time, make sure you can process your retrieves without ignoring the storage and processing capacity on your end.