Bad Performance - Exceed time limit

Former Member · ‎2007 Oct 05

Hi Friends,

I have written a program to generate a report using the table EKBE, EKKO. The purpose of the report is to find the pending IR against the GR for a given period and vendor.

Whenever I execute this report, the sytem throws Run-time error for Maximum time limit exceed.

The code written is as follows :

*************************************************************************

START OF SELECTION.

************************************************************************

* GR PART UPDATION * *

SELECT * FROM EKBE

INTO CORRESPONDING FIELDS OF TABLE I_PO_ITEM_GR

WHERE GJAHR EQ P_GJAHR AND

BEWTP = 'E' AND

BUDAT IN GRDAT AND

MATNR IN SMATL.

SORT I_PO_ITEM_GR BY BELNR.

LOOP AT I_PO_ITEM_GR.

IF I_PO_ITEM_GR-BWART = '101'.

I_PO_ITEM_GR-MENGE = I_PO_ITEM_GR-MENGE * 1.

I_PO_ITEM_GR-DMBTR = I_PO_ITEM_GR-DMBTR * 1.

ELSEIF I_PO_ITEM_GR-BWART = '102'.

I_PO_ITEM_GR-MENGE = I_PO_ITEM_GR-MENGE * -1.

I_PO_ITEM_GR-DMBTR = I_PO_ITEM_GR-DMBTR * -1.

MODIFY I_PO_ITEM_GR INDEX SY-TABIX.

ENDIF.

ENDLOOP.

----

* IR PART UPDATION * *

LOOP AT I_PO_ITEM_GR.

SELECT * FROM EKBE

INTO CORRESPONDING FIELDS OF TABLE I_PO_ITEM_IR_TEMP

WHERE GJAHR EQ P_GJAHR AND

BEWTP = 'Q' AND

EBELP = I_PO_ITEM_GR-EBELP AND

LFBNR = I_PO_ITEM_GR-BELNR.

APPEND LINES OF I_PO_ITEM_IR_TEMP TO I_PO_ITEM_IR.

ENDLOOP.

----

* GR UPDATION WITH PO DETAILS * *

LOOP AT I_PO_ITEM_GR.

CTRL = SY-TABIX.

READ TABLE I_PO_ITEM_IR WITH KEY LFBNR = I_PO_ITEM_GR-BELNR

EBELP = I_PO_ITEM_GR-EBELP.

IF SY-SUBRC = 0.

MOVE :

I_PO_ITEM_IR-BELNR TO I_PO_ITEM_GR-IRNUM,

I_PO_ITEM_IR-BUDAT TO I_PO_ITEM_GR-IRDAT,

I_PO_ITEM_IR-MENGE TO I_PO_ITEM_GR-MENGE_IR,

I_PO_ITEM_IR-DMBTR TO I_PO_ITEM_GR-DMBTR_IR.

MODIFY I_PO_ITEM_GR INDEX CTRL.

ENDIF.

CLEAR CTRL.

ENDLOOP.

PO details updation*********************************

LOOP AT I_PO_ITEM_GR.

CTRL1 = SY-TABIX.

SELECT SINGLE LIFNR BUKRS BSART BEDAT

INTO (WA_VENDOR, WA_BUKRS, WA_BSART, WA_BEDAT)

FROM EKKO

WHERE EBELN = I_PO_ITEM_GR-EBELN.

MOVE :

WA_VENDOR TO I_PO_ITEM_GR-LIFNR,

WA_BUKRS TO I_PO_ITEM_GR-BUKRS,

WA_BSART TO I_PO_ITEM_GR-BSART,

WA_BEDAT TO I_PO_ITEM_GR-BEDAT.

MODIFY I_PO_ITEM_GR INDEX CTRL1.

CLEAR : WA_VENDOR, WA_BUKRS, WA_BSART, WA_BEDAT.

ENDLOOP.

***********************************************************

Kindly look into this, and guide me the solution to improve the performace.

TIA.

Regards,

Mark K

Former Member · ‎2007 Oct 05

hI

YOUR PUTING select STATEMENT IN THE loop THATS WHY ITS TAKING MORE TIME

TRY TO AVOID THAT AND USE READ STATEMENT

YOUR USEING SELECT STSTEMENT IN LOOP 2 TIMES

AND DON'T USE CORRESPONDING FIELDS OF BECAUSE IT MAY ALSO PERFORMANCE ISSUE FOR THIS YOU NEED TO PUT THE INTERNAL TABLE FIELDS IN THE ORDER OF SELECT STSEMENT

REWARD IF USEFULL

*************************************************************************

START OF SELECTION.

************************************************************************

* GR PART UPDATION * *

SELECT * FROM EKBE

INTO CORRESPONDING FIELDS OF TABLE I_PO_ITEM_GR

WHERE GJAHR EQ P_GJAHR AND

BEWTP = 'E' AND

BUDAT IN GRDAT AND

MATNR IN SMATL.

SORT I_PO_ITEM_GR BY BELNR.

LOOP AT I_PO_ITEM_GR.

IF I_PO_ITEM_GR-BWART = '101'.

I_PO_ITEM_GR-MENGE = I_PO_ITEM_GR-MENGE * 1.

I_PO_ITEM_GR-DMBTR = I_PO_ITEM_GR-DMBTR * 1.

ELSEIF I_PO_ITEM_GR-BWART = '102'.

I_PO_ITEM_GR-MENGE = I_PO_ITEM_GR-MENGE * -1.

I_PO_ITEM_GR-DMBTR = I_PO_ITEM_GR-DMBTR * -1.

MODIFY I_PO_ITEM_GR INDEX SY-TABIX.

ENDIF.

ENDLOOP.

----

* IR PART UPDATION * *

LOOP AT I_PO_ITEM_GR.

SELECT * FROM EKBE

INTO CORRESPONDING FIELDS OF TABLE I_PO_ITEM_IR_TEMP

WHERE GJAHR EQ P_GJAHR AND

BEWTP = 'Q' AND

EBELP = I_PO_ITEM_GR-EBELP AND

LFBNR = I_PO_ITEM_GR-BELNR.

APPEND LINES OF I_PO_ITEM_IR_TEMP TO I_PO_ITEM_IR.

ENDLOOP.

----

* GR UPDATION WITH PO DETAILS * *

LOOP AT I_PO_ITEM_GR.

CTRL = SY-TABIX.

READ TABLE I_PO_ITEM_IR WITH KEY LFBNR = I_PO_ITEM_GR-BELNR

EBELP = I_PO_ITEM_GR-EBELP.

IF SY-SUBRC = 0.

MOVE :

I_PO_ITEM_IR-BELNR TO I_PO_ITEM_GR-IRNUM,

I_PO_ITEM_IR-BUDAT TO I_PO_ITEM_GR-IRDAT,

I_PO_ITEM_IR-MENGE TO I_PO_ITEM_GR-MENGE_IR,

I_PO_ITEM_IR-DMBTR TO I_PO_ITEM_GR-DMBTR_IR.

MODIFY I_PO_ITEM_GR INDEX CTRL.

ENDIF.

CLEAR CTRL.

ENDLOOP.

PO details updation*********************************

LOOP AT I_PO_ITEM_GR.

CTRL1 = SY-TABIX.

SELECT SINGLE LIFNR BUKRS BSART BEDAT

INTO (WA_VENDOR, WA_BUKRS, WA_BSART, WA_BEDAT)

FROM EKKO

WHERE EBELN = I_PO_ITEM_GR-EBELN.

MOVE :

WA_VENDOR TO I_PO_ITEM_GR-LIFNR,

WA_BUKRS TO I_PO_ITEM_GR-BUKRS,

WA_BSART TO I_PO_ITEM_GR-BSART,

WA_BEDAT TO I_PO_ITEM_GR-BEDAT.

MODIFY I_PO_ITEM_GR INDEX CTRL1.

CLEAR : WA_VENDOR, WA_BUKRS, WA_BSART, WA_BEDAT.

ENDLOOP.

Former Member · ‎2007 Oct 05

Try trx MB5S.. I think it does the same thing which u r trying to do.... in addition it has more powerfull drill down feature...

Former Member · ‎2007 Oct 05

Hi

In your first query GR PART UPDATION you have used EKBE table

and selection parameter which you are using is niether a primary key

nor a secondary key. So it will take lot of time to execute the same

You can use another table MKPF & MSEG.

i can give you the sample query which you can modify as per your requirement

select mseg~mblnr

mseg~mjahr

mseg~zeile

mseg~lfbja

mseg~lfbnr

mseg~lfpos

mseg~werks

mseg~bwart

mkpf~budat

mseg~matnr

mseg~menge

mseg~dmbtr

mseg~shkzg

mseg~ebeln

mseg~ebelp

mkpf~xblnr

mseg~lifnr

into corresponding fields of table i_mseg

from ( mkpf inner join mseg on mkpf_{mblnr = mseg}mblnr and

mkpf_{mjahr = mseg}mjahr )

where mkpf~budat in grdat

and mkpf~vgart eq 'WE'

and mkpf~blart eq 'WE'

and ( mseg~bwart eq '101' or

mseg~bwart eq '102' ).

I think rest of the code has no problem since all queries are using proper index key

Please reward if helpful.

Regards

Harish

*************************************************************************

START OF SELECTION.

************************************************************************

* GR PART UPDATION * *

SELECT * FROM EKBE

INTO CORRESPONDING FIELDS OF TABLE I_PO_ITEM_GR

WHERE GJAHR EQ P_GJAHR AND

BEWTP = 'E' AND

BUDAT IN GRDAT AND

MATNR IN SMATL.

SORT I_PO_ITEM_GR BY BELNR

Former Member · ‎2007 Oct 05

Hi Mark,

A quick glance through your code will tell you the following.

1) You seem to be retrieving all the fields of table EKBE ( SELECT * ) and don't seem to be using all the data. Preferably create a field list and include only those fields that you need. This will free up some memory.

2) You seem to be using INTO CORRESPONDING FIELDS OF TABLE. This will slow down your select because SAP will try to match field names in your internal table with those from the database before it decides what data goes to which field in your internal table. This is an unnecessary overhead. Use INTO TABLE instead and pre-arrange your field list to match with the sequence of fields in your internal table.

3) When BWART = '101' you are multiplying MENGE and DMBTR with 1. Why?????

4) You seem to be using select statements within a LOOP statement. This is bad from the performance point of view. When you execute a SELECT statement SAP established contact with the database across the 3 tier architecture. When you do this in a within a LOOP for each time you execute the SELECT statement SAP will establish contact with the database across the 3 tier architecture (unless your table is fully buffered). This means that the greater the number of records in the internal table you are looping into the greater the number of times SAP contacts the database. This causes performance degradation.

5) Finally try to optimize the usage of LOOPs. In your code you have looped through internal table 'i_po_item_gr' 4 times. I believe that you can achieve the same results by looping at this table just once. Please take a look at my code below where I have tried to duplicate what you are doing using just one loop.

TABLES: ekbe.

PARAMETER: p_gjahr TYPE ekbe-gjahr.

SELECT-OPTIONS: grdat FOR ekbe-budat,
                smatl FOR ekbe-matnr.

TYPES: BEGIN OF ty_ekbe,
         ebeln    TYPE ekbe-ebeln,
         ebelp    TYPE ekbe-ebelp,
         zekkn    TYPE ekbe-zekkn,
         vgabe    TYPE ekbe-vgabe,
         gjahr    TYPE ekbe-gjahr,
         belnr    TYPE ekbe-belnr,
         buzei    TYPE ekbe-buzei,
         menge    TYPE ekbe-menge,
         dmbtr    TYPE ekbe-dmbtr,
         bwart    TYPE ekbe-bwart,
         lfbnr    TYPE ekbe-lfbnr,
         budat    TYPE ekbe-budat,
         bewtp    TYPE ekbe-bewtp,
         irnum    TYPE ekbe-belnr,
         irdat    TYPE ekbe-budat,
         menge_ir TYPE ekbe-menge,
         dmbtr_ir TYPE ekbe-dmbtr,
         lifnr    TYPE ekko-lifnr,
         bukrs    TYPE ekko-bukrs,
         bsart    TYPE ekko-bsart,
         bedat    TYPE ekko-bedat,
       END OF ty_ekbe,

       BEGIN OF ty_ekko,
         ebeln TYPE ekko-ebeln,
         lifnr TYPE ekko-lifnr,
         bukrs TYPE ekko-bukrs,
         bsart TYPE ekko-bsart,
         bedat TYPE ekko-bedat,
       END OF ty_ekko.


DATA: w_index       TYPE                 sy-tabix,
      t_ekbe        TYPE        TABLE OF ty_ekbe ,
      w_ekbe_gr     TYPE                 ty_ekbe ,
      t_ekbe_gr     TYPE        TABLE OF ty_ekbe ,
      t_ekbe_gr_tmp TYPE        TABLE OF ty_ekbe ,
      w_ekbe_ir     TYPE                 ty_ekbe ,
      t_ekbe_ir     TYPE SORTED TABLE OF ty_ekbe
        WITH NON-UNIQUE KEY lfbnr ebelp          ,
      w_ekko        TYPE                 ty_ekko ,
      t_ekko        TYPE HASHED TABLE OF ty_ekko
        WITH UNIQUE KEY ebeln                    .


SELECT ebeln
       ebelp
       zekkn
       vgabe
       gjahr
       belnr
       buzei
       menge
       dmbtr
       bwart
       lfbnr
       budat
       bewtp
  FROM ekbe
  INTO TABLE t_ekbe
  WHERE gjahr EQ p_gjahr
  AND   bewtp IN ('E', 'Q')
  AND   budat IN grdat
  AND   matnr IN smatl.

IF sy-subrc EQ 0.

  t_ekbe_gr[] = t_ekbe[].
  DELETE t_ekbe_gr WHERE bewtp EQ 'Q'.

  INSERT LINES OF t_ekbe INTO TABLE t_ekbe_ir.
  DELETE t_ekbe_ir WHERE bewtp EQ 'E'.

  IF NOT t_ekbe_gr[] IS INITIAL.

    t_ekbe_gr_tmp[] = t_ekbe_gr[].

    SORT t_ekbe_gr_tmp BY ebeln.

    DELETE ADJACENT DUPLICATES FROM t_ekbe_gr_tmp COMPARING ebeln.

    SELECT ebeln
           lifnr
           bukrs
           bsart
           bedat
      FROM ekko
      INTO TABLE t_ekko
      FOR ALL ENTRIES IN t_ekbe_gr_tmp
      WHERE ebeln EQ t_ekbe_gr_tmp-ebeln.

  ENDIF.

  LOOP AT t_ekbe_gr INTO w_ekbe_gr.

    w_index = sy-tabix.

    CASE w_ekbe_gr-bwart.
      WHEN '102'.
        MULTIPLY: w_ekbe_gr-menge BY -1,
                  w_ekbe_gr-dmbtr BY -1.
    ENDCASE.

    READ TABLE t_ekbe_ir INTO w_ekbe_ir
      WITH KEY lfbnr = w_ekbe_gr-belnr
               ebelp = w_ekbe_gr-ebelp
               TRANSPORTING
                 belnr
                 budat
                 menge
                 dmbtr.

    IF sy-subrc EQ 0.
      w_ekbe_gr-irnum    = w_ekbe_ir-belnr.
      w_ekbe_gr-irdat    = w_ekbe_ir-budat.
      w_ekbe_gr-menge_ir = w_ekbe_ir-menge.
      w_ekbe_gr-dmbtr_ir = w_ekbe_ir-dmbtr.
    ENDIF.

    READ TABLE t_ekko INTO w_ekko
      WITH KEY ebeln = w_ekbe_gr-ebeln
               TRANSPORTING
                 lifnr
                 bukrs
                 bsart
                 bedat.
    IF sy-subrc EQ 0.
      w_ekbe_gr-lifnr = w_ekko-lifnr.
      w_ekbe_gr-bukrs = w_ekko-bukrs.
      w_ekbe_gr-bsart = w_ekko-bsart.
      w_ekbe_gr-bedat = w_ekko-bedat.
    ENDIF.

    MODIFY t_ekbe_gr FROM w_ekbe_gr INDEX w_index
      TRANSPORTING
        menge
        dmbtr
        irnum
        irdat
        menge_ir
        dmbtr_ir
        lifnr
        bukrs
        bsart
        bedat.

  ENDLOOP.

ENDIF.

Former Member · ‎2007 Oct 06

Hi Mark,

Please take a note of the points given below.

1- if all the fields in ekbe is not required then

please declare an internal tabl with only the necessary fields and write the select including only those fields rather than select *

which will improve the performance to some extend.

2.If possible try to include more key fields of the table in the where condition which will make the database access faster Or if you can include

only those fields which form the table index also the access will be faster.

3 > Never ever use select statement inside loop. Use "for all entries' instead.

But while using for all entries we need to first check

if I_PO_ITEM_GR is initial

only if it is not initial we need to go for 'for all entries'.

then using adjacent duplicates from I_PO_ITEM_GR comapring key fieldsif included

before the "for all entries' statement will improve the performance.

Former Member · ‎2007 Oct 08

Hi,

Dont use select statement with in loop and also INTO CORRSPONDING fileds statement.

*************************************************************************

START OF SELECTION.

************************************************************************

* GR PART UPDATION * *

SELECT field1 field2..... FROM EKBE

INTO TABLE I_PO_ITEM_GR " use fileds names which you declared in internal table I_PO_ITEM_GR instead of ' * '

WHERE GJAHR EQ P_GJAHR AND

BEWTP = 'E' AND

BUDAT IN GRDAT AND

MATNR IN SMATL.

SORT I_PO_ITEM_GR BY BELNR.

LOOP AT I_PO_ITEM_GR.

IF I_PO_ITEM_GR-BWART = '101'.

I_PO_ITEM_GR-MENGE = I_PO_ITEM_GR-MENGE * 1.

I_PO_ITEM_GR-DMBTR = I_PO_ITEM_GR-DMBTR * 1.

ELSEIF I_PO_ITEM_GR-BWART = '102'.

I_PO_ITEM_GR-MENGE = I_PO_ITEM_GR-MENGE * -1.

I_PO_ITEM_GR-DMBTR = I_PO_ITEM_GR-DMBTR * -1.

MODIFY I_PO_ITEM_GR INDEX SY-TABIX.

ENDIF.

ENDLOOP.

if not I_PO_ITEM_GR [] is initial.

SELECT field1 field2..... FROM EKBE

INTO TABLE I_PO_ITEM_IR

FOR ALL INTRIES IN I_PO_ITEM_GR

WHERE GJAHR EQ P_GJAHR AND

BEWTP = 'Q' AND

EBELP = I_PO_ITEM_GR-EBELP AND

LFBNR = I_PO_ITEM_GR-BELNR.

IF sy-subrc = 0.

sort I_PO_ITEM_IR by belnr ebelp.

endif.

SELECT SINGLE LIFNR BUKRS BSART BEDAT

INTO (WA_VENDOR, WA_BUKRS, WA_BSART, WA_BEDAT)

FROM EKKO

WHERE EBELN = I_PO_ITEM_GR-EBELN.

"INSTEAD of the above code

SELECT LIFNR BUKRS BSART BEDAT

INTO table tbl_ekko

FROM EKKO

FOR ALL ENTRIES IN I_PO_ITEM_GR

WHERE EBELN = I_PO_ITEM_GR-EBELN.

SORT tbl_ekko by BELNR EBLEN.

endif.

LOOP AT I_PO_ITEM_GR.

CTRL = SY-TABIX.

READ TABLE I_PO_ITEM_IR WITH KEY LFBNR = I_PO_ITEM_GR-BELNR

EBELP = I_PO_ITEM_GR-EBELP BINARY SEARCH.

IF SY-SUBRC = 0.

MOVE :

I_PO_ITEM_IR-BELNR TO I_PO_ITEM_GR-IRNUM,

I_PO_ITEM_IR-BUDAT TO I_PO_ITEM_GR-IRDAT,

I_PO_ITEM_IR-MENGE TO I_PO_ITEM_GR-MENGE_IR,

I_PO_ITEM_IR-DMBTR TO I_PO_ITEM_GR-DMBTR_IR.

ENDIF.

READ TABLE TBL_EKKO WITH KEY LFBNR = I_PO_ITEM_GR-BELNR

EBELN = I_PO_ITEM_GR-EBELN BINARY SEARCH.

IF SY-SUBRC = 0 .

MOVE :

WA_VENDOR TO I_PO_ITEM_GR-LIFNR,

WA_BUKRS TO I_PO_ITEM_GR-BUKRS,

WA_BSART TO I_PO_ITEM_GR-BSART,

WA_BEDAT TO I_PO_ITEM_GR-BEDAT

ENDIF.

MODIFY I_PO_ITEM_GR INDEX CTRL.

CLEAR I_PO_ITEM_GR,I_PO_ITEM_IR ,TBL_EKKO, CTRL.

ENDLOOP.

Hope above code is help ful for you.

And also here i am giving some tips for select and internal tables.

Internal Tables

Internal tables are considered to be the true work horse of ABAP. They can have a big impact on processing and performance when programmed incorrectly. Use the quick checklist below to help guide you when coding internal table(itab) processing.

Internal Table Quick Checklist

Loop at itab must always use a work area or assign to a field symbol
Use parallel cursor technique for nested loops.
Use the TRANSPORTING clause with READ and MODIFY wherever possible to tranport only the fields necessary
Use TRANSPORTING No FIELDS clause when checking only for existence of a record
Read by INDEX is the fastest access option for a single READ. Use standard table if you are accessing mainly by index. Consider this where possible but use caution as it does not apply to all programming situations.
Specify full key on a table read whenever possible. Use ‘WITH TABLE KEY’ clause when full key is specified
Internal Tables should be passed to FORMS with the "USING" clause. The "TABLES" clause is considered obsolete.
Internal tables should be passed to FORMS by Reference for performance reasons

i.e. do not use USING Value(..)

Do not use Occurs 0 or With Header Line unless it is a SAP function that requires it.
Hashed tables are a good performance approach over standard tables whenever random record accesses are required for a large internal table using the fully qualified key.
When sorting internal tables, always use "SORT BY Key1...n", never just "SORT" on it's own
Standard Tables require a Sort by, Delete adjacent Duplicates, and READ itab with KEY...Binary Search
Keep "SORT itab BY" statement as close as possible to the READ itab with KEY...Binary Search.
Delete Adjacent Duplicates should always be explicit by using the COMPARING clause, even if there is only one field in the itab
Standard Tables should be sorted by sorting keys to take advantage of Binary Search. However, if you sort by one key and Read with a different set, you could miss data

SQL Quick Checklist

Select Statements within Loop processing is not recommended. Preferred approach is to select data into an itab and then read the itab to access specific records
Do not use Nested Selects, Selects within Loops. or SELECT...ENDSELECT
Do not use Select * unless at least 70% of fields are needed

Select only the fields you require.
Do not use INTO CORRESPONDING
Do not do Order By on non key fields
Force optimizer to use the index where possible
If primary index can not be used, look for alternate indexes or alternate index tables
Avoid Use of LIKE in the Where clause on index fields. It will force a non index read.
Avoid Use of NOT conditions in the Where clause on index fields. It will force a non index read.
Select Single MUST have the primary key fully specified in the WHERE clause. Otherwise use Select.. Up to 1 Rows.
Avoid DISTINCT see performance standards for usage
Consider filtering on the appserver rather than in a WHERE statement
SAP Recommendation on Joins - try not to exceed a 3 Table Join

When using "Select.. For all Entries". The following 4 rules MUST be followed:

o Check to make sure driver itab is not empty

o Always SORT the itab (driver table) by keys. Specify all keys used in the Where clause

o DELETE Adjacent Duplicates Comparing the keys that were sorted.

o All Primary Key Fields must be in the Select List

Reward with points if helpful.

Regards,

Vijay

Former Member · ‎2007 Oct 09

Hey Mark,

Did any of us solve your problem. If yes please reward points and close the thread or else please let us know what went wrong.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Bad Performance - Exceed time limit