Performance tuning.

[#1]

Hi

This report came for optimization

There are four inner joins tables used in the report..

Few codes i am attaching for ur ref.--

SELECT AUFK_{AUART AUFK}AUFNR AUFK_{OBJNR AFKO}AUFNR AFKO~FEVOR

AFKO_{RSNUM AFKO}STLAL AFKO_{STLNR AFKO}STLTY RESB~BDMNG

RESB_{ENMNG RESB}ENWRT RESB_{MATNR RESB}MEINS RESB~RSNUM

RESB_{WERKS RESB}LGORT RESB~POSNR

RESB_{STLKN RESB}STLNR RESB_{STLTY RESB}STPOZ RESB~WAERS

AFPO_{AMEIN AFPO}AUFNR AFPO_{LGORT AFPO}MATNR AFPO~PSMNG

AFPO~WEMNG

INTO (AUFK-AUART , AUFK-AUFNR , AUFK-OBJNR , AFKO-AUFNR , AFKO-FEVOR

, AFKO-RSNUM , AFKO-STLAL , AFKO-STLNR , AFKO-STLTY , RESB-BDMNG

, RESB-ENMNG , RESB-ENWRT , RESB-MATNR , RESB-MEINS , RESB-RSNUM

, RESB-WERKS , RESB-LGORT, RESB-POSNR

, RESB-STLKN , RESB-STLNR , RESB-STLTY , RESB-STPOZ , RESB-WAERS

, AFPO-AMEIN , AFPO-AUFNR , AFPO-LGORT , AFPO-MATNR , AFPO-PSMNG

, AFPO-WEMNG )

FROM ( AUFK

INNER JOIN AFKO

ON AFKO_{AUFNR = AUFK}AUFNR

INNER JOIN RESB

ON RESB_{RSNUM = AFKO}RSNUM

INNER JOIN AFPO

ON AFPO_{AUFNR = AUFK}AUFNR )

WHERE AUFK~AUART IN S_AUART

AND AUFK~AUFNR IN S_AUFNR

AND AFKO~FEVOR IN S_FEVOR

AND AFPO~LGORT IN S_LGORT

AND RESB~MATNR IN S_MATNRC

AND RESB~DUMPS IN S_DUMPS

AND RESB~XLOEK EQ SPACE

AND AFPO~MATNR IN S_MATNRP.

SELECT SINGLE * FROM MARA CLIENT SPECIFIED

WHERE MANDT = SY-MANDT

AND MATNR = RESB-MATNR

AND MATKL IN S_MATKL.

In the run time analysis AUFK having 39% and mara having 20%.

Please advice.

Thanks and regards

Arunachalam S

[Former Member]

You are doing the SELECT from MARA every time you get a hit on the outer SELECT. This means that you are probably selecting the same material many many times. Put the results of the first select into an internal table and then delete adjacent duplicates from it (comparing MARA). Then SELECT from MARA using FOR ALL ENTRIES of this table.

Rob

[Former Member]

1. Define a interanl table with same structure as you output fileds.

data: begin of ITAb occurs 0,

AUART like AUFK-AUART ,

AUFNR like AUFK-AUFNR ,

OBJNR like AUFK-OBJNR ,

AUFNR like AFKO-AUFNR ,

FEVOR like AFKO-FEVOR,

RSNUM like AFKO-RSNUM ,

STLAL like AFKO-STLAL ,

STLNR like AFKO-STLNR ,

STLTY like AFKO-STLTY ,

BDMNG like RESB-BDMNG,

ENMNG like RESB-ENMNG ,

ENWRT like RESB-ENWRT ,

MATNR like RESB-MATNR ,

MEINS like RESB-MEINS ,

RSNUM like RESB-RSNUM,

WERKS like RESB-WERKS ,

LGORT like RESB-LGORT,

POSNR like RESB-POSNR,

STLKN like RESB-STLKN ,

STLNR like RESB-STLNR ,

STLTY like RESB-STLTY ,

STPOZ like RESB-STPOZ ,

WAERS like RESB-WAERS,

AMEIN like AFPO-AMEIN ,

AUFNR like AFPO-AUFNR ,

LGORT like AFPO-LGORT ,

MATNR like AFPO-MATNR ,

PSMNG like AFPO-PSMNG,

WEMNG like AFPO-WEMNG ,

end of ITAB.

2. Use into table.

select AUFK_{AUART AUFK}AUFNR AUFK_{OBJNR AFKO}AUFNR AFKO~FEVOR

AFKO_{RSNUM AFKO}STLAL AFKO_{STLNR AFKO}STLTY RESB~BDMNG

RESB_{ENMNG RESB}ENWRT RESB_{MATNR RESB}MEINS RESB~RSNUM

RESB_{WERKS RESB}LGORT RESB~POSNR

RESB_{STLKN RESB}STLNR RESB_{STLTY RESB}STPOZ RESB~WAERS

AFPO_{AMEIN AFPO}AUFNR AFPO_{LGORT AFPO}MATNR AFPO~PSMNG

AFPO~WEMNG

into table ITAB

from AUSK

inner join AFKO

on AFKO_{AUFNR = AUFK}AUFNR

INNER JOIN RESB

ON RESB_{RSNUM = AFKO}RSNUM

WHERE AUFK~AUART IN S_AUART

AND AUFK~AUFNR IN S_AUFNR

AND AFKO~FEVOR IN S_FEVOR

AND AFPO~LGORT IN S_LGORT

AND RESB~MATNR IN S_MATNRC

AND RESB~DUMPS IN S_DUMPS

AND RESB~XLOEK EQ SPACE

AND AFPO~MATNR IN S_MATNRP.

[Former Member]

Hello,

I would suggest u to look for a view with all these tables or some of them atleast and use them in ur select. The main advantage is the database itself takes care of performance related issue and optimise the data extraction.

[Former Member]

Hi

Use forall entries instead of inner joins.