08-27-2007 1:03 PM
hi experts,
I have made Purchase Requisition . User has to enetr plant, Date and Material. BUt it taks long time to execute. sometimes shows with Timeout. Below is my Code. can u plz send me the updations to make it more faster. iTs urgent
SELECT BADAT LFDAT BANFN MATNR TXZ01 MENGE MEINS EBELN EBELP WERKS INTO (IT_EKKO-BADAT, IT_EKKO-LFDAT, IT_EKKO-BANFN, IT_EKKO-MATNR_EB,
IT_EKKO-TXZ01_EB, IT_EKKO-MENGE_EB, IT_EKKO-MEINS_EB, IT_EKKO-EBELN_EB, IT_EKKO-EBELP_EB, IT_EKKO-WERKS)
FROM EBAN
WHERE EBAN~WERKS IN S_WERKS AND EBAN~BADAT IN S_DATE AND EBAN~MATNR IN S_MAT AND EBAN~LOEKZ = SPACE.
IF IT_EKKO-EBELN_EB <> SPACE.
SELECT EBELN EBELP AEDAT MATNR TXZ01 MENGE MEINS NETPR
INTO (IT_EKKO-EBELN, IT_EKKO-EBELP, IT_EKKO-AEDAT, IT_EKKO-MATNR, IT_EKKO-TXZ01_EP, IT_EKKO-MENGE, IT_EKKO-MEINS,
IT_EKKO-NETPR)
FROM EKPO
WHERE EKPO~EBELN = IT_EKKO-EBELN_EB AND EKPO~EBELP = IT_EKKO-EBELP_EB AND EKPO~LOEKZ = SPACE.
it_ekko-pend = IT_EKKO-MENGE_EB - IT_EKKO-MENGE.
SELECT single BEDAT RESWK INTO (IT_EKKO-BEDAT, IT_EKKO-RESWK)
FROM EKKO
WHERE EKKO~EBELN = IT_EKKO-EBELN.
ENDSELECT.
IF SY-SUBRC = 0 AND IT_EKKO-EBELN_EB <> SPACE.
SELECT ERDAT VBELN MATNR ARKTX LFIMG MEINS VGBEL INTO (IT_EKKO-ERDAT, IT_EKKO-VBELN, IT_EKKO-MATNR_LI,
IT_EKKO-ARKTX, IT_EKKO-LFIMG, IT_EKKO-MEINS_LI, IT_EKKO-VGBEL)
FROM LIPS
WHERE LIPS~VGBEL = IT_EKKO-EBELN AND LIPS~VGPOS = IT_EKKO-EBELP.
IF SY-SUBRC = 0.
n = 1.
LOOP AT IT_TRFRATE WHERE matnr_tr = it_ekko-matnr_li.
IF N = 1.
IT_EKKO-TRFRATE = IT_TRFRATE-TRFRATE.
select single umren from marm into vumren where marm~matnr = IT_EKKO-MATNR
and ( marm~meinh = 'L' or marm~meinh = 'KG' ).
endselect.
select single umrez from marm into vumrez where marm~matnr = IT_EKKO-MATNR
and ( marm~meinh = 'L' or marm~meinh = 'KG' ).
endselect.
compute vlfimg = ( IT_EKKO-LFIMG * vumren ) / vumrez.
IT_EKKO-NETPR = VLFIMG * IT_EKKO-TRFRATE.
CLEAR IT_EKKO-PEND_LI.
it_ekko-pend_li = IT_EKKO-MENGE_EB - IT_EKKO-LFIMG.
N = N + 1.
ENDIF.
ENDLOOP.
SELECT single WBSTK INTO (IT_EKKO-WBSTK)
FROM VBUK
WHERE VBUK~VBELN = IT_EKKO-VBELN.
ENDSELECT.
ELSE.
n = 1.
LOOP AT IT_TRFRATE WHERE matnr_tr = it_ekko-matnr_li.
IF N = 1.
IT_EKKO-TRFRATE = IT_TRFRATE-TRFRATE.
select single umren from marm into vumren where marm~matnr = IT_EKKO-MATNR
and ( marm~meinh = 'L' or marm~meinh = 'KG' ).
endselect.
select single umrez from marm into vumrez where marm~matnr = IT_EKKO-MATNR
and ( marm~meinh = 'L' or marm~meinh = 'KG' ).
endselect.
compute vlfimg = ( IT_EKKO-LFIMG * vumren ) / vumrez.
IT_EKKO-NETPR = VLFIMG * IT_EKKO-TRFRATE.
CLEAR IT_EKKO-PEND_LI.
it_ekko-pend_li = IT_EKKO-MENGE_EB - IT_EKKO-LFIMG.
N = N + 1.
ENDIF.
ENDLOOP.
SELECT single WBSTK INTO (IT_EKKO-WBSTK)
FROM VBUK
WHERE VBUK~VBELN = IT_EKKO-VBELN.
ENDSELECT.
ENDIF.
ENDSELECT.
IT_EKKO-PEND_LI = IT_EKKO-MENGE - IT_EKKO-LFIMG.
APPEND IT_EKKO.
CLEAR IT_EKKO.
ELSE.
IT_EKKO-PEND_LI = IT_EKKO-MENGE - IT_EKKO-LFIMG.
APPEND IT_EKKO.
ENDIF.
CLEAR IT_EKKO.
ENDSELECT.
IF SY-SUBRC <> 0.
it_ekko-pend = IT_EKKO-MENGE_EB.
APPEND IT_EKKO.
ENDIF.
ELSE.
it_ekko-pend = IT_EKKO-MENGE_EB.
APPEND IT_EKKO.
ENDIF.
ENDSELECT.
******************************************************************************************
THanks.
Khan
08-27-2007 1:06 PM
Hi!
Remove the larges SELECT - ENDSELECT from your program.
Try to replace it with SELECT ... INTO TABLE ... statement and then LOOP AT ... ENDLOOP on your table.
Use SE30 also for runtime analysis.
Regards
Tamá
08-29-2007 6:55 AM
HI EXPERTS,
i HAVE USED SELECT SINGLE INSTED OF SELECT..........ENDSELECT. BUT THERE IS NO IMPROVEMENT IN SPEED. WHAT ELSE i CAN DO. iTS URGENT. pLZ HELP.
tHANKS
kHAN
08-29-2007 1:57 PM
Of course not. I repeat my question:
Is there an idex on lips-vgbel and lips-vbpos in your system?
Rob
08-31-2007 6:37 AM
ho rob,
I didnt know abt index on lips. how can i find. plz help.
Thanks.
Khan
08-31-2007 2:05 PM
go to the data dictionary; enter lips as t etable and press 'display'. On the display screen, there are a number of buttons; press 'indexes' and tell us if there is anything. In our system there is, but it looks like a "Z" index.
Rob
08-27-2007 1:48 PM
Check other postings on SQL trace and run SQL Trace, get SQL Summary,
and post it here.
=> This will show you where the runtime is spend.
Siegfried
08-27-2007 2:26 PM
Hi
reward if usefull
SELECT ENDSELECT makes the loop so it will take so much time to execute the program
if you avoid this then it will be very fast execution
see this
Improve performance of SELECT
This tip has been copied from SearchSap.com
Tip submitted by: Ben Meijs
As you all know, it is important to use as many key fields as possible in WHERE clauses of SELECT
statements. Sometimes you are not sure about the value of some key
fields. They seem to be empty (Initial value) but you are afraid to use this in your ABAP coding.
Using the option GE (greater equal) in your coding can improve your performance considerably
without the risk of table lines not being selected.
Code
REPORT ZZBM_SELECT_1 .
TABLES: S001.
CONSTANTS: SSOUR_INI LIKE S001-SSOUR VALUE IS INITIAL,
VRSIO_INI LIKE S001-VRSIO VALUE IS INITIAL,
SPMON_INI LIKE S001-SPMON VALUE IS INITIAL,
SPTAG_INI LIKE S001-SPTAG VALUE IS INITIAL,
SPWOC_INI LIKE S001-SPWOC VALUE IS INITIAL,
SPBUP_INI LIKE S001-SPBUP VALUE IS INITIAL.
DATA: TA_S001 TYPE STANDARD TABLE OF S001.
DATA: WA_S001 TYPE S001.
SELECT-OPTIONS:
SO_SPTAG FOR S001-SPTAG OBLIGATORY,
SO_KUNNR FOR S001-KUNNR OBLIGATORY,
SO_VKORG FOR S001-VKORG OBLIGATORY,
SO_VTWEG FOR S001-VTWEG OBLIGATORY,
SO_SPART FOR S001-SPART OBLIGATORY,
SO_MATNR FOR S001-MATNR OBLIGATORY.
START-OF-SELECTION.
SELECT * FROM S001
INTO TABLE TA_S001
WHERE SSOUR GE SSOUR_INI "Greater Equal initial value
AND VRSIO GE VRSIO_INI " idem
AND SPMON GE SPMON_INI " idem
AND SPTAG IN SO_SPTAG
AND SPWOC GE SPWOC_INI " idem
AND SPBUP GE SPBUP_INI " idem
AND KUNNR IN SO_KUNNR
AND VKORG IN SO_VKORG
AND VTWEG IN SO_VTWEG
AND SPART IN SO_SPART
AND MATNR IN SO_MATNR.
Package size in SELECT statements
Package size can be used to retreive a spcific number of records at a time. This can be used if you
for example only want tofinish processing a limited amount of data at a time due to lack of memory.
The exampel below read 50 records at a time from VBAK into an internal table, and selects the
corresponding entries from vbap into an internal table. Then the two internal tables can be
processed, and the next 50 records from VBAk can be read. remeber to reinitialize tha tables before
the next read.
Note the usage of SELECT - ENDSELECT !
REPORT z_test .
TYPES:
BEGIN OF t_vbak,
vbeln LIKE vbak-vbeln,
erdat LIKE vbak-erdat,
END OF t_vbak,
BEGIN OF t_vbap,
posnr LIKE vbap-posnr,
matnr LIKE vbap-matnr,
meins LIKE vbap-meins,
END OF t_vbap,
BEGIN OF t_report,
vbeln LIKE vbak-vbeln,
erdat LIKE vbak-erdat,
posnr LIKE vbap-posnr,
matnr LIKE vbap-matnr,
meins LIKE vbap-meins,
END OF t_report.
DATA:
li_vbak TYPE t_vbak OCCURS 0,
l_vbak TYPE t_vbak,
li_vbap TYPE t_vbap OCCURS 0,
l_vbap TYPE t_vbap,
li_report TYPE t_report OCCURS 0,
l_report TYPE t_report.
START-OF-SELECTION.
SELECT vbeln erdat
FROM vbak
INTO TABLE li_vbak PACKAGE SIZE 50.
SELECT posnr matnr meins
FROM vbap
INTO TABLE li_vbap
FOR ALL ENTRIES IN li_vbak
WHERE vbeln = li_vbak-vbeln.
IF sy-subrc = 0.
Now you have the two internal tables li_vbak and liÆ_vbap filled with data.
Do something with the data - remember to reinitialize internal tables
ENDIF.
ENDSELECT.
All of the product names here are trademarks of their respective companies. The site
www.allsaplinks.com no way affiliated with SAP AG. We have made every effort for the content
integrity. Information used on this site is at your own risk.
08-27-2007 4:15 PM
Is there an idex on lips-vgbel and lips-vbpos in your system?
Rob
08-30-2007 10:34 AM
08-31-2007 6:57 AM
Put the variables on selection screen and put /h before executing. The program will run in debug mode. Press F5 and see exactly in which which queries it is taking time. Send me those queries, only then I will try to analyze the things and come up with the answer.
Regards,
Mallick
08-31-2007 11:09 AM
I have now chage my code and now below is the new code. plz hav a look and send me suggestions if any modifications required.
SELECT BADAT LFDAT BANFN MATNR TXZ01 MENGE MEINS EBELN EBELP WERKS INTO (IT_EKKO-BADAT, IT_EKKO-LFDAT, IT_EKKO-BANFN, IT_EKKO-MATNR_EB,
IT_EKKO-TXZ01_EB, IT_EKKO-MENGE_EB, IT_EKKO-MEINS_EB, IT_EKKO-EBELN_EB, IT_EKKO-EBELP_EB, IT_EKKO-WERKS)
from eban
WHERE EBAN~BANFN IN S_BANFN AND LOEKZ = SPACE.
append it_ekko.
endselect.
clear it_ekko.
loop at it_ekko.
IF it_ekko-EBELN_EB <> SPACE.
SELECT SINGLE EBELN EBELP AEDAT MATNR TXZ01 MENGE MEINS NETPR
INTO (IT_EKKO-EBELN, IT_EKKO-EBELP, IT_EKKO-AEDAT, IT_EKKO-MATNR, IT_EKKO-TXZ01_EP, IT_EKKO-MENGE, IT_EKKO-MEINS,
IT_EKKO-NETPR)
FROM EKPO
WHERE EKPO~EBELN = IT_EKKO-EBELN_EB AND EKPO~EBELP = IT_EKKO-EBELP_EB and EKPO~LOEKZ = SPACE.
it_ekko-pend = IT_EKKO-MENGE_EB - IT_EKKO-MENGE.
SELECT single BEDAT RESWK INTO (IT_EKKO-BEDAT, IT_EKKO-RESWK)
FROM EKKO
WHERE EKKO~EBELN = IT_EKKO-EBELN.
modify it_ekko.
clear it_ekko.
else.
it_ekko-pend = IT_EKKO-MENGE_EB.
modify it_ekko.
endif.
endloop.
loop at it_ekko where ebeln <> space.
SELECT SINGLE ERDAT VBELN MATNR ARKTX LFIMG MEINS VGBEL INTO (IT_EKKO-ERDAT, IT_EKKO-VBELN, IT_EKKO-MATNR_LI,
IT_EKKO-ARKTX, IT_EKKO-LFIMG, IT_EKKO-MEINS_LI, IT_EKKO-VGBEL)
FROM LIPS
WHERE LIPS~VGBEL = IT_EKKO-EBELN AND LIPS~VGPOS = IT_EKKO-EBELP.
IF SY-SUBRC = 0.
LOOP AT IT_TRFRATE WHERE matnr_tr = it_ekko-matnr_li.
IT_EKKO-TRFRATE = IT_TRFRATE-TRFRATE.
modify it_ekko.
endloop.
endif.
modify it_ekko.
endloop.
loop at it_ekko where vbeln <> space.
select single umren from marm into vumren where marm~matnr = IT_EKKO-MATNR
and ( marm~meinh = 'L' or marm~meinh = 'KG' ).
select single umrez from marm into vumrez where marm~matnr = IT_EKKO-MATNR
and ( marm~meinh = 'L' or marm~meinh = 'KG' ).
compute vlfimg = ( IT_EKKO-LFIMG * vumren ) / vumrez.
IT_EKKO-NETPR = VLFIMG * IT_EKKO-TRFRATE.
CLEAR IT_EKKO-PEND_LI.
it_ekko-pend_li = IT_EKKO-MENGE_EB - IT_EKKO-LFIMG.
SELECT single WBSTK INTO (IT_EKKO-WBSTK)
FROM VBUK
WHERE VBUK~VBELN = IT_EKKO-VBELN.
modify it_ekko.
endloop.
********************************************************************************************
Thanks.
Khan
08-31-2007 12:00 PM
hey y u r using [ into internal tablename with field.......] rather than its better to use into table <itab> right?????
and please avoid select with append and endselect..........
and please mention all the key fields ( order should be as it is in database table ) in where condition.......
after fetching the data from eban use for all entries with ebeln in where condition to fetch the data from ekpo....
may i know y u have started fetching from eban......rather than that first fetch ebeln from ekko ....based on ebeln in ekko ...fetch the data from ekpo......after that fetch the data from eban.......
this will result better performance
reward points if helpful
08-31-2007 12:37 PM
hi,
According the requirement from Functional guy, as he enters PR no. it wil fetch PR detail from EBAN. for every PR there is STO no. then for STO no. it fetch data from EKPO. and at last according to STO it fetched Delivery Detail from LIPS. there are cases where STO doesnt created for any respective PR No. or Delivery doesnt there for any respective STO.
So I made the code according to that.
Thanks.
Khan