How optimize Code in Badi User-exits long time running

[Former Member]

Hi, my code use 4 hrs long time running in prd, any idea for optimice the code.

I can move code :

SELECT * FROM user_addr

INTO TABLE lt_user_addr

where DEPARTMENT = 'Telesoporte'.

UP OVER to LOOP AT C_T_DATA ASSIGNING ..

HOW OPTIMIZE SELECT FROM CDHDR AND CDPOS ?

THANK YOU.

[Former Member]

Hi, my code use 4 hrs long time running in prd, any idea for optimice the code.

I can move code :

SELECT * FROM user_addr

INTO TABLE lt_user_addr

where DEPARTMENT = 'Telesoporte'.

UP OVER to LOOP AT C_T_DATA ASSIGNING ..

HOW OPTIMIZE SELECT FROM CDHDR AND CDPOS ?

THANK YOU.

[Former Member]

This is total code.

[matt]

You optimise in the same way you'd optimise any other. Only select the fields you need, use JOINS, not For All Entries and consider adding indexes.

[Former Member]

IS BADI se19

in Badi not working INNER JOIN WE HAVE ERROR, FOR THAT WE USE "FOR ALL ENTRIES"

We only use Field for Select, but what more we can optimize ?

[matt]

"not working" is not a recognised error code or syntax error.

There is no reason whatsoever that a properly written SQL with INNER JOINS should not work just because it's in a BADI. You've most likely not correctly written the SQL. You can use FOR ALL ENTRIES with c_t_data.

SELECT <fields> FROM user_addr INTO TABLE all_my_data
  INNER JOIN cdhdr
          ON cdhdr~username EQ user_addr~bname
  INNER JOIN cdpos
          ON cdpos~changenr EQ cdhdr~changenr
         AND cdpos~objectid EQ cdhdr~objectid
  FOR ALL ENTRIES IN c_t_data
  WHERE user_addr~department EQ 'Telesoporte'
    AND cdhdr~objectclass EQ 'MELDUNG'
    AND cdhdr~objectid EQ c_t_data-qmnum.

With later ABAP releases, you can join the internal table directly - something like this (might be a bit off, since I don't have a system to check it on).

SELECT <fields> FROM user_addr INTO TABLE all_my_data
  INNER JOIN cdhdr
          ON cdhdr~username EQ user_addr~bname
  INNER JOIN cdpos
          ON cdpos~changenr EQ cdhdr~changenr
         AND cdpos~objectid EQ cdhdr~objectid
  INNER JOIN @c_t_data AS bapi_data
          ON cdhdr~objectid EQ bapi_data~objectid
    AND cdhdr~objectclass EQ 'MELDUNG'
    AND cdhdr~objectid EQ c_t_data-qmnum.

Why not show what you tried (using the code button in the answers.sap.com editor) and what the error you got?

[Former Member]

I think is good code, but how adding CDPOS conditions.

IF sy-subrc = 0.

SORT lt_cdhdr BY udate DESCENDING utime DESCENDING.

SORT lt_cdpos BY changenr fname.

LOOP AT lt_cdhdr ASSIGNING <fs_cdhdr>.
READ TABLE lt_cdpos ASSIGNING <fs_cdpos>with key changenr = <fs_cdhdr>-changenr
fname = 'ARBPL' BINARY SEARCH.

IF sy-subrc = 0.

<L_S_DATA>-ZULMOD = <fs_cdhdr>-USERNAME.
<L_S_DATA>-ZFULMOD = <fs_cdhdr>-UDATE.
<L_S_DATA>-ZHULMOD = <fs_cdhdr>-UTIME.
EXIT.ENDIF.
ENDLOOP.

[matt]

First of all using internal tables, you cannot use BINARY SEARCH if you've a descending sort parameter. Also, you seldom need a SORT/BINARY search - you use a SORTED table to start with. I think perhaps you've been using very old training material if it doesn't include JOINS and different tables types.

Having seen your other answer, CDPOS in your system is a cluster table and so cannot be joined. In later versions it is transparent or can be converted. So you will need two selects, with two for all entries:

SELECT <fields> FROM user_addr INTO TABLE all_my_data
  INNER JOIN cdhdr
          ON cdhdr~username EQ user_addr~bname
*  INNER JOIN cdpos
*          ON cdpos~changenr EQ cdhdr~changenr
*         AND cdpos~objectid EQ cdhdr~objectid
  FOR ALL ENTRIES IN c_t_data
  WHERE user_addr~department EQ 'Telesoporte'
    AND cdhdr~objectclass EQ 'MELDUNG'
    AND cdhdr~objectid EQ c_t_data-qmnum.
IF sy-subrc IS INITIAL.
  SELECT <cpos_field> FROM cdpos INTO TABLE cdpos_data
    FOR ALL ENTRIES IN c_t_data
    WHERE ...

You should be using SORTED or HASHED table rather than SORT with BINARY SEARCH. Furthermore, neither work with sort DESCENDING. Note also that you can use ORDER BY and AS in select statements to sort the table before putting it into the internal table, and to change the target name of fields from what they are in the database.

ORDER BY udate DESCENDING...

SELECT username AS zfulmod...

[Former Member]

This is correct ?

SELECT bname FROM user_addr INTO TABLE all_my_data

INNER JOIN cdhdr
          ON cdhdr~username EQ user_addr~bname
  INNER JOIN cdpos
          ON cdpos~changenr EQ cdhdr~changenr
         AND cdpos~objectid EQ cdhdr~objectid
  FOR ALL ENTRIES IN c_t_data
  WHERE user_addr~department EQ 'Telesoporte'AND 
        cdhdr~objectclass EQ 'MELDUNG' AND
        cdhdr~objectid EQ c_t_data-qmnum AND
        cdpos~fname = 'ARBPL'.

[Former Member]

Error when JOIN CDPOS

[matt]

Don't use the YOUR ANSWER box to add a comment. Use the Add A Comment button.

[Former Member]

We using this Variant. and have error using Ranges, not knwow how declare in OO this argument.

FIELD-SYMBOLS: <L_S_DATA> TYPE BWE_QMEL,
<fs_cdhdr> TYPE cdhdr,
<fs_cdpos> TYPE cdpos.

FIELD-SYMBOLS: <fs_user_addr > TYPE user_addr.

ranges: LR_bname for XUBNAME.

DATA:
LT_CDPOS TYPE TABLE OF CDPOS.
DATA: lt_USER_ADDR TYPE STANDARD TABLE OF USER_ADDR,
lt_cdhdr type STANDARD TABLE OF cdhdr.

REFRESH: LT_CDHDR, LT_CDPOS, lt_user_addr.

SELECT bname FROM user_addrINTO CORRESPONDING FIELDS OF TABLE lt_user_addr

where DEPARTMENT = 'Telesoporte'.

LOOP AT lt_user_addr ASSIGNING <fs_user_addr>.

LR_bname-sign = 'I'.

LR_bname-option = 'EQ'.

LR_bname-low = <fs_user_addr>- bname.

append LR_bname.

ENDLOOP.

IF NOT C_T_DATA[] IS INITIAL AND NOT LR_bname[] IS INITIAL .

SELECT username OBJECTCLAS OBJECTID udateutimeINTO CORRESPONDING FIELDS OF TABLE lt_cdhdr

FROM cdhdrFOR ALL ENTRIES IN C_T_DATAWHERE username IN LR_bname

AND OBJECTCLAS EQ 'MELDUNG'AND OBJECTID EQ C_T_DATA-QMNUM.

IF sy-subrc EQ 0.

SELECT objectclas objectid changenr fnameINTO TABLE lt_cdposFROM cdposFOR ALL ENTRIES IN lt_cdhdrWHERE objectclas = 'MELDUNG' "avisoAND objectid = lt_cdhdr-OBJECTIDAND changenr = lt_cdhdr-changenr

AND fname = 'ARBPL'.

ENDIF.

ENDIF.

lt_user_addr-bname"AND OBJECTCLAS EQ 'MELDUNG'"AND OBJECTID EQ <L_S_DATA>-QMNUM.

SORT lt_cdhdr BY objectid ASCENDING date DESCENDING utime DESCENDING."SORT lt_cdpos BY changenr fname.

SORT lt_cdpos BY objectid ASCENDING changenr fname.

LOOP AT lt_cdhdr ASSIGNING <fs_cdhdr>.
"READ TABLE lt_cdpos ASSIGNING <fs_cdpos>"with key changenr = <fs_cdhdr>-changenr" fname = 'ARBPL' BINARY SEARCH.

READ TABLE lt_cdpos ASSIGNING <fs_cdpos>with key changenr = <fs_cdhdr>-changenr
objectid = <fs_cdhdr>-objectidBINARY SEARCH.
IF sy-subrc = 0.

READ TABLE C_T_DATA ASSIGNING <L_S_DATA>
with key QMNUM = <fs_cdhdr>-objectid BINARY SEARCH.IF sy-subrc = 0.
<L_S_DATA>-ZULMOD = <fs_cdhdr>-USERNAME.
<L_S_DATA>-ZFULMOD = <fs_cdhdr>-UDATE.
<L_S_DATA>-ZHULMOD = <fs_cdhdr>-UTIME."EXIT.

ENDIF.ENDIF.
ENDLOOP.

Thank you.

[FredericGirod]

If you are using HANA, you could check with CDS view. It is really faster than simple join