SQL Join and CDS associations

[#1]

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 ?

[retired_member]

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.