se11 DB-view buffered, how to? Improvement suggestion?

[Former Member]

se11, View for two DB-Tables (inner join),
trying to use SAP-Table-buffer (on the view(!), not the tables)

given:
say, I have 2 million products, and 125.000 of them sell.
So I want to use SAP-table-buffering for list-prices for these 125.000.
(also prices having a history via DATAB&DATBI;
i.e. there are a lot of prices datasets (say, hundreds of millions)
of which only a few will be needed
)

Plan:
So I want a view that joins DB-tables aNNN and KONP,
and then for the subsequent view: the single-record SAP-table-buffering;
(the view(!) FWIW I keep seeing people buffer the DB-Tables underlying a view,
which of course will not work, because a DB view is thus a join, and join bypasses
SAP-table-buffering - AFAIK, and as seen in the st05 or st04)

se11:
- create database view
- specify all key fields:
a) the fixed ones (when values are always the same) in Register "Selection Conditions"
b) the variable ones (values like the product need to be given) in Register "View Flds"

yet, se11 makes all view-fields to key-fields (Register "View Flds").
Thus, this now includes field KBETR of DB-Table konp which is only meant for output.

KBETR is a CURR(currency) field.
So, se11 does not activate:
because KBETR is a currency field and it's a key-field,
(because se11 itself made *all* view-fields to key-fields in the view),

and the SAP-Table-buffering here didn't allow Currency fields (as key field).

So I change the type of the view-field KBETR from CURR to decimal 11 2;
then, se11 does not activate because the domain of the konp-KBETR(CURR)
differs from the view field's type decimal 11 2.

So I unchange the KBETR type,
and then add all the fixed-value key-fields
(which are already in the Register "Selection conditions")
now also into the Register "View Flds";
and I put all of these fields first/top in the view.

now se11 only marks these key-fields als key in the view;
and thus, KBETR is no longer a key field in the view,
and now the view can be activated including SAP-Table-buffering.

Downside is, that much more bytes are to be buffered.
(the width of the view increases)
And it's confusing to have fields in the view,
which are also in the selection conditions
(contradicting useless where-clauses could be programmed)

suggestion:
it would be great if se11 would allow the user to change which
fields are key in a view
(currently it's done automatically, and then cannot be changed)
or if SAP-table-buffering could buffer CURR fields that are key in a view.

[horst_keller]

You might be able to circumvent the SE11 restrictions by using a CDS view instead of a classical view.

https://help.sap.com/http.svc/rc/abapdocu_751_index_htm/7.51/en-US/index.htm?file=abencds_sap_puffer...

Using the annotation AbapCatalog.preserveKey you can try to influence the key fields of the generated CDS database view.

Good luck

[周建华]

@AbapCatalog.sqlViewName: 'ZCDSA988'
@AbapCatalog.compiler.CompareFilter: true
@AccessControl.authorizationCheck: #CHECK
@EndUserText.label: 'A988'
define view ZCDS_A988
with parameters p_matnr : matnr,
p_dudat : datab
as select
( konp.kbetr / konp.kpein) as price
from a988
left outer join konp on a988.knumh = konp.knumh
where a988.kappl = 'V'
and a988.kschl = 'ZPR0'
and a988.waerk = 'CNY'
and a988.knumh is not null
and konp.kpein is not null
and a988.matnr = :p_matnr
and ( a988.datab <= :p_dudat and a988.datbi >= :p_dudat )

how to

konp.kbetr / konp.kpein