xDM

We have a case where we have a contract entity consisting of ~60 foreign key references to other entities and ~50 simple attributes.

For this entity we have a collection that visualizes 41 foreign keys and 18 simple attributes.

The amount of records for that entity is ~10 000.

Loading of the collection view takes longer than expected yet the infra side should be on a suitable level from performance point of view.

So the topics I'd like to discuss about are:

How much would utilizing built-in LOVs improve performance compared to LOVs created as entities?

Is the number of columns or the amount of data affecting more on the loading speed of the collection view?

Is there some other ways to optimize this to make it load faster?

Do you have any row-level security implemented on that collection ? Also, do you have lots of sortings ?

On my side I had similar performance issues on entities not very huge (~5000 records), and the two main causes were :

• row-level security implemented thanks to functions in the database, called in semarchy model to filter the collection
  => I replaced this "database function" mechanism with another RLS mechanism based on model variables, and performance increased like 10x to 20x faster (at least). 
• too much sorting in collections => it's recommended to keep only a few sortings. I you have lot of columns in your collection and sorting applied to lots of them, it will decrease performance a lot