Why does `FOR ALL ENTRIES` lower performance of CDS view on DB6?

Question

I'm reading data from a SAP Core Data Service (CDS view, SAP R/3, ABAP 7.50) using a WHERE clause on its primary (and only) key column. There is a massive performance decrease when using FOR ALL ENTRIES (about a factor 5):

Reading data using a normal WHERE clause takes about 10 seconds in my case:

SELECT DISTINCT *
FROM ZMY_CDS_VIEW
WHERE prim_key_col eq 'mykey'
INTO TABLE @DATA(lt_table1).

Reading data using FOR ALL ENTRIES with the same WHERE takes about 50 seconds in my case:

"" boilerplate code that creates a table with one entry holding the same key value as above
TYPES: BEGIN OF t_kv,
  key_value like ZMY_CDS_VIEW-prim_key_col,
END OF t_kv.

DATA lt_key_values TYPE TABLE OF t_kv.
DATA ls_key_value TYPE t_kv.
ls_key_value-key_value = 'mykey'.
APPEND ls_key_value TO lt_key_values.

SELECT *
FROM ZMY_CDS_VIEW
FOR ALL ENTRIES IN @lt_key_values
WHERE prim_key_col eq @lt_key_values-key_value
INTO TABLE @DATA(lt_table2). 

I do not understand why the same selection takes five times as long when utilising FOR ALL ENTRIES. Since the table lt_key_values has only 1 entry I'd expect the database (sy-dbsys is 'DB6' in my case) to do exactly the same operations plus maybe some small neglectable overhead ≪ 40s.

Selecting from the underlying SQL view instead of the CDS (with its Access Control and so on) makes no difference at all, neither does adding or removing the DISTINCT key word (because FOR ALL ENTRIES implies DISTINCT).

Fleam

I don't see why the 2 SELECTs would be different if the FAE table contains only one line. It's probably an issue with the DBSL. You should check if some SAP notes have a patch for that, or open a ticket at SAP support if notes don't apply. – Ibson

Answer 1

A colleague guessed, that the FOR ALL ENTRIES is actually selecting the entire content of the CDS and comparing it with the internal table lt_key_values at runtime. This seems about right.

Using the transaction st05 I recorded a SQL trace that looks like the following in the FOR ALL ENTRIES case:

  SELECT
     DISTINCT "ZMY_UNDERLYING_SQL_VIEW".*
   FROM
     "ZMY_UNDERLYING_SQL_VIEW",
     TABLE( SAPTOOLS.MEMORY_TABLE( CAST( ? AS BLOB( 2G )) ) CARDINALITY 1 ) AS "t_00" ( "C_0" VARCHAR(30) )
   WHERE
         "ZMY_UNDERLYING_SQL_VIEW"."MANDT" = ?
     AND "ZMY_UNDERLYING_SQL_VIEW"."PRIM_KEY_COL" = "t_00"."C_0"

   [...]

Variables

   A0(IT,13)       = ITAB[1x1(20)]
   A1(CH,10)       = 'mykey'
   A2(CH,3)        = '100'

So what actually happens is: ABAP selects the entire CDS content and puts the value from the internal table in something like an additional column. Then it only keeps those values where internal table and SQL result entry do match. ==> No optimzation on database level => bad performance.

Fleam

This is not the standard behavior of CDS views, seems to be DB6 specific. What is your SAP_BASIS version? – Zebada

@András : SAP_BASIS Relase 750, SP-Level 0013 – Fleam