Retrieve from database vs by association

5
I have a microflow where I retriebe an entity by association. This takes nearly 100 ms. This seem to be too long for me and I triy to retrieve it from the database which takes 40 - 50 ms. I'm wondering why it is faster to retrieve it from the database. When I look into the microflow engine output it looks like that the entity is retrieved from the cache when I retrieve it from the database. This should be quite faster in my opinion. Anyone has an idea? [1ea51087e4ff420] Executing activity: {"name":"Process.Request_ActionMenu","current_activity":{"inputVariableNames":["Request"],"returnVariableName":"BusinessGroupAsOrganisation","caption":"Retrieve Organisation by $Request/Delivering_Organisation","inputConstants":{"associationName":"Process.Delivering_Organisation","fromCache":true,"returnType":"NAP.Organisation"},"type":"Retrieve"},"type":"Microflow"}
asked
4 answers
4

In response to Tobias' comment "I added a second retrieve by association behind the first one and after it a retrieve from database. The retrieve times looking like: 41ms, 40ms, 36ms or similar. all arround 40ms and no strict order which one is faster"

I see, so you're expecting the next 'retrieve by association' to go faster right because you expect it was now added to the cache right? Not everything that is retrieved is put in the cache, especially in this case it wouldn't make much sense since you already have the object in the scope of your Microflow. When you change an object however (without committing), it will be put in the cache since it needs to keep the changes even after the Microflow finishes (and for example opens a form with the changed object). You could re-do your test and see how fast it retrieves after you changed the object (and thus added it to the cache).

Update: Retrieve by association simply means what it says, it will retrieve the object that it is associated to, by some association. This object could be in memory but could very well come from the database as well. I realize the terminology is not great, since the other option is called 'from database' which should really be something like 'retrieve by query' or whatever.

On topic of your expected behavior, that's what I would expect as well. I'm not sure how you are measuring these times? It might be hard with just the logging options, we'd like to build a new proper microflow profiler again sometime soon.

The Mendix cache can result in faster queries but it's more of a memory store where we also keep changes until they are committed. Considering many objects are just retrieved once (such as in a datagrid) and never actually changed, retrieved or committed again, it's not a big improvement to cache them. That said, we'd still like to implement a true cache for retrieval speed, but it's somewhere down the roadmap :)

answered
1

Don't know what happens. Cache should be faster, that is what it is built for. Some possible causes can be:

  • Limited cache memory
  • Many items in cache and suboptimal access algorithm
  • By unknown cause your object is dirty (changed) and after retrieving from cache is it retrieved from database. You can check that in the retrieve log.
  • You use slow association (access from both sides)

EDIT:

  • Is it not yet in cache (As Bas wrote)
answered
1

Hi Chris, thank you for answering.

I added a second retrieve by association behind the first one and after it a retrieve from database. The retrieve times looking like: 41ms, 40ms, 36ms. The problem also occur everytime for the same entities. Others are retrieved in 2ms from the cache. Because of that I don't think that point one, two or three you wrote above are the case. Point four is also not the case. It is a one to manny association.

answered
1

Ok, that explains the behaviour. But why are not all entities added to the cache? I mean in my case the entity I retrieve by association is still retrieved from the database before, because it is related to an entity I opened in the client. Is there a reason for? This behaviour slows down our application quite a lot.

What is a little bit wired for me is the following behaviour. I added a change after the first retrieve by association. Now I having a retrieve by association, a change, a retrieve by association and a retrieve from database. Now both retrieve by association are done in 0 ms. What I expected after your explanation that the first retrieve will be slow and the second one will be fast, because the entity is added to the cache after I did the change.

In my opinion the retrieve by association tries to retrieve the abject from the memory, which is for me the 'cache' as it is a normal Java apllication running. Can you explain what the mendix cache is instead and how it works?

Thanks, Tobias

answered