Long runtime in CDS View using ASSOCIATION

[ennowulff]

Hey there!

I stumbled upon the following performance issue inh CDS views:

There is a customizing table ZBTRK_CF which holds Information about customizable fields. The structure is as follows:

• FIELDNAME
• REF_TABLE
• REF_FIELD

Entries are like:

FIELDNAME REF_TABLE REF_FIELD
F01	  T001	    BUKRS
F02	  T002	    SPRAS
F03	  T003	    BLART
F04	  T004	    KTOPL
F05	  T005	    LAND1

For these fields I want to retrieve the description (table DD04T) based on the rollname of REF_TABLE-REF_FIELD (Table DD03L).To achieve this I created the CDS view "ZI_TABFLD_DESCR":

@AbapCatalog.sqlViewName: 'ZI_TABFLD_DESCR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Table field description'
define view zi_tab_fld_descr as select from dd03l as field
association [0..1] to dd04t as text
 on text.rollname = field.rollname 
 and text.ddlanguage = $session.system_language
{
 key field.tabname as Tablename,
 key field.fieldname as Fieldname,
 text.ddtext as Description
}
where field.fieldname <> '.INCLUDE'
 and text.as4local = 'A'
 and text.as4vers = '0000'

Then I created the CDS view "ZI_BTRKCF" to select the customizing table and map the description:

@AbapCatalog.sqlViewName: 'ZI_BTRKCF'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Custom field test'
define view zi_btrk_cf as select from zbtrk_cf as cf
/* description from REF_TABLE and REF_FIELDNAME */ 
association [0..1] to zi_tab_fld_descr as descr 
//inner join zi_tab_fld_descr as descr 
 on cf.ref_tabname = descr.Tablename
 and cf.ref_field = descr.Fieldname 
{
 key cf.mandt as Client,
 key cf.fieldname as Fieldname,
 descr.Description as description
}

When I use the definition with ASSOCIATION, the selection takes ~ 50 seconds.

When I use INNER JOIN then the selection is super fast (2 to 5 micro seconds).

Why is the performance using ASSOCIATION that bad?What am I doing wrong?Thanks for any suggestionReagardsEnno

[Marian_Zeis]

I think this "association [0..1]" is your problem:
Using [1..1] instead of [0..1] in the association definition will enforce a cardinality of one-to-one between the zi_btrk_cf view and the zi_tab_fld_descr view.
[0..1] means "LEFT JOIN" not "INNER JOIN".

[ennowulff]

Thanks for your comment, 20eed143c19f4b82bc4cf049916102cb !

I intentionally used [0..1] because I want to have the entry although the definition might be wrong (like wrong table or field name).

Your suggestion does not change anything on the runtime... 😞

[Sandra_Rossi]

Could you analyze the database execution plan? What database system is it?

[manuf]

Hi Enno,

Can you check the SQL CREATE statements of both versions if you see anything special?

Can you replace with LEFT OUTER JOIN and see how this behaves? Usually association gets translated to left join.

Maybe also try a comparison of SQL trace / PlanViz.

[ennowulff]

Hey manuf

This is the SQL CREATE statement:

CREATE VIEW "ZI_BTRK_CFTXT" AS SELECT 
  "CF"."MANDT" AS "CLIENT", 
  "CF"."FIELDNAME" AS "FIELDNAME", 
  CASE "=A1"."DESCRIPTION" WHEN ' ' THEN "=A0"."DESCRIPTION" ELSE "=A1"."DESCRIPTION" 
  END AS "DESCRIPTION" 
FROM ( 
  "/AKN/BTRK_CF" "CF" LEFT OUTER JOIN "/AKN/BTRK_CFT" "=A1" ON ( 
    "CF"."FIELDNAME" = "=A1"."FIELDNAME" AND 
    "=A1"."LANGU" = SESSION_CONTEXT( 
      'SAP_LANGU'
    ) AND 
    "CF"."MANDT" = "=A1"."MANDT" 
  ) 
) LEFT OUTER JOIN "ZI_TABFLD_DESCR" "=A0" ON ( 
  "CF"."REF_TABNAME" = "=A0"."TABLENAME" AND 
  "CF"."REF_FIELD" = "=A0"."FIELDNAME" 
)

LEFT OUTER JOIN also takes a long time...

I think as pjl suggests it might be a bug/ missing parameter in MaxDB

[Domi]

Hi enno.wulff

In ZI_TABFLD_DESCR move the TEXT restrictions to the ON clause and switch to INNER JOIN - the OUTER JOIN is in ZI_BTRKCF

@AbapCatalog.sqlViewName: 'ZI_TABFLD_DESCR'
@AbapCatalog.compiler.compareFilter: true
@AbapCatalog.preserveKey: true
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Table field description'
define view zi_tab_fld_descr as select from dd03l as field
association [0..1] to dd04t as text
 on text.rollname = field.rollname 
 and text.ddlanguage = $session.system_language
 and text.as4local = 'A'
 and text.as4vers = '0000'
{
 key field.tabname as Tablename,
 key field.fieldname as Fieldname,
 text[inner].ddtext as Description
}
where field.fieldname <> '.INCLUDE'

regards
Domi

[pjl]

that is correct, if you don't pull up the conditions into the join condition the whole thing is an inner join anyway.

[ennowulff]

Thanks for this hint, dominik.bigl2 !

Unfortunately it does not make the association faster.

[pjl]

As far as I know you are using MaxDB. And in my opinion it is a flaw in the query optimizer of the MaxDB, because I see a big difference in execution times when using these two statements, which should IMHO be equivalent:

fast

select cft.*, t.ddtext
    from "/AKN/BTRK_CFT" as cft
    join "/AKN/BTRK_CF" as cf 
                        on cf.fieldname = cft.fieldname
                        and cft.langu = 'E'
    left outer join dd03l as d 
                        on cf.ref_tabname = d.tabname
                        and cf.ref_field = d.fieldname
    left outer join dd04t as t 
                        on t.rollname = d.rollname
                        and t.ddlanguage = 'E'

slow

select cft.*, t.description
from "/AKN/BTRK_CFT" as cft
join "/AKN/BTRK_CF" as cf 
                    on cf.fieldname = cft.fieldname
                    and cft.langu = 'E'
left outer join (
    SELECT 
        "FIELD"."TABNAME"   AS "TABLENAME", 
        "FIELD"."FIELDNAME" AS "FIELDNAME", 
        "=A0"."DDTEXT"      AS "DESCRIPTION" 
    FROM               "DD03L" AS "FIELD" 
    LEFT OUTER JOIN    "DD04T" AS "=A0" 
                        ON ( 
                        "=A0"."ROLLNAME"    = "FIELD"."ROLLNAME" AND 
                        "=A0"."DDLANGUAGE"  = 'E' ) 
) as t
                    on t.tablename = cf.ref_tabname 
                    and t.fieldname = cf.ref_field

Maybe the SAP Note 1368477 - FAQ: SAP MaxDB Queryrewrite - SAP ONE Support Launchpad might help here? Possible there is just some setting on the database missing.

[ennowulff]

Thanks pjl I will check this SAP note ASAP!

[chaouki_akir]

I created the 3 objects in the minisap system NPL with DB SyBase.

And the duration is almost the same. So the definition with Association is not lasting 50 seconds.

(there is no execution plan in the st05 trace)

The source code of the table :

@EndUserText.label : 'table'
@AbapCatalog.enhancementCategory : #EXTENSIBLE_ANY
@AbapCatalog.tableCategory : #TRANSPARENT
@AbapCatalog.deliveryClass : #A
@AbapCatalog.dataMaintenance : #ALLOWED
define table zbtrk_cf {
  key mandt     : mandt not null;
  key fieldname : lvc_fname not null;
  key ref_table : lvc_rtname not null;
  key ref_field : lvc_rfname not null;

}

[ennowulff]

Thanks for checking, chaouki.akir !

[ennowulff]

sandra.rossi the DB is MaxDB

This is the execution plan:

Summary:

COSTVALUE              :     732773

  1. Table             : FIELD
     Strategy          : INDEX SCAN
     Additional Info.  : ONLY INDEX ACCESSED
  2. Table             : =A0
     Strategy          : JOIN VIA MULTIPLE KEY COLUMNS
     Additional Info.  :      NO TEMPORARY RESULTS CREATED
  3. Table             : CF
     Strategy          : RANGE CONDITION FOR KEY
  4. Table             : INTERNAL.TEMPORARY RESULT
     Strategy          : JOIN VIA RANGE OF MULTIPLE KEY COLUMNS
     Additional Info.  : TABLE TEMPORARY SORTED

==========================================================

Details:

COSTVALUE              :     732773
RESULT IS COPIED

  1. Table             : FIELD
     PAGECOUNT         :     450426
     Strategy          : INDEX SCAN
     Additional Info.  : ONLY INDEX ACCESSED
     Column or Index   : DD03L^2

  2. Table             : =A0
     PAGECOUNT         :      46562
     Strategy          : JOIN VIA MULTIPLE KEY COLUMNS
     Additional Info.  :      NO TEMPORARY RESULTS CREATED
     Columns           : ROLLNAME
                         DDLANGUAGE
                         AS4LOCAL
                         AS4VERS

  3. Table             : CF
     PAGECOUNT         :          1
     Strategy          : RANGE CONDITION FOR KEY
     Column            : MANDT

  4. Table             : INTERNAL.TEMPORARY RESULT
     PAGECOUNT         :          1
     Strategy          : JOIN VIA RANGE OF MULTIPLE KEY COLUMNS
     Additional Info.  : TABLE TEMPORARY SORTED
     Columns           : TABLENAME
                         FIELDNAME