Nested CDT takes too long to query data

Question

Hi everyone, 
 I'm having a multi layer nested CDT (Nested CDT that is a column in another nested CDT column of the main CDT that uses as data type) which is taking too long to query data and sometimes causes time out error. I would love to have some suggestion to optimise/ replace it with something else if possible. 
 Thank you

Stefan Helzle · Answer

Well, you will have to analyze why it takes so much time. Try to fetch only the data you need.

Shubham Aware · Answer

Deeper nesting can create an N+1 query problem and slow queries or timeouts.
Create a DB view with the required JOINs and map it to a single flat CDT. Then query that view with one a!queryEntity() call, using paging and only the fields you need.
For longer-term maintainability, consider Record Types with Relationships(Replace deep nested CDTs with flat related CDTs)

Harsha Sharma · Answer

Appian doesnt recommend to use Nested CDTs in general. So better to normalise the cdts using foreign keys would be the best approach, if feasible. Or even move to record type/ data fabric! 
 For time out, find the cause of the time out in query. E.g. try querying 1,10 or 100 rows as per need to find the root of the timeout issue. 
 Another suggestion is if you are querying to show data in a grid, which is why you might need to query multiple rows - query just the main record data (except nested cdts) and show them in grid. The relative nested data can be queried and shown in separate UI component for each row - thus nested data will be queried one for each row rather than multiple at once. Can discuss further for each of the above approaches, yet hope these general suggestions help.