OData expand + CDS/AMDP = bad performance

[AlexNecula]

I have a CDS with multiple associations which uses a table function with AMDP. This is referenced inside an OData service. Everything is pretty basic.

There are some fields that require expanding in order to retrieve the texts. These are simple 0..1 association based only on one field.

Whenever data is retrieved via smart table, the main CDS is queried for each line and each field that is expanded. So, for example, I have 3 fields that require expanding and 8 rows retrieved, that means the main CDS is queried up to 24 + 1 (from the main request) times. As you can imagine, this translates in very poor  performance as the number of rows grow.

The cause is that for each association, the SADL joins it with the main CDS. So if the main CDS looks like this:

define new entity ZI_MainCDS
as select from ZI_TableFunction

association [0..1] to I_Plant as _Plant on _Plant = $projection.Plant
{
  key id as Id,
  ...
  @ObjectModel.foreignKey.association: '_Plant'
  plant as Plant,
  _Plant,
  ...
}

The SADL first reads from the ZI_MainCDS. Then, it expands for the Plant text and reads from I_Plant but does inner join with ZI_MainCDS as well to ensure the value exists. Unfortunately, this means a second query on ZI_MainCDS that I don't want. This repeats for every association.

Is it possible to prevent joining the main CDS for each association? I know that I could implement GET_EXPANDED_ENTITYSET and do everything manually but that would take a lot of time and really defeats the purpose of using fiori elements.

[junwu]

you checked the sadl code regarding your finding? or it is all your imagination....

[AlexNecula]

Hi @junwu , haha good one, but yes I checked the code

You can see below a concrete example. ZI_User is an association while ZC_ChangeStatusApproval is the main CDS. This select is triggered when trying to read the name of the user from ZI_User based on ZC_ChangeStatusApproval.CreatedBy

Also, the breakpoint in the AMDP is stopping tens of times because of this for a single request in the Smart Table so the query is indeed used.

[tanhsbhp]

One way we did was changed the oData from Co-deployed to RFC based as we realized that when in Co-deployed mode the expand call are trigger in sequence but using RFC based service it will call the expand in parallel.