topic Re: avoiding select in loop....endloop in Application Development and Automation Discussions

avoiding select in loop....endloop

Former Member — Thu, 04 Sep 2008 05:42:06 GMT

Hi ,

select MARA~matnr

MAKTX

from mara join marc on mara_{matnr = marc}matnr

JOIN makt on mara_{matnr = makt}matnr

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 EKPO_{EBELN = EKKO}EBELN

JOIN EKET ON EKKO_{EBELN = EKET}EBELN

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 EKPO_{EBELN = EKKO}EBELN

JOIN EKET ON EKKO_{EBELN = EKET}EBELN

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.

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

Re: avoiding select in loop....endloop

Former Member — Thu, 04 Sep 2008 07:13:56 GMT

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.

Re: avoiding select in loop....endloop

Former Member — Thu, 04 Sep 2008 09:12:54 GMT

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 ekpo_{ebeln = ekko}ebeln

JOIN eket ON ekko_{ebeln = eket}ebeln

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

Re: avoiding select in loop....endloop

Former Member — Thu, 04 Sep 2008 10:58:30 GMT

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 mara_{matnr = marc}matnr

JOIN makt on mara_{matnr = makt}matnr

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 EKPO_{EBELN = EKKO}EBELN

JOIN EKET ON EKKO_{EBELN = EKET}EBELN

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

Re: avoiding select in loop....endloop

Former Member — Fri, 05 Sep 2008 04:14:19 GMT

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

Re: avoiding select in loop....endloop

Former Member — Fri, 05 Sep 2008 04:21:21 GMT

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

Re: avoiding select in loop....endloop

Former Member — Tue, 09 Sep 2008 12:57:31 GMT

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

Re: avoiding select in loop....endloop

Former Member — Fri, 18 Feb 2011 06:30:27 GMT

answered