ABAP CDS performance - usage of multiple association

Hello,

I'm working on a ECC system on HANA. There is no standard ABAP CDS in that system but I build my own data model for sales orders.

To keep it simple, let say I have

- ZI_SalesOrder view for sales order header

- ZI_SalesOrderItem view for sales order items

- ZI_SalesCondition view for sales order item conditions (table KONV)

Let's focus on sales order item and the association to conditions. It is defined like this :

define view ZI_SalesOrderItem 
as select from vbap 
inner join vbak on vbak.vbeln = vbap.vbeln
inner join vbup on vbap.vbeln = vbup.vbeln
                and vbap.posnr = vbup.posnr

association [1..1] to ZI_SalesOrder as _SalesOrder on $projection.SalesOrder = _SalesOrder.SalesOrder

association [*] to ZI_SalesCondition as _Conditions 
on $projection.ConditionId= _Conditions.ConditionId
and $projection.SalesOrderItem = _Conditions.ConditionItem
...

And condition view look like this :

define view ZI_SalesCondition 
as select from konv 
{
key konv.knumv as ConditionId,
key konv.kposn as ConditionItem, 
key konv.stunr as ConditionStep,
key konv.zaehk as ConditionCounter,
    konv.kschl as ConditionType,
    konv.kwert as ConditionValue,
    konv.waers as ConditionCurrency,
    case 
        when konv.krech = 'A' then div(konv.kbetr, 10)
        else konv.kbetr 
    end as ConditionAmount,
    case 
        when konv.krech = 'A' then cast('%' as abap.char( 3 )) 
        else konv.waers 
    end as ConditionAmountUnit 
}
where konv.kappl = 'V' and konv.kinak = ''

Now, on top of this data model, I created a consumption view that I use in my Fiori application. In this view, I need to display items and some conditions values. For example, I need MWST condition value in one column.

This is how I use the association :

//Tax
Item._Conditions[1:ConditionType = 'MWST'].ConditionAmount as TaxRate,
Item._Conditions[1:ConditionType = 'MWST'].ConditionAmountUnit as TaxRateUnit,
Item._Conditions[1:ConditionType = 'MWST'].ConditionValue as TaxValue,

Everything works fine... BUT it is slow. VERY SLOW. In production environnement, it can take 8 or 9 seconds just to display a single sales order with 4 items.

I used ST05 tcode to get a trace plan and opened it in eclipse. I'm not used of this tool, but I understand that the database read all the record with condition type MWST and then compare it to the condition ID of the selected sales order, instead of searching the logical way, get the condition ID from the sales order and then get the corresponding records in KONV.

What make me think this :

As you can see, 15 905 812 records selected in condition table ! And if I look at the details :

I can see that all of this is just for KONV condition that I could get super fast if it was from the condition ID (KNUMV).

My question is : what can I do about this ? Is there something I can do in the CDS view or on the database ?

I tried that in the CDS, but no change :

Item._Conditions[1:ConditionId = ConditionId and ConditionItem = ConditionItem and ConditionType = 'MWST'].ConditionAmount as TaxRate,

Really need your help... Thank you.