Retrieve is very slow

Got an entity XYZ containing 5 million objects being owner of five associations (1-1 and *-1, so always only one associated object, never many). Whenever doing ’Retrieve XYZ’, Mendix also retrieves the association-objects of the owned association-tables . These retrieves are pretty slow: over a second. This surprises me, because those retrieves are on the association table, which only contains the XYZ-ID and the otherID. Should not take a second should it? Also, is there any way to only retrieve XYZ, without these five mentioned associations?
1 answers

sounds pretty tricky. Ideally mendix should give developers the freedom/option to do shallow retrieves. Until that day comes, I can think of the following:

- add more memory to database so that the retrieve would be speedy. The important part here is that the memory must be large enough to fit all the relevant tables; ideally all your database data to give better speed guarantees.

- I suppose you have considered already: break the association and do it manually. You probably don't want to do this. But in certain scenario's this can be an option.

- At specific flows/screens use database connector to do custom retrieves avoiding the auto retrieval of associated objects.

- as a final idea: introduce an extra layer (kind of like sharding) to reduce the number of auto-retrieved associated objects. This is like refactoring your domain model to overcome this performance issue.