performance issue

[Former Member]

Hello Everyone,

I have a created a program to update a assignment(Zuonr) field with refernce doc number (refdoc) in both BSEG and BSIS tables. This is requirement they have given to to have common a field so that they can do the GL clearing at end of year.

The program is taking long time, its taken more than a day to just update the assignment field in both BSEG and BSIS for around 8000 records(for a month range) and its just a test data in dev sys. The actual data is almost 10 times more than test data n will take ages if i run the same.

I tried to modify the table instead of update in the program, as zuonr is primary key in BSIS table, instead of updating the record, its appending the record.

Can anyone help/suggest to improve the performnce of the program please. This is an urgent requirement.

The code:

REPORT  zgl_clearing
        NO STANDARD PAGE HEADING
        MESSAGE-ID zgl.
*
**-----------------------------Types decleration--------------------------*
TYPES: BEGIN OF tw_j_1iexchdr,
        trntyp       TYPE  j_1itrntyp,
        docno        TYPE  j_1idocno,
        END OF tw_j_1iexchdr,
        tt_j_1iexchdr TYPE STANDARD TABLE OF tw_j_1iexchdr.

TYPES: BEGIN OF tw_j_1ipart2,
        trntyp       TYPE  j_1itrntyp,
        docno        TYPE  j_1idocno,
*        refdoc       TYPE  j_1irefdoc,
         refdoc(10)  TYPE c,
        fawref       TYPE  j_1ifawref,
        fayear       TYPE  j_1ifayear,
        END OF tw_j_1ipart2,
        tt_j_1ipart2 TYPE STANDARD TABLE OF tw_j_1ipart2.

TYPES: BEGIN OF tw_exbsis.
        INCLUDE STRUCTURE bsis.
TYPES: END OF tw_exbsis,
       tt_exbsis TYPE STANDARD TABLE OF tw_exbsis.

TYPES: BEGIN OF tw_rseg,
        gjahr TYPE gjahr,
        belnr TYPE belnr_d,
        lfbnr TYPE lfbnr,
        END OF tw_rseg,
        tt_rseg TYPE STANDARD TABLE OF tw_rseg.

TYPES: BEGIN OF tw_bkpf,
        belnr TYPE belnr_d,
        END OF tw_bkpf,
        tt_bkpf TYPE STANDARD TABLE OF tw_bkpf.

TYPES: BEGIN OF tw_temp,
        awkey TYPE awkey,
        END OF tw_temp,
        tt_temp TYPE STANDARD TABLE OF tw_temp.


DATA:  date      TYPE bldat,
       gjahr     TYPE gjahr,
       hkont     TYPE hkont,
       gjahr1    TYPE gjahr,
       bukrs     TYPE bukrs,
      lv_year(4) TYPE c,
      v_zuonr    TYPE dzuonr.

DATA:  lw_j_1iexchdr TYPE tw_j_1iexchdr,
       lt_j_1iexchdr TYPE tt_j_1iexchdr,
       lw_j_1ipart2  TYPE tw_j_1ipart2,
       lt_j_1ipart2  TYPE tt_j_1ipart2,
       lw_exbsis     TYPE tw_exbsis,
       lt_exbsis     TYPE tt_exbsis,
       lw_bsis       TYPE tw_exbsis,
       lt_bsis       TYPE tt_exbsis,
       lw_rseg       TYPE tw_rseg,
       lt_rseg       TYPE tt_rseg,
       lw_bkpf       TYPE tw_bkpf,
       lt_bkpf       TYPE tt_bkpf,
       lw_temp       TYPE tw_temp,
       lt_temp       TYPE tt_temp.


FIELD-SYMBOLS: <fs> TYPE STANDARD TABLE,
               <fs_wa>,
               <fs_wa1>.
ASSIGN lt_exbsis[] TO <fs>.
ASSIGN lt_exbsis TO <fs_wa>.

SELECTION-SCREEN: BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.
SELECT-OPTIONS: s_bldat FOR  date.
PARAMETERS:     p_gjahr TYPE gjahr,
                p_bukrs TYPE bukrs,
                p_hkont LIKE bseg-hkont,
                r1 RADIOBUTTON GROUP rad1,
                r2 RADIOBUTTON GROUP rad1 DEFAULT 'X'.
SELECTION-SCREEN: END OF BLOCK b1.

lv_year = p_gjahr.
CASE 'X'.
  WHEN r1.
    PERFORM sales_clearing.
  WHEN r2.
    PERFORM purchase_clearing.
ENDCASE.


*&---------------------------------------------------------------------*
*&      Form  sales_clearing
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM sales_clearing .

ENDFORM.                    " sales_clearing
*&---------------------------------------------------------------------*
*&      Form  purchase_clearing
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
*  -->  p1        text
*  <--  p2        text
*----------------------------------------------------------------------*
FORM purchase_clearing .
*Select the records from Excise header Table
  SELECT trntyp
         docno
   FROM  j_1iexchdr
   INTO  CORRESPONDING FIELDS OF TABLE lt_j_1iexchdr
   WHERE trntyp = 'GRPO'  AND
         bukrs EQ p_bukrs AND
         exdat IN s_bldat.
  SORT lt_j_1iexchdr BY docno.

  IF NOT lt_j_1iexchdr[] IS INITIAL.
* Select the records from Part 2 table
    SELECT trntyp
           docno
           refdoc
           fawref
           fayear
    FROM  j_1ipart2
    INTO  CORRESPONDING FIELDS OF TABLE lt_j_1ipart2
    FOR   ALL ENTRIES IN lt_j_1iexchdr
    WHERE trntyp = lt_j_1iexchdr-trntyp AND
          docno  = lt_j_1iexchdr-docno.
  ENDIF.

  SORT lt_j_1ipart2 BY docno.
LOOP AT lt_j_1ipart2 INTO lw_j_1ipart2.
* Fetch single record based on Part 2 table entries
    SELECT SINGLE * FROM bsis
       INTO lw_bsis
             WHERE belnr = lw_j_1ipart2-fawref
*               AND gjahr = p_gjahr
               AND gjahr = lw_j_1ipart2-fayear
               AND hkont = p_hkont.
    IF sy-subrc EQ 0.
      v_zuonr = lw_j_1ipart2-refdoc.           "assignment field which needs to be updated

      UPDATE bseg
            SET zuonr = v_zuonr                "Update the required field in BSEG Table
         WHERE belnr = lw_j_1ipart2-fawref
           AND gjahr = lw_j_1ipart2-fayear
           AND hkont = p_hkont.
*      IF sy-subrc = 0.
      UPDATE bsis                              "Update the required filed in BSIS Table
      SET zuonr = v_zuonr
         WHERE hkont = p_hkont
           AND gjahr = lw_j_1ipart2-fayear
           AND belnr = lw_j_1ipart2-fawref.
      IF sy-subrc = 0.
        COMMIT WORK.                           "Commit the DB changes
      ENDIF.

    ENDIF.
    CLEAR: v_zuonr.
* Get the selected entries from RSEG based on part2 table
    SELECT SINGLE gjahr
                  belnr
                  lfbnr
             FROM rseg
             INTO lw_rseg
            WHERE lfbnr = lw_j_1ipart2-refdoc
              AND gjahr = lw_j_1ipart2-fayear
              AND bukrs = p_bukrs.
    CONCATENATE lw_rseg-belnr lw_rseg-gjahr INTO lw_temp.
*    CLEAR lw_rseg.

*Select document number from BKPF based on rseg table
    SELECT SINGLE belnr
            FROM  bkpf
            INTO  lw_bkpf
           WHERE  bukrs = p_bukrs
             AND  gjahr = lw_rseg-gjahr
             AND  awkey = lw_temp-awkey.
    CLEAR lw_temp.

    CLEAR lw_bsis.
    SELECT SINGLE *
             FROM bsis
             INTO CORRESPONDING FIELDS OF lw_bsis
            WHERE belnr = lw_bkpf-belnr
              AND bukrs = p_bukrs
              AND gjahr = lw_rseg-gjahr.


    IF sy-subrc EQ 0.
      v_zuonr = lw_j_1ipart2-refdoc.                 "assignment field which needs to be updated

      UPDATE bseg
            SET zuonr = v_zuonr                      "Update the required field in BSEG Table
         WHERE belnr = lw_bkpf-belnr
           AND gjahr = lw_rseg-gjahr
           AND hkont = p_hkont.

      UPDATE bsis
      SET zuonr = v_zuonr                           "Update the required field in BSIS Table
         WHERE hkont = p_hkont
           AND gjahr = lw_rseg-gjahr
           AND belnr = lw_bkpf-belnr.
      IF sy-subrc = 0.
        COMMIT WORK.
      ENDIF.

    ENDIF.
    CLEAR: v_zuonr.
  ENDLOOP.

ENDFORM.                    " purchase_clearing

Regards

[Former Member]

Hi,

Try to avoid SELECT in LOOP.

Instead of

loop at itab try to use READ TABLE with index.

by doing like this u can improve some more performance...

Kishore.

[Former Member]

Thanks for your suggestion Kishor.

But that really dosnt solve my purpose.

Regards

[Former Member]

hi

Apply this

A: Change the SELECT statement to use INTO TABLE itab.

B: Add MOVE statements to fill the header line of itab.

C: Remove the internal table itab from the SELECT statement.

😧 This code is optimized; no change is required.

**Please reward suitable points***

With Regards

Navin Khedikar

[Former Member]

Hi,

You are using the Selects from BKPF,BSIS and RSEG tables in the LOOP..ENDLOOP and also Updating the tables BSEG and BSIS in LOOP

so naturally it will take huge time.

Write sepearte selects for them outside of loop using for all entreies and read them in LOOP.

Also update the tables out of the Loop .

Other performance Tips are:

1) Dont use nested seelct statement

2) If possible use for all entries in addition

3) In the where addition make sure you give all the primary key

4) Use Index for the selection criteria.

5) You can also use inner joins

6) You can try to put the data from the first select statement into an Itab and then in order to select the data from the second table use for all entries in.

7) Use the runtime analysis SE30 and SQL Trace (ST05) to identify the performance and also to identify where the load is heavy, so that you can change the code accordingly

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/5d0db4c9-0e01-0010-b68f-9b1...

reward if useful.

regards,

Anji

[Former Member]

Hello Anji,

So what you have suggested is true. But how can i use update statement outside the loop. I have to update the assignment filed for corresponding document number.

Also how can i optimize the following code.

 LOOP AT lt_j_1ipart2 INTO lw_j_1ipart2.

SELECT SINGLE gjahr
                  belnr
                  lfbnr
             FROM rseg
             INTO lw_rseg
            WHERE lfbnr = lw_j_1ipart2-refdoc
              AND gjahr = lw_j_1ipart2-fayear
              AND bukrs = p_bukrs.
    CONCATENATE lw_rseg-belnr lw_rseg-gjahr INTO lw_temp.
*    CLEAR lw_rseg.

*Select document number from BKPF based on rseg table
    SELECT SINGLE belnr
            FROM  bkpf
            INTO  lw_bkpf
           WHERE  bukrs = p_bukrs
             AND  gjahr = lw_rseg-gjahr
             AND  awkey = lw_temp-awkey.
    CLEAR lw_temp.

IF sy-subrc EQ 0.
      v_zuonr = lw_j_1ipart2-refdoc.                 "assignment field which needs to be updated

      UPDATE bseg
            SET zuonr = v_zuonr                      "Update the required field in BSEG Table
         WHERE belnr = lw_bkpf-belnr
           AND gjahr = lw_rseg-gjahr
           AND hkont = p_hkont.

      UPDATE bsis
      SET zuonr = v_zuonr                           "Update the required field in BSIS Table
         WHERE hkont = p_hkont
           AND gjahr = lw_rseg-gjahr
           AND belnr = lw_bkpf-belnr.
      IF sy-subrc = 0.
        COMMIT WORK.
      ENDIF.

    ENDIF.
    CLEAR: v_zuonr.
  ENDLOOP.

Regards

[Former Member]

What Kishore is saying is right,besides that, pls have a look at my observations below.

You are not suppose to update or insert any standard databse table directly.

You need to do it through BAPI or BDC.

Regards,

Sujatha.