Performance Issue

[Former Member]

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

[Former Member]

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

[Former Member]

<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

[abdul_hakim]

Hi

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

[Former Member]

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

[Former Member]

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