09-04-2008 6:42 AM
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
09-04-2008 8: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.
09-04-2008 10: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
09-04-2008 11: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
09-05-2008 5: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
09-05-2008 5: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
09-09-2008 1: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
02-18-2011 6:30 AM