PERFORMANCE OF INNER JIONS ..

[Former Member]

HI,

SELECT LIFNR MBLNR MATNR WERKS SUM( MENGE ) SUM( DMBTR )

CHARG BWART MJAHR INTO

(ITAB-LIFNR, ITAB-MBLNR, ITAB-MATNR, ITAB-WERKS,

ITAB-MENGE,ITAB-DMBTR, ITAB-CHARG, ITAB-BWART,

ITAB-MJAHR)

FROM MSEG

WHERE ( WERKS IN WERKS AND

LIFNR IN LIFNR AND

CHARG IN CHARG AND

BWART BETWEEN '101' AND '102' AND

EBELN BETWEEN '4701000000' AND '4701999999' ) OR

( WERKS IN WERKS AND

LIFNR IN LIFNR AND

CHARG IN CHARG AND

BWART BETWEEN '101' AND '102' AND

EBELN BETWEEN '4704000000' AND '4704999999' )

GROUP BY LIFNR MBLNR MATNR WERKS CHARG BWART MJAHR.

SELECT BUDAT INTO (ITAB-BUDAT)

FROM MKPF

WHERE BUDAT IN BUDAT

AND MBLNR EQ ITAB-MBLNR

AND MJAHR EQ ITAB-MJAHR.

SELECT SINGLE BKLAS INTO (TEMP_BKLAS)

FROM MBEW WHERE BKLAS IN BKLAS AND

MATNR EQ ITAB-MATNR.

I HAVE TO COMBINE ALL THE ABOVE PROGRAMS IN 1 STMTS.

INSTEAD OF GROUP BY WAT TO USE TO REDUCE PERFORMANCE ISSUE

CAN ANY ONE HELIP ME OUT?

[christine_evans]

Well, you need to join MSEG, MKPF and MBEW on indexed fields, list all the fields you want to select in the select list, and include all the fields that you want to use to restrict your query in the where clause. Then you need to test your new select statement using ST05 performance trace to make sure it is using the proper indexes and is indeed faster than the original three selects.

If you've not used joins before, search this forum for examples - there are lots of examples.

Oh, and you're using SUM on some fields and not on others and so you're not going to be able to avoid using GROUP BY - unless you use aggregate functions eg MAX on all the other fields in your select field list and I'm not sure how this would work out.

Edited by: Christine Evans on Sep 1, 2008 11:58 AM

[kesavadas_thekkillath]

DONT YOU HAVE DATE RANGE FOR SELECTION ???

[kesavadas_thekkillath]

CTUALLY GO THROUGH MKPF( I GUESS u MUST BE HAVING A DATE RANGE FOR SELECTION...IM WRITING ACCORDING TO THAT)

SELECT MBLNR MJAHR INTO TABLE IT_MKPF FROm MKPF WHERE BUDAT in SoBUDAT <-----THE DATE RANGE

sort IT_MKPF by mblnr mjahr ascending.



SELECT MJAHR MBLNR MATNR WERKS MENGE BWART LIFNR DMBTR CHARG  
INTO TABLE ITAB FOR ALL ENTRIES IN IT_MKPF WHERE MBLNR = it_MKPF-MBLNR
AND MJAHR = IT_MKPF-MJAHR 
and matnr ge v_matnr     <---OPT TECHNIQUE
AND WERKS IN WERKS
AND BWART in ('101','102')
AND EBELN in RA_EBELN
and LIFNR in LIFNR
and charg in charg.

if ITAB[] is not initial.

sort itab by matnr ascending.

select matnr bklas into table it_bklas from mbew for all entries in ITAB
where matnr = ITAB-MATNR and bklas in bklas.
endif.



after selection is done loop through the needed  internal tables and join the records....

[kesavadas_thekkillath]

also add this

RANGES:RA_EBELN FOR EKPO-EBELN.

RANGES:RA_MJAHR FOR MKPF-MJAHR.

DATA:V_MATNR TYPE MARC-MATNR VALUE IS INITIAL.

RA_EBELN-LOW = '4701000000'.

RA_EBELN-HIGH = '4701999999'.

APPEND RA_EBELN.

RA_EBELN-LOW = '4704000000'.

RA_EBELN-HIGH = '4704999999'.

APPEND RA_EBELN.