When to use association tables instead of direct associations?

0
Since 10.21 we can use direct associations. According to the documentation using these direct associations result in "Queries are not always faster, and might not be faster in your use case" (https://docs.mendix.com/refguide10/association-storage/)What would be a use case in which the direct associations are faster? (Or otherwise a better choice?)
asked
2 answers
0

Direct associations can be faster (or a better choice) mainly in simple 1-1 or many-to-1 relationships, especially when working with large datasets and read-heavy queries.

With a traditional association table, the database needs an extra JOIN:


FROM MyFirstModule$customer AS CUS
JOIN MyFirstModule$customer_account AS CUS_ACC 
  ON CUS.id = CUS_ACC.MyFirstModule$customerid  
JOIN administration$account AS ACC 
  ON ACC.id = CUS_ACC.administration$accountid

With a direct association, this becomes:


FROM MyFirstModule$customer AS CUS
JOIN administration$account AS ACC 
  ON ACC.id = CUS.customer_account

This removes one JOIN and avoids scanning the intermediate association table. In large datasets, this can reduce I/O, memory usage, and query execution time.

However, the benefit depends on the use case:

  • It’s most effective for 1-1 or many-to-1 relationships
  • It does not apply to many-to-many
  • It cannot be used if the association itself needs extra attributes

So direct associations are generally a better choice for simpler models and read-heavy scenarios, but they are not always faster in every situation, which is why Mendix documents it that way.

answered
0

Hi Pieter Scherphof


Direct associations are faster (and better) in these specific


use cases:


1. High-volume data retrieval The biggest win. Traditional association tables generate SQL with additional joins through an intermediate table, increasing query complexity. With direct associations, the join is simplified to a direct foreign key reference, which is easier for the database engine to optimize. Squad-apps In practical terms, retrieving 100,000 assets through a traditional association table took significantly longer Squad-apps compared to direct associations.

2. Many-to-one relationships you query frequently If you constantly retrieve a child object and need its parent (e.g. OrderLine → Order), direct associations avoid the extra join table lookup entirely — the foreign key is just a column on the same row.

3. One-to-one relationships Direct associations simplify 1-to-1 relationships significantly as the foreign key only lives in one table Squad-apps, whereas the old approach required a separate join table even for a simple 1-1 link.

4. Smaller database footprint No additional table in the database is needed to store association information, which benefits the size of your database


When it won't be faster (the doc's warning explained):

If you're retrieving from the non-owning side of the association (i.e. traversing "backwards"), the database still needs to do a lookup — so you won't see the same gain. The improvement is most visible when querying from the owner side with large datasets.


Direct associations are the recommended default going forward for all new 1-* and 1-1 associations, especially on entities with high data volumes. The documentation's caveat just means don't assume it fixes all performance issues — test with your actual data volume.

answered