12-01-2006 12:56 PM
hi friends,
below code is taking more time to access and moves to short dump
is there any way we can reduce exection time of this code
SELECT msegmblnr msegmjahr msegzeile msegebeln msegebelp msegwerks
msegwempf msegmenge msegmeins msegerfmg msegerfme msegdmbtr mkpf~xblnr
msegmatnr mkpfbudat msegsmbln msegsmblp msegbwart mseglifnr mseg~umwrk
INTO CORRESPONDING FIELDS OF TABLE t_gm_f
FROM mseg INNER JOIN mkpf
ON msegmblnr EQ mkpfmblnr
AND msegmjahr EQ mkpfmjahr
FOR ALL entries IN t_gm_r
WHERE mseg~ebeln EQ t_gm_r-ebeln
AND mseg~ebelp EQ t_gm_r-ebelp
AND mseg~matnr IN s_matnr
AND mkpf~cpudt IN s_cpudt
AND mseg~bwart EQ '671'
AND mseg~bwart IN ('671','672','101','102','301','302')
AND mseg~charg = 'S'
AND mseg~kzzug EQ 'X'
AND mseg~xauto NE 'X'.
AND mseg~smbln = ' '. " Doc not reversed
ORDER BY mseg~mblnr.
regards
raj
12-01-2006 1:00 PM
as such MSEG & MKPF are hughe tables...Try to split the select statement...First write select statement on MSEG....then write select statement on MKPF using for all entries...
12-01-2006 1:03 PM
Hi
Check if t_gm_r[] is not initial before your select statement otherwise it would fetch invalid data even it is initial.
Regards
Su
12-01-2006 1:05 PM
SELECT mkpf~xblnr
mkpf~budat
mseg~mblnr
mseg~mjahr
mseg~zeile
mseg~ebeln
mseg~ebelp
mseg~werks
mseg~wempf
mseg~menge
mseg~meins
mseg~erfmg
mseg~erfme
mseg~dmbtr
mseg~matnr
mseg~smbln
mseg~smblp
mseg~bwart
mseg~lifnr
mseg~umwrk
INTO TABLE t_gm_f
FROM mseg
INNER JOIN mkpf ON msegmblnr EQ mkpfmblnr
AND msegmjahr EQ mkpfmjahr
FOR ALL entries IN t_gm_r
WHERE mkpf~cpudt IN s_cpudt
AND mseg~ebeln EQ t_gm_r-ebeln
AND mseg~ebelp EQ t_gm_r-ebelp
AND mseg~matnr IN s_matnr
AND mseg~bwart EQ '671'
AND mseg~bwart IN ('671','672','101','102','301','302') " try to create a range and then pass thse values in a range
AND mseg~charg = 'S'
AND mseg~kzzug EQ 'X'
AND mseg~xauto NE 'X'.
AND mseg~smbln = ' '. " Doc not reversed
ORDER BY mseg~mblnr.
create the table with the same sequence as in data base.
if that doesnt work u have to write for all entries for MKPF and then agn for all entries for mseg two seperate statements.
hope that helps.
santhosh
12-01-2006 1:06 PM
hi,
1. avoid INTO CORRESSPONDING
2.fetch only the records u required in the ITAB t_gm_r
rgds
Anver
12-01-2006 1:12 PM
IF you are trying to fetch material documents pertaining to a purchase order I strongly suggest you to use EKBE instead of MSEG.
EKBE has EBELN and EBELP as first two fields in the key fields list hence the performance will be much much better.
So all you need to do is.
a. Fetch MBLNR, MJAHR, from EKBE by passing EBELN and EBELP.
b. pass this to MSEG and get all the other details.
12-01-2006 1:20 PM
1. INTO CORRESPONDING FIELDS OF TABLE t_gm_f,-- define an internal table with the same structure as the fields coming from mseg,mkpf.
later loop at t_gm_f. read from interaml table in which you read and
modify t_gm_f,.
2. define a rage for mseg~bwart
append all these values ('671','672','101','102','301','302')
and put mseg~bwart IN r_bwart.
12-01-2006 1:36 PM
I think there is not much you can change here but just check on the below suggestions.
1. If your t_gm_r internal table has data for matnr etc try to reduce it before you pass it to the select.
2. Do the select only on mseg data first and later do a select from mkpf using for all enteries...basically taking out inner join.
3. Last is to fetch SELECT up to 1000 rows and do your processing.
Regards
Anurag
12-01-2006 2:23 PM
Two things
The only index you are using is mseg-matnr, so make sure s_matnr is not initial before doing the select.
There is no index on mkpf-cpudt, but there is one on mkpf-budat, so it might help if you selected on that instead. But also make sure that the new select option is not empty.
Rob
12-08-2006 11:48 AM
Hi Raj,
You can also try making use of OPEN CURSOR.
It goes like this.
DATA c_tab TYPE cursor.
OPEN CURSOR c_tab FOR
*your select statement*
DO.
FETCH NEXT CURSOR c_tab
APPENDING CORRESPONDING FIELDS OF TABLE i_tab
PACKAGE SIZE 100.
IF sy-subrc <> 0.
CLOSE CURSOR c_tab. EXIT.
ENDIF.
ENDDO.
Any doubts let me know.
Regards.
Venkat.
12-08-2006 4:40 PM
In my opinion you should do following:
1. Avoid using INTO CORRESPONDING by creating an internal table with fields you intend to select
2. Ensure s_matnr, s_cpudt are not initial OR you can remove these from where clause and delete the entries which are not equal to s_cpudt and s_matnr
3. Use indexes if available.
12-10-2006 4:54 AM
Hello raj,
Well MKPF and MSEG are very huge tables. Moreover using joins effectively reduces the performance heavily, cozz it compares all entries of both tables and then process.
Instead you can fetch data from one table according to your conditions in where clause and then fetch data from another table using "for all entries clause" specifying the equality condition in where clause.
For further performace gain.. You can use indexes from both tables and add them into your where condition.
Try to reduce loops. Wherever you are using loops specify loop at itab where.....
You can use read table itab with key... too...
Try to minimize database access. i.e try to minimize multiple select statements,.
Try to put maximum conditions in where clause, so that database access is fast.
12-11-2006 9:04 AM
hi,
check whether the internal table you are referring to is not initial.
Regards
Abdullah
12-27-2006 9:37 AM
Hi,
In connection with SELECT ... FOR ALL ENTRIES the following problems occur:
1)You cannot have an empty internal driver table. If the driver table is empty, selection is not limited. In particular, WHERE clause conditions are not evaluated if they do not refer to the internal driver table.
2)Duplicate table entries should be deleted from the internal table before executing the SELECT... FOR ALL ENTRIES. If they are not deleted, identical data is read unnecessarily from the database.
3)The parameter rsdb/max_blocking_factor must be implemented according to SAP's database-specific recommendations.
Thanks,
Balaji Reddy G
Reward if answers are helpful