2024 Aug 27 9:53 AM - edited 2024 Aug 27 3:43 PM
Hi,
We are in SAP S4 HANA OP2020 system and we are facing severe performance issue with below select statement. We are passing full primary keys still it takes huge spike on CPU consumption and time consuming statement.
IF lt_material[] IS NOT INITIAL
SELECT
a~matnr,
a~bwkey,
a~bwtar,
a~lbkum,
a~salk3,
a~verpr,
a~zkprs,
a~timestamp
FROM mbew AS a
FOR ALL ENTRIES IN _material
WHERE a~matnr EQ _material-matnr AND
a~bwkey IN _t001w_rg AND "This is range for BWKEY
a~bwtar IN _bwtar " This is range for BWTAR
INTO TABLE (lt_mbew_tab_n1).
ENDIF.
For example: if LT_MATERIAL has 1000 Records it takes 5 seconds to fetch data(~39000 records) and when LT_MATERIAL has 10000 Records then it take 12 Seconds to fetch data(~390000 records). It is not good that a select statement is going for 5 seconds or 12 seconds.
One thing to notice here is table MBEW is having huge number of records in our system(no of records: 2662533622 i.e more than ~2500 Million records). Data volume could be one of the reason why performance is poor here.
1. But we would to know what SAP suggest in this case when MBEW has these many records?
2. Even though we are passing full key CPU load is really high and it is time consuming select statement?
3. Does SAP recommend to Archive MBEW table data?
P.S: We looked into MBEW it is having Replacement object MBV_MBEW(which is a CDS view MBV_MBEW) that means data is anyway pushed down to HANA DB still it has this performance issue.
Regards,
Prince
Request clarification before answering.
..
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi, Prince
I think nobody helps you. We have the same problems with view MBV_MBEW.
To get a stock for single material and for all plants where material exists system need 30 seconds that is very poor performance, and we didn't use for all entries clause in query. I created CDS which return needed information from MBV_MBEW and performance is very bad. I did little investigation and noticed that fields salk3 and lbkum are calculated from accdoca extract on the fly, that is cause and you can't do anything with that.
Hi, @Sandra_Rossi just execute simple query to mbv_mbew asking for matnr, werks, lbkum, salk3 and collect SQL trace in any way which you prefer. I prefer ST12. And then push button "Explain".
I pretty sure that you would see the same picture as on the screenshot which I attached. That is why I decided that MBV_MBEW has a poor performance because of ACDOCA extract calculations. The bigger part of query's cost is aggregation from ACDOCA.
There is a execution plan to CDS which I created for querying MBV_MBEW that is simple CDS which selects data from MBV_MBEW and some tables which connected to that CDS via "left outer join". If you need I'll share the CDS with you.
User | Count |
---|---|
53 | |
9 | |
8 | |
6 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.