performance issue with AUSP table

[former_member768291]

Hi experts

I am facing a low performance after using AUSP table.

 SELECT
 objek,
 atwrt AS ppo
 FROM ausp
 FOR ALL ENTRIES IN @lta_matnr
 WHERE
 objek = @lta_matnr-matnr
 AND atinn = @ltp_output
 AND atzhl = @lco_atzhl
 AND mafid = @lco_mafid
 AND klart = @lco_klart
 AND datub > @sy-datum
 INTO TABLE @DATA(lta_ausp).

This is the query I am using, I have mentioned all the key fields, but still the performance is degraded.

Any fix for this ?

Thank You,

Ankur.

[aoyang]

If you want to use FOR ALL ENTRIES IN, follow the below best practice and see if it helps the performance.

-Pass lta_matnr to another temporal itab. DATA(lta_matnr_tmp) = lta_matnr.

-SORT lta_matnr_tmp BY matnr. DELETE ADJACENT DUPLICATES FROM lta_matnr_tmp COMPARING matnr. This will reduce the number of rows.

-Add check before the select. IF lta_matnr_tmp IS NOT INITIAL, do the select. otherwise don't do the select. If lta_matnr_tmp is empty, FOR ALL ENTRIES IN will try to fetch all the records from AUSP.

-Finally use lta_matnr_tmp as the FOR ALL ENTRIES IN itab.

Or alternatively, you can use INNER JOIN to join lta_matnr instead of FOR ALL ENTRIES.

Of course, you should still delete duplicates lta_matnr by above method to reduce the number so the performance improves.

SELECT
AUSP~OBJEK,
AUSP~ATWRT AS PPO
FROM AUSP
##DB_FEATURE_MODE[ITABS_IN_FROM_CLAUSE] ##ITAB_KEY_IN_SELECT
INNER JOIN @LTA_MATNR AS LTA_MATNR
ON LTA_MATNR~MATNR = AUSP~OBJEK
WHERE AUSP~ATINN = @LTP_OUTPUT
AND AUSP~ATZHL = @LCO_ATZHL
AND AUSP~MAFID = @LCO_MAFID
AND AUSP~KLART = @LCO_KLART
AND AUSP~DATUB > @SY-DATUM
INTO TABLE @DATA(LTA_AUSP).

[former_member768291]

yes, i have already done that before FOR ALL ENTRIES. First i have sorted the lta_matnr the delete adjacent duplicate and then checked for initial.

But still the performance is same.

i also tried with a left outer join in CDS.

But, sadly the performance is same.

[aoyang]

gghosh123123 Please try INNER JOIN and see if it helps.

How many records are in lta_matnr?

[former_member768291]

Right now there is only 3 records in lta_matnr

Before the addition of the AUSP table it was only taking 3-4 microseconds, but now its taking around 14-15.

[aoyang]

gghosh123123 3 records in lta_matnr should not take 15 sec. I feel like your index optimizer is not picking the right index for AUSP.

In my system, I put around 80,000 rows in lta_matnr and ran your SQL and came back in less than 1 sec. What is your DB? If it's non-HANA DB, can you try specifying index(in your case, N1 should be a good index. You can try to add ATWRT as the WHERE condition as well) and see if it helps?

Also, is it this note applicable to your system?

https://launchpad.support.sap.com/#/notes/3081892

[RaymondGiuseppi]

Could you execute a SQL trace?

[Sandra_Rossi]

Please edit your question, select your code and press the button [CODE], which makes the code appear colored/indented, it will be easier for people to look at it. Thank you!