Skip to Content
avatar image
Former Member

PERFORMANCE OF INNER JIONS ..

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?

Add comment
10|10000 characters needed characters exceeded

  • Follow
  • Get RSS Feed

3 Answers

  • Best Answer
    Sep 01, 2008 at 10:52 AM

    DONT YOU HAVE DATE RANGE FOR SELECTION ???

    Add comment
    10|10000 characters needed characters exceeded

  • Sep 01, 2008 at 11:07 AM
    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....
    
    
    
    Add comment
    10|10000 characters needed characters exceeded

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

  • avatar image
    Former Member
    Sep 01, 2008 at 09:56 AM

    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

    Add comment
    10|10000 characters needed characters exceeded