topic Re: Performance Issue in Application Development and Automation Discussions

Performance Issue

Former Member — Thu, 28 Dec 2006 05:24:41 GMT

Hi Abap Gurus,

From SE30, I found the the database performance is very poor and especially it is describing the inner join part.

For your ref. Pl.find the below SQL code.

Fetch Orders matching the selection criteria

SELECT AUFK_{AUFNR AUFK}OBJNR AFKO_{GSTRP AFKO}GAMNG

AFKO_{STLBEZ AFKO}AUFPL AFKO~RSNUM

INTO TABLE ITAB_ORDERS

FROM ( AUFK INNER JOIN AFKO

ON AUFK_{AUFNR EQ AFKO}AUFNR )

WHERE AUFK~AUFNR IN S_AUFNR

AND AUFK~AUART IN S_AUART

AND AUFK~WERKS IN S_WERKS

AND AFKO~GSTRP IN S_PERIOD

AND AFKO~STLBEZ IN S_MATNR

AND AFKO~FEVOR IN S_FEVOR

AND AUFK~GSBER IN R_GSBER.

Kindly check this code and ur advice please.

Regards,

Arunachalam S

Re: Performance Issue

Former Member — Thu, 28 Dec 2006 05:29:40 GMT

If you don't pass AUFK-AUFNR select option, this query would take a long time.

MAke that field mandatory.

Also, order the where clause fields in the same order in which they appear in the respective tables.

Regards,

Ravi

Re: Performance Issue

Former Member — Thu, 28 Dec 2006 05:29:45 GMT

<b>if s_aufnr[] is not initial.</b>

SELECT AUFK_{AUFNR AUFK}OBJNR AFKO_{GSTRP AFKO}GAMNG

AFKO_{STLBEZ AFKO}AUFPL AFKO~RSNUM

INTO TABLE ITAB_ORDERS

FROM ( AUFK INNER JOIN AFKO

ON AUFK_{AUFNR EQ AFKO}AUFNR )

WHERE AUFK~AUFNR IN S_AUFNR

AND AUFK~AUART IN S_AUART

AND AUFK~WERKS IN S_WERKS

AND AUFK~GSBER IN R_GSBER

AND AFKO~GSTRP IN S_PERIOD

AND AFKO~STLBEZ IN S_MATNR

AND AFKO~FEVOR IN S_FEVOR.

<b>endif.</b>

regards

Prabhu

Re: Performance Issue

abdul_hakim — Thu, 28 Dec 2006 05:48:40 GMT

Avoid using Joins.

Replace ur query with FOR ALL ENTRIES variant.

something like

SELECT AUFNR

OBJNR

FROM AUFK INTO TABLE ITAB_AUFK

WHERE AUFNR IN S_AUFNR AND

WERKS IN S_WERKS AND

GSBER IN R_GSBER.

IF NOT ITAB_AUFK IS INITIAL.

SELECT AUFNR

GSTRP

GAMNG

STLBEZ

AUFPL

RSNUM

FROM AFKO INTO TABLE ITAB_AFKO

FOR ALL ENTRIES IN ITAB_AUFK

WHERE AUFNR EQ ITAB_AFKO-AUFNR AND

GSTRP IN S_PERIOD AND

STLBEZ IN S_MATNR AND

FEVOR IN S_FEVOR.

ENDIF.

Cheers,

Abdul Hakim

Re: Performance Issue

Former Member — Thu, 28 Dec 2006 05:55:11 GMT

Hi,

you are using range R_GSBER. in where clause. Please check if values in this range are exceeding 2000. I hope you have deleted duplicate entries from range.

If value in ranges exceeds 2000 query will strart dumping.

Best option is slit the queries in 2 and use for all entries.

regards

Saurabh

Re: Performance Issue

Former Member — Thu, 28 Dec 2006 09:25:33 GMT

Hi,

From what I know, there are 3 ways for selecting:

1) joins

2) for all entries

3) subquery

It depends on situation and in diff cases.. diff methods result in diff performance..

so which method... I think based on experience and trial and error?

Regards,

Charles