Skip to Content
avatar image
Former Member

avoiding select in loop....endloop

Hi ,

select MARA~matnr

MAKTX

from mara join marc on maramatnr = marcmatnr

JOIN makt on maramatnr = maktmatnr

into table itab

where mtart = 'FERT' AND

WERKS = P_PLANT .

LOOP AT ITAB.

  • CALCULATING PENDING ORDERS

select SUM( EKET~MENGE )

SUM( EKET~GLMNG )

INTO (ITAB-MENGE ,ITAB-GLMNG )

from

EKKO JOIN EKPO ON EKPOEBELN = EKKOEBELN

JOIN EKET ON EKKOEBELN = EKETEBELN

where

EKET~EINDT LE ZFDATE and

EKET~EINDT GE P_DATE AND

EKPO~MATNR = ITAB-MATNR and

EKPO~WERKS = P_PLANT and

EKKO~BSART = 'UB' .

IF SY-SUBRC = 0.

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

    • UNIT CONVERSION OF PENDING ORDERS added by k srinivas

    select SINGLE EKPO~MEINS "ORDER UNIT

    EKPO~UMREZ "1 PACK = 'UMREZ' LITRES

    EKPO~LMEIN "BASE UNIT OF MEASURE

    INTO (V_ORDER_UNIT,V_CONVERSION,V_BASE_UNIT)

    from

    EKKO JOIN EKPO ON EKPOEBELN = EKKOEBELN

    JOIN EKET ON EKKOEBELN = EKETEBELN

    where

    EKET~EINDT LE ZFDATE and

    EKET~EINDT GE P_DATE AND

    EKPO~MATNR = ITAB-MATNR and

    EKPO~WERKS = P_PLANT and

    EKKO~BSART = 'UB' .

    *CONVERSION OF QUANTITY INTO LITRES

    IF V_ORDER_UNIT = 'PAK' AND V_BASE_UNIT = 'L'.

    ITAB-MENGE = ITAB-MENGE * V_CONVERSION.

    ITAB-GLMNG = ITAB-GLMNG * V_CONVERSION.

    ENDIF.

    IF ITAB-MENGE > ITAB-GLMNG .

    ITAB-PENDO = ITAB-MENGE - ITAB-GLMNG.

    ENDIF.

    ENDIF.

    ENDLOOP.

    Because of the 'Select' statement inside the loop im getting

    Performance issue and going to dump.

    Please suggest how to bring the Select statement outside the loop and still get the same output.

    Thanks

    K Srinivas

    Add comment
    10|10000 characters needed characters exceeded

    • Follow
    • Get RSS Feed

    7 Answers

    • avatar image
      Former Member
      Sep 04, 2008 at 07:13 AM

      Hello Srinivas,

      Use for all entries instead of select statement in loop and get the values from the second and third select statement to other internal tables and loop the first internal table in the loop read the second and third internal table update your first internal table.

      Regards,

      Naresh.

      Add comment
      10|10000 characters needed characters exceeded

    • avatar image
      Former Member
      Sep 04, 2008 at 09:12 AM

      Hi,

      You need not to SUM while retriving the data from the database.That makes the database server more burden.So Delete select statement with SUM.(Instead use AT END of fileds SUM.).

      And coming to One more select,in the LOOP eliminate it by using READ statement and writing the select outside the loop with for all entries.

      Example:

      data:

      begin of itab2 occurs 0 with header line.

      meins like ekpo-meins,

      umrez llike ekpo-umrez,

      lmein like ekpo~lmein ,

      eindt type d,

      end of itab2.

      SELECT ekpo~meins "ORDER UNIT

      ekpo~umrez "1 PACK = 'UMREZ' LITRES

      ekpo~lmein "BASE UNIT OF MEASURE

      INTO table itab2

      FROM

      ekko JOIN ekpo ON ekpoebeln = ekkoebeln

      JOIN eket ON ekkoebeln = eketebeln

      WHERE

      eket~eindt LE zfdate AND

      eket~eindt GE p_date AND

      ekpo~matnr = itab-matnr AND

      ekpo~werks = p_plant AND

      ekko~bsart = 'UB' .

      loop at itab.

      read itab2 with key eindt between p_date and zfdate.

      if sy-subrc EQ 0.

      *do you action here

      endif.

      endloop.

      Regards,

      Rama.P

      Add comment
      10|10000 characters needed characters exceeded

    • avatar image
      Former Member
      Sep 04, 2008 at 10:58 AM

      Hi Srinivas,

      Using of joins inside or outside the loop can largely affect the performance of a program, So always prefer selecting data from each table,

      First select from the header table based on the condition as per your requirement.

      Then by using ' for all entries' clause, fetch from the other tables relating to that.

      1) select MARA~matnr

      MAKTX

      from mara join marc on maramatnr = marcmatnr

      JOIN makt on maramatnr = maktmatnr

      into table itab

      where mtart = 'FERT' AND

      WERKS = P_PLANT .

      This select query can be replaced by doing the following,

      SELECT MATNR

      FROM MARA INTO TABLE T_MARA

      WHERE MTART EQ FERT'.

      sort t_mara by matnr.

      if not t_mara is initial.

      SELECT MATNR

      WERKS

      FROM MARC

      INTO TABLE T_MARC

      FOR ALL ENTRIES IN T_MARA

      WHERE MATNR EQ T_MARA-MATNR

      AND WERKS EQ P_PLANT.

      endif.

      sort t_marc by matnr werks.

      if not t_marc is initial.

      SELECT MATNR

      SPRAS

      MAKTX

      FROM MAKT

      INTO TABLE T_MAKT

      FOR ALL ENTRIES IN T_MARC

      WHERE MATNR EQ T_MARC-MATNR.

      endloop.

      LOOP AT T_MAKT INTO WA_MAKT.

      READ TABLE T_MARA INTO WA_MARA WITH KEY MATNR = WA_MAKT-MATNR.

      WA_ITAB-MAKTX = WA_MAKT-MAKTX.

      WA_ITAB-MATNR = WA_MARA-MATNR.

      APPEND WA_ITAB TO T_ITAB.

      CLEAR: WA_ITAB,

      WA_MAKT,

      WA_MARA.

      ENDLOOP.

      But in this query from mara, we are giving the primary key in the where condition, so its better you can have the material number in the selection screen or you can create a index on the mtart.

      2) select SUM( EKET~MENGE )

      SUM( EKET~GLMNG )

      INTO (ITAB-MENGE ,ITAB-GLMNG )

      from

      EKKO JOIN EKPO ON EKPOEBELN = EKKOEBELN

      JOIN EKET ON EKKOEBELN = EKETEBELN

      where

      EKET~EINDT LE ZFDATE and

      EKET~EINDT GE P_DATE AND

      EKPO~MATNR = ITAB-MATNR and

      EKPO~WERKS = P_PLANT and

      EKKO~BSART = 'UB' .

      New code:

      SELECT EBELN

      BSART

      FROM EKKO

      INTO TABLE T_EKKO

      WHERE BSART EQ 'UB'.

      SELECT EBELN

      EBELP

      MATNR

      WERKS

      FROM EKPO

      INTO TABLE T_EKPO1

      FOR ALL ENTRIES IN T_ITAB

      WHERE MATNR = T_ITAB-MATNR

      AND WERKS = P_PLANT.

      LOOP AT T_EKPO1 INTO WA_EKPO1.

      READ TABLE T_EKKO INTO WA_EKKO WITH KEY EBELN = WA_EKPO1-EBELN.

      IF SY-SUBRC EQ 0.

      APPEND WA_EKPO1 TO T_EKPO2.

      ENDIF.

      CLEAR: WA_EKPO1,

      WA_ITAB,

      WA_EKKO.

      ENDLOOP.

      SELECT EBELN

      EBELP

      ETENR

      EINDT

      MENGE

      GLMNG

      FROM EKET

      INTO TABLE T_EKET

      FOR ALL ENTRIES IN T_EKPO2

      WHERE EBELN = T_EKPO2-EBELN

      AND EINDT LE '01.01.9999' "ZFDATE

      AND EINDT GE '01.01.2000'. "P_DATE.

      LOOP AT T_ITAB INTO WA_ITAB .

      READ TABLE T_EKPO2 INTO WA_EKPO1 WITH KEY MATNR = WA_ITAB-MATNR

      WERKS = P_PLANT.

      "WA_ITAB-WERKS.

      LOOP AT T_EKET INTO WA_EKET WHERE EBELN = WA_EKPO1-EBELN.

      GV_MENGE = GV_MENGE + WA_EKET-MENGE.

      GV_GLMNG = GV_GLMNG + WA_EKET-GLMNG.

      AT END OF EBELN.

      WA_ITAB-MENGE = GV_MENGE.

      WA_ITAB-GLMNG = GV_GLMNG.

      CLEAR: GV_GLMNG,

      GV_MENGE.

      ENDAT.

      CLEAR: WA_EKET.

      ENDLOOP.

      CLEAR: WA_ITAB,

      WA_EKPO1.

      ENDLOOP.

      Please follow the same methodology for the other query also.

      Hope this code should help you to get the problem resolved

      Edited by: Shobana k on Sep 4, 2008 12:59 PM

      Add comment
      10|10000 characters needed characters exceeded

    • avatar image
      Former Member
      Sep 05, 2008 at 04:14 AM

      Hi Srinivas,

      As others said using inner join in the program code is time consuming.

      So u can go for FOR ALL ENTRIES. or if required create a views, because it handle the data in external buffer.

      retrieve only those fields required for further processing, and specify maximum fields in the WHERE condition to reduces the number of records retrieved from the database level. also as you specified u can use aggreagte function for the fields required.

      regards

      GP

      Add comment
      10|10000 characters needed characters exceeded

    • avatar image
      Former Member
      Sep 05, 2008 at 04:21 AM

      Hi Srinivas,

      You can do one think, first fetch all the data into internal tables and then process the required data.

      Hitting the data base once is better than hitting several times.

      First select all the required data from the database, then process the data using READ or LOOP statement inside the LOOP statement.

      Best regards,

      raam

      Add comment
      10|10000 characters needed characters exceeded

    • avatar image
      Former Member
      Sep 09, 2008 at 12:57 PM

      Hi Srinivas

      first fetch the data for mara based on WERKS and MTART

      then use for all entries to fetch data from MAKT,

      after that use for all entries to fetch data from EKKO & EKPO respectively.

      Now run a loop on EKPO internal tables.

      inside that loop read all the tables one by one and populate the data.

      you can also calculate all the values in the loop.

      in the end of loop modify the internal table

      i hope this will work well for you

      Regards

      Rajnesh

      Add comment
      10|10000 characters needed characters exceeded

    • avatar image
      Former Member
      Feb 18, 2011 at 06:30 AM

      answered

      Add comment
      10|10000 characters needed characters exceeded