SQL Join and CDS associations

[Louis-Arnaud]

Hello,

I created a couple of CDS views that I can use as data source in my OData service (transaction SEGW).

I have a data model with header and items. I created an association between items and a CDS view that contains materials and their prices from condition records.

My problem is that I am facing performance issue with the request when I want to retrieve items and their prices (it's fine if I don't use the prices association).

I created a trace in order to get the SQL request, and this is it :

SELECT
  RequestItemSet.REQUEST_NB AS REQUEST_NB,
  RequestItemSet.MATERIAL_LIST_ID AS MATERIAL_LIST_ID,
  BSA_X53X1.MATERIAL_LIST_TEXT AS MATERIAL_LIST_TEXT,
  RequestItemSet.MATERIAL_ID AS MATERIAL_ID, BSA_X53X1.MATERIAL_TEXT AS MATERIAL_TEXT,
  RequestItemSet.QUANTITY AS QUANTITY, BSA_X53X1.MAX_QTY_UNIT AS UNIT,
  BSA_X60X2.MSEHT AS UNIT_LONG_TEXT, BSA_X53X2.UNIT_PRICE AS UNIT_PRICE,
  BSA_X53X2.CURRENCY AS CURRENCY
FROM
  "SAPSR3"."ZERD_ER_ITEM" AS RequestItemSet 


  LEFT OUTER JOIN "SAPSR3"."ZERD_LIST_MATDB" AS BSA_X53X1 
  ON BSA_X53X1.MATERIAL_ID = RequestItemSet.MATERIAL_ID 
  AND BSA_X53X1.MATERIAL_LIST_ID = RequestItemSet.MATERIAL_LIST_ID 
  AND BSA_X53X1.SOLD_TO_PARTY = RequestItemSet.SOLD_TO_PARTY 
  AND BSA_X53X1.MANDT = '310' 


  LEFT OUTER JOIN "SAPSR3"."ZERD_MAT_PRICES" AS BSA_X53X2 
  ON BSA_X53X1.MATERIAL_ID = BSA_X53X2.MATERIAL_ID 
  AND BSA_X53X1.SOLD_TO_PARTY = BSA_X53X2.SOLD_TO_PARTY 
  AND BSA_X53X2.MANDT = '310' 


  LEFT OUTER JOIN "SAPSR3"."T006A" AS BSA_X60X2 
  ON BSA_X60X2.SPRAS = 'F' 
  AND BSA_X53X1.MAX_QTY_UNIT = BSA_X60X2.MSEHI 
  AND BSA_X60X2.MANDT = '310'




WHERE
  RequestItemSet.REQUEST_NB IS NOT NULL AND RequestItemSet.REQUEST_NB = '0000000001' AND
  RequestItemSet.MANDT = '310'
ORDER BY
  MATERIAL_ID ASC LIMIT 100 OFFSET 0 WITH PARAMETERS( 'LOCALE' = 'FR' )

This request is very time consuming (around 5 seconds), although it just get the items of a request (3 items for this example). I tried to changed a little bit the request and write a inner join instead of a left outer join which is more correct as the material number is always found in the material view :

SELECT
  RequestItemSet.REQUEST_NB AS REQUEST_NB,
  RequestItemSet.MATERIAL_LIST_ID AS MATERIAL_LIST_ID,
  BSA_X53X1.MATERIAL_LIST_TEXT AS MATERIAL_LIST_TEXT,
  RequestItemSet.MATERIAL_ID AS MATERIAL_ID, BSA_X53X1.MATERIAL_TEXT AS MATERIAL_TEXT,
  RequestItemSet.QUANTITY AS QUANTITY, BSA_X53X1.MAX_QTY_UNIT AS UNIT,
  BSA_X60X2.MSEHT AS UNIT_LONG_TEXT, BSA_X53X2.UNIT_PRICE AS UNIT_PRICE,
  BSA_X53X2.CURRENCY AS CURRENCY
FROM
  "SAPSR3"."ZERD_ER_ITEM" AS RequestItemSet 


  INNER JOIN "SAPSR3"."ZERD_LIST_MATDB" AS BSA_X53X1 
  ON BSA_X53X1.MATERIAL_ID = RequestItemSet.MATERIAL_ID 
  AND BSA_X53X1.MATERIAL_LIST_ID = RequestItemSet.MATERIAL_LIST_ID 
  AND BSA_X53X1.SOLD_TO_PARTY = RequestItemSet.SOLD_TO_PARTY 
  AND BSA_X53X1.MANDT = '310' 


  LEFT OUTER JOIN "SAPSR3"."ZERD_MAT_PRICES" AS BSA_X53X2 
  ON BSA_X53X1.MATERIAL_ID = BSA_X53X2.MATERIAL_ID 
  AND BSA_X53X1.SOLD_TO_PARTY = BSA_X53X2.SOLD_TO_PARTY 
  AND BSA_X53X2.MANDT = '310' 


  LEFT OUTER JOIN "SAPSR3"."T006A" AS BSA_X60X2 
  ON BSA_X60X2.SPRAS = 'F' 
  AND BSA_X53X1.MAX_QTY_UNIT = BSA_X60X2.MSEHI 
  AND BSA_X60X2.MANDT = '310'




WHERE
  RequestItemSet.REQUEST_NB IS NOT NULL AND RequestItemSet.REQUEST_NB = '0000000001' AND
  RequestItemSet.MANDT = '310'
ORDER BY
  MATERIAL_ID ASC LIMIT 100 OFFSET 0 WITH PARAMETERS( 'LOCALE' = 'FR' )

This new request is around 300 ms. Very much faster and result is the same.

If I check the PlanViz, it's clear that the problem is that the request with LEFT OUTER JOIN is retrieving all the prices although in the end only 3 rows are needed.

here is my CDS View, I tried to force the inner join but it doesn't work :

@AbapCatalog.sqlViewName: 'ZERD_ER_ITEM'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Dotations : poste des demandes'
define view Zer_Cds_Equi_Request_Item 
as select from zert_eq_requ as header
inner join zert_eq_requ_it as item
on header.request_nb = item.request_nb
association [1..1] to Zer_Cds_List_Materials_Db as material
on material.sold_to_party = $projection.sold_to_party
and material.material_list_id = $projection.material_list_id
and material.material_id = $projection.material_id
association [*] to Zer_Cds_Equi_Request_Item_Car as caracs
on $projection.request_nb = caracs.request_nb
and $projection.material_list_id = caracs.material_list_id
and $projection.material_id = caracs.material_id
{
key item.request_nb,
key header.sold_to_party,
key item.material_list_id,
key item.material_id,
    item.quantity,
    
    //association
    material[1:inner],
    caracs


}

Any idea on how to force the inner join in the generated SQL statement ?

[Louis-Arnaud]

Hello Horst and thank you for your answer.

What I now understand is that I can't change the behavior of the generated SQL request from the CDS View.

I'm using the association path directly in the mapping to data source in SEGW. And I don't see any option to force inner join there.

I only see 2 options :

- add the needed fields from the material association directly into main view (but then why create association if I can't use it ?) ==> just tried... Doesn't change anything

- create a new CDS view on top of it that retrieve all the required fields (there I can force the inner) ==> I guess this wouldnt change anything too.

Here is the new CDS, I force the inner join, but it is still very slow... And I don't see the join now as I just select the view.

@AbapCatalog.sqlViewName: 'ZERD_ER_ITEM'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'Dotations : poste des demandes'
define view Zer_Cds_Equi_Request_Item 
as select from zert_eq_requ as header
inner join zert_eq_requ_it as item
on header.request_nb = item.request_nb
inner join Zer_Cds_List_Materials_Db as material
on material.sold_to_party = header.sold_to_party
and material.material_list_id = item.material_list_id
and material.material_id = item.material_id
left outer join Zer_Cds_Materials_Price 
on  header.sold_to_party = Zer_Cds_Materials_Price.sold_to_party
and item.material_id = Zer_Cds_Materials_Price.material_id
association [*] to Zer_Cds_Equi_Request_Item_Car as caracs
on $projection.request_nb = caracs.request_nb
and $projection.material_list_id = caracs.material_list_id
and $projection.material_id = caracs.material_id
{
key item.request_nb,
key header.sold_to_party,
key item.material_list_id,
key item.material_id,
    item.quantity,
    material.material_list_text,
    material.material_text,
    material.max_qty_unit as unit,
    material.t006a.mseht as unit_long_text,
    Zer_Cds_Materials_Price.unit_price,
    Zer_Cds_Materials_Price.currency,
        
    //association
    caracs


}

Is there any documentation or anything that can help to optimize CDS Views ?

[horst_keller]

With material[1:inner] in the SELECT list of a view you simply publish the association for usage in another view or in Open SQL. Although it is syntactically allowed to specify [inner] here, it has no effect.

The instantiation of the joins only takes place a the point where the association is used in a path expression and it is there, where you can specify the kind of the join. Check the database objects of your views to see the generated DDL first (not the SQL trace, that would be the second step). Find the view that uses the association and try to enforce the inner join there in the path expression. Hope that helps.