Performance Tuning: Data extraction from RSEG table

[Former Member]

Experts/ Friends/ Rob,

Need help in Performance Tuning:

Data extraction from RSEG table

Appreciate if Expert help is provided to

fine tune the below code.

Currently it takes 65 to 70 minutes to extract

5000 data records from R3 to BW(3.5 version)

=============================
  TABLES : ekko,            "PO Header
           konv,            "Pricing conditions
           t001,            "Company Codes"
           ekpo,            "PO Item
           ekab,            " PO - CONTRACT LINK
           a016,            "Contract Item
           zlg_ticketdata,   "Completed transactions Load table
           rbkp,      "Document Header: Invoice Receipt  --- CHG1
           rseg.      "Document Item: Incoming Invoice   --- CHG1


DATA : BEGIN OF  itab1 OCCURS 0,                             
              belnr LIKE rbkp-belnr,                         
              gjahr LIKE rbkp-gjahr,                         
              buzei LIKE rbkp-buzei,  
              bukrs like rseg-bukrs,                        
ebeln LIKE rseg-ebeln,                         
ebelp LIKE rseg-ebelp,                         
         END OF itab1.                                       

  DATA : itab2 LIKE konv  OCCURS 0 WITH HEADER LINE.
  DATA : itab3 LIKE itab1 OCCURS 0 WITH HEADER LINE.
  DATA : itab4 LIKE ekab  OCCURS 0 WITH HEADER LINE.
  DATA : itab5 LIKE itab1 OCCURS 0 WITH HEADER LINE.

  DATA : BEGIN OF  itab_rbkp OCCURS 0,                       
              belnr LIKE rbkp-belnr,                         
              gjahr LIKE rbkp-gjahr,                         
              cpudt LIKE rbkp-cpudt,                         
              cputm LIKE rbkp-cputm,                         
              bukrs LIKE rbkp-bukrs,                         
         END OF itab_rbkp.                                   


  DATA : d1 TYPE dats,                                       
         t1 TYPE tims.                                       
**    date and time for updating delta table
  DATA : dmax   TYPE dats,                                   
         tmax   TYPE tims,                                   
         v_year TYPE gjahr.                                  
* end   addition CHG1

  DATA : ws_fc_bsart LIKE ekko-bsart.
  DATA : ws_baseprc TYPE f.
  DATA: ws_baseprc1 TYPE f.
  DATA: lv_adrnr LIKE t001-adrnr.
  DATA: l_carint LIKE zlg_ticketdata-tankcar_init.
  DATA: l_carno LIKE zlg_ticketdata-tankcar_no.


* Select ranges

  RANGES: l_r_cpudt FOR RBKP-cpudt.


      DATA:ls_base_price LIKE konv-kawrt,
           ls_knumv LIKE ekko-knumv,
           ls_base_diff LIKE konv-kawrt.

      DATA : v_startdate  type  sy-datum.
             doc_no(10) TYPE c,
             year(4) TYPE n,
             doc_no_max(10) TYPE c,
             year_max(4) TYPE n.


      CLEAR   v_year.
      v_year = d1(4).                        "Year value

      CLEAR   :       itab_rbkp,
                      itab1.
      REFRESH :       itab_rbkp,
                      itab1.


      v_startdate  =  sy-datum – 100.


************  Fetch records from RBKP table
************  into  internal  table  “itab_rbkp”
      SELECT  belnr
              gjahr
              cpudt
              cputm
              bukrs
         INTO  TABLE  itab_rbkp
           FROM rbkp
        WHERE   belnr  GE  '1100000000'
        AND     belnr  LE  '1199999999'
        AND     gjahr  GE  v_year
        AND     cpudt  GE  v_startdate
        AND     bukrs  IN  ('0100', '0200').


      IF  NOT  itab_rbkp[]  IS INITIAL.

        SORT itab_rbkp   BY   gjahr
                              belnr
                              bukrs.
*=======================
*******    fetch records from  RSEG  table
*******    FOR ALL ENTRIES  IN  itab_rbkp
*******    into  internal  table  “itab1”
*======================

        SELECT * FROM  RSEG
           INTO TABLE itab1
             FOR ALL ENTRIES  IN  itab_rbkp
                 WHERE   gjahr    EQ  itab_rbkp-gjahr
                 AND     belnr    EQ  itab_rbkp-belnr
                 AND     bukrs    EQ  itab_rbkp-bukrs.


        SORT  itab1     BY   gjahr
                             belnr
                             buzei.


      ENDIF.               " IF  NOT  itab_rbkp[]  IS INITIAL


      LOOP AT itab1.


        CLEAR e_t_data.
        MOVE-CORRESPONDING itab1 TO e_t_data.



        IF itab1-stblg NE ' '. " If this is a reversal document
          e_t_data-stmt_type = 'REV'.  " set reversal status

          clear   RSEG.
          SELECT * FROM  RSEG
                 INTO TABLE itab3
                  WHERE ( belnr = itab1-stblg
                   AND    ebeln = itab1-ebeln
                   AND    ebelp = itab1-ebelp ) .

        ENDIF.    " Not a reversal


********	Get Pricing Condition number from EKKO
****************************************************
        CLEAR ekko.
        SELECT SINGLE knumv
          INTO e_t_data-knumv
          FROM ekko
         WHERE ebeln = itab1-ebeln.

        MOVE e_t_data-knumv TO ls_knumv.

*** Get Contract Details for the PO

********	Get details from table EKPO
****************************************************

        CLEAR ekpo.
        SELECT SINGLE *
          INTO ekpo
          FROM ekpo
         WHERE ebeln = itab1-ebeln
           AND ebelp = itab1-ebelp.

        MOVE ekpo-konnr TO e_t_data-konnr.


        CLEAR ekko.
        SELECT SINGLE *
          INTO ekko
          FROM ekko
         WHERE ebeln = ekpo-konnr.


        IF ekko-ihrez IS INITIAL.
          MOVE ekko-ebeln TO ekko-ihrez.
        ENDIF.


        MOVE ekko-ihrez TO e_t_data-ihrez.
        MOVE ekko-llief TO e_t_data-llief.

****** In case of regular payments, we need to check if this
****** is an adjustment or regular payment *****

***  For this we need to get the contract details ******

        IF e_t_data-stmt_type EQ ' '.  " if not reversal

********* Fetch from  EKAB
          Clear  EKAB.
          SELECT SINGLE *
            FROM ekab
           WHERE ebeln = itab1-ebeln
             AND ebelp = itab1-ebelp. "Get CONTRACT NO FOR PO


*==================================
**** NOW SELECT ALL THE POS FOR THE CONTRACT
********* Fetch from  EKAB
*===================================================
          SELECT *
            FROM ekab
            INTO TABLE itab4
           WHERE konnr = ekab-konnr
             AND ktpnr = ekab-ktpnr.

          LOOP AT itab4.

**** SELECT THE DETAILS FROM RBKP / RESEG
*** FOR THE PO'S IN THIS CONTRACT TO MAKE SURE THAT
*** THIS PO/ITEM IS AN ADJ OR NOT

*** if po / item is same as one currently being processed

            IF itab4-ebeln = itab1-ebeln AND
              itab4-ebelp  = itab1-ebelp.  " if PO item is same
            ELSE. " PO / item is different - check document date


*******************************
              Clear   RSEG.
              SELECT SINGLE *
                FROM   RSEG
               WHERE ebeln = itab4-ebeln
                 AND ebelp = itab4-ebelp  .

              IF itab1-bldat = RSEG-bldat. " if same doc date
                MOVE 'ADJ' TO e_t_data-stmt_type.
                EXIT.
              ENDIF.

            ENDIF.

          ENDLOOP.

        ENDIF.

***************************************
        SELECT SINGLE stceg adrnr periv
         FROM t001
         INTO (e_t_data-stceg,
               lv_adrnr,
               e_t_data-periv)
        WHERE bukrs =  RSEG-bukrs.



***** Get Pricing Condtiion details and append to the inv detais

        CLEAR konv.

        REFRESH itab2.
        CLEAR itab2.

        SELECT *
         FROM konv
         INTO TABLE itab2
        WHERE knumv = ls_knumv
          AND kposn = itab1-ebelp.

        CLEAR e_t_data-zbaseprice.

        LOOP AT itab2.

          MOVE itab2-kposn TO e_t_data-kposn.
          MOVE itab2-kschl TO e_t_data-kschl.



* getting the GST Value
            IF itab2-kschl = 'GST'.
              MOVE itab2-kwert TO e_t_data-gst.
            ENDIF.
* getting the Freight Charge
            IF itab2-kschl = 'ZRFS'.
              MOVE itab2-kwert TO e_t_data-frt_charge.
            ENDIF.



***   Getting the Bill Of Lading number
*******************************************************
            SELECT SINGLE frbnr
              INTO e_t_data-bol_no
              FROM mkpf
             WHERE mblnr = itab1-lfbnr
                   AND mjahr = itab1-lfgja.


          ENDIF.    " if freight contract.


        ENDLOOP . " end of itab2


        APPEND e_t_data.

        CLEAR e_t_data.

      ENDLOOP. " END OF ITAB1
*==============================

Thanks & Regards,

Aby Jacob

[Former Member]

hi,

try and avoid use of select query inside loops.

instead, you can use select .. for all entries statement.

To avoid nested select statements we use SELECT FOR ALL ENTRIES statement.

If there r more than 10000 records SELECT FOR ALL ENTRIES is used.

Performance wise SELECT FOR ALL ENTRIES is better to use.

reward points if helpful..

[Former Member]

Hi,

Create a secondary index on where condition fields i.e, BELNR, EBELN, EBELP fields which might improve the performance..

[Former Member]

Friends,

Thanks for your helpful suggestions.

Rob,

I am waiting for your expert analysis/solution.

Many Thanks

Aby ........ )

[Former Member]

Well, it's a bit hard to tell - I couldn't get it to pass a syntax check or even do a pretty print on it. There seems to be a misplaced ENDIF.

However, the problem may be on the initial SELECT from RBKP. How many documents are in the table for the range 11XXXXXXXX? This may not be selective enough.

There also seems to be a lot of work going on inside the main loop, but I doubt if that's the problem.

The SELECT on RESB seems OK.

Have you run ST05 or SE30 to pinpoint the problem?

Rob

[ThomasZloch]

here's where I would start:

> FROM rbkp

> WHERE belnr GE '1100000000'

> AND belnr LE '1199999999'

-> try replacing with WHERE belnr BETWEEN '1100000000' AND '1199999999'

> SELECT SINGLE *

> FROM ekab

> WHERE ebeln = itab1-ebeln

> AND ebelp = itab1-ebelp. "Get CONTRACT NO FOR PO

-> not using primary or secondary key here

> SELECT SINGLE *

> FROM RSEG

> WHERE ebeln = itab4-ebeln

> AND ebelp = itab4-ebelp .

-> not using primary or secondary key here

Cheers

Thomas

[Former Member]

Thomas / Rob,

You guys are a Blessing !

Sorry for the delay in answering

Rob,

Thanks, SE30 analysis was very helpful in performance tuning

I have achieved 50% improvement in data retrieval

Many thanks again, )

Aby Jacob