topic Re: select statemant in Application Development and Automation Discussions

select statemant

Former Member — Fri, 01 Dec 2006 12:56:14 GMT

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 mseg_{mblnr mseg}mjahr mseg_{zeile mseg}ebeln mseg_{ebelp mseg}werks

mseg_{wempf mseg}menge mseg_{meins mseg}erfmg mseg_{erfme mseg}dmbtr mkpf~xblnr

mseg_{matnr mkpf}budat mseg_{smbln mseg}smblp mseg_{bwart mseg}lifnr mseg~umwrk

INTO CORRESPONDING FIELDS OF TABLE t_gm_f

FROM mseg INNER JOIN mkpf

ON mseg_{mblnr EQ mkpf}mblnr

AND mseg_{mjahr EQ mkpf}mjahr

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

Re: select statemant

Former Member — Fri, 01 Dec 2006 13:00:35 GMT

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...

Re: select statemant

Former Member — Fri, 01 Dec 2006 13:03:51 GMT

Check if t_gm_r[] is not initial before your select statement otherwise it would fetch invalid data even it is initial.

Regards

Re: select statemant

Former Member — Fri, 01 Dec 2006 13:05:47 GMT

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 mseg_{mblnr EQ mkpf}mblnr

AND mseg_{mjahr EQ mkpf}mjahr

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

Re: select statemant

anversha_s — Fri, 01 Dec 2006 13:06:07 GMT

hi,

1. avoid INTO CORRESSPONDING

2.fetch only the records u required in the ITAB t_gm_r

rgds

Anver

Re: select statemant

Former Member — Fri, 01 Dec 2006 13:12:40 GMT

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.

Re: select statemant

Former Member — Fri, 01 Dec 2006 13:20:54 GMT

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.

Re: select statemant

Former Member — Fri, 01 Dec 2006 13:36:22 GMT

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

Re: select statemant

Former Member — Fri, 01 Dec 2006 14:23:50 GMT

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

Re: select statemant

Former Member — Fri, 08 Dec 2006 11:48:49 GMT

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.

Re: select statemant

Former Member — Fri, 08 Dec 2006 16:40:39 GMT

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.

Re: select statemant

Former Member — Sun, 10 Dec 2006 04:54:43 GMT

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.

Re: select statemant

Former Member — Mon, 11 Dec 2006 09:04:08 GMT

hi,

check whether the internal table you are referring to is not initial.

Regards

Abdullah

Re: select statemant

Former Member — Wed, 27 Dec 2006 09:37:04 GMT

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