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 ?