cancel
Showing results for 
Search instead for 
Did you mean: 

ABAP CDS performance - usage of multiple association

Louis-Arnaud
Participant
0 Kudos
4,871

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.

FredericGirod
Active Contributor
0 Kudos

Did you try to see the SQL statement generated by your CDS ? and check with the same code in ABAP/SQL consol how the system react ?

Louis-Arnaud
Participant
0 Kudos

Well, the SQL statement is not very interesting at it is only a Select on the CDS View. Yes the system react the same way.

What I did is creating a trace with ST05, download request SQL explaination and opened it in eclipse.

Accepted Solutions (0)

Answers (3)

Answers (3)

lbreddemann
Active Contributor

Based on the information provided, it is not possible to properly understand what causes bad performance.

What I see is this:

  • the "Search on Table" on KONV takes 275ms including all four equal predicates (MANDT, KAPPL, KSCHL and KINAK)
  • Combined the four predicates only filter a minimal share of records (15,947,186 - 15,841,828 = 105,358 -> 0.66%).
    For practical means, these predicates don't filter the KONV table
  • The bulk of data is handed further up the processing tree. In the projection list of ZI_SalesCondition rather expensive per-record computations are performed for ConditionAmount and ConditionAmountUnit.
    This leads to a materialization of this bulk of data (see the Create Temp table plan operator)

Based on that it's likely that the top-query does not provide and efficient filter conditions that could reduce the amount of data (nearly 16 Mio rows is typically far too much for a client query result set).
In addition, it should be checked if the calculated columns are actually required for the top-query (e.g. if the top-query is a simple SELECT * FROM... then specifying just the relevant columns could help a lot).

As stated in the first sentence, this is an incomplete assessment as a lot of information (top-query, explain plan, full plan viz trace, full CDS sources, table statistics, ...) is missing, However, it should be clear from the listed observations that "the" performance of a data model is not solely depending on how this model is designed.

Instead, it's a based on

top-query + data modeling + data in tables

Hope that helps nevertheless.

Louis-Arnaud
Participant
0 Kudos

Thank you for this very detailed answer.

you are right that there is no column selection on the top query. This is done on purpose, as the CDS is designed to get all the fields needed in the fiori application.

But the top query should be very restritictive, because it only requests 1 sales order. So we have the key field of VBAK, which contains the key field of KONV (KNUMV).

If I decompose this query in 3 query, the response comes very fast.

This works fine :

SELECT knumv FROM vbak WHERE vbeln = 'XXXXXX'
SELECT posnr FROM vbap WHERE vbeln = 'XXXXXX'
SELECT kwert FROM konv WHERE knumv = (knumv gotten from vbak) AND posnr = (posnr gotten from vbap)

But the join is very slow :

SELECT kwert
FROM VBAK
INNER JOIN VBAP on vbak.vbeln = vbap.vbeln
INNER JOIN KONV on vbak.knumv = konv.knumv and vbap.posnr = konv.posnr.
WHERE vbak.vbeln = 'XXXXX'.

My data model should be ok, because the condition value that I get is correct. So the join is well defined in the CDS through the association...

I don't know if I can get the real generated SQL query to understand what is wrong.

lbreddemann
Active Contributor
0 Kudos

To clarify, with top-query I was referring to the actual query that is used in the application and not the CDS view.

Concerning the de-composition approach: the MANDT field is missing in the join conditions between these tables which may lead to execution plans that don't use existing indexes.

Of course, without an explain plan/plan viz for this join, all we can do is guess why it's slow.

former_member642751
Discoverer
0 Kudos

We have some issues with field Mandt in ours cds, I am not aware about tables you use, but try to add it if it exist in yours tables

juan_suros
Contributor
0 Kudos

I always use the link from VBAK-KNUMV to KONV-KNUMV in designs like this one.

Can the answer to your question be that simple?

Louis-Arnaud
Participant
0 Kudos

Thé link between vbap and konv is defined in the association itself.

The answer of the request is correct, there is only a performance issue.