Performance issue in ECC 6.0, pls help!

[Former Member]

Dear all,

I have a join statement which works fine with the same data in 4.6 C but in the upgraded system it is too slow.

SELECT PA0105_{USRID PA0001}BUKRS PA0001~GSBER

INTO TABLE I_pa0001

FROM PA0105 INNER JOIN PA0001 ON PA0001_{PERNR = PA0105}PERNR

FOR ALL ENTRIES IN i_apqi

WHERE PA0001~ENDDA EQ '99991231'

AND PA0001~BUKRS IN S_bukrs

AND PA0001~GSBER IN S_GSBER

AND PA0105~SUBTY EQ '0001'

and pa0105~usrid = i_apqi-creator

AND PA0105~ENDDA EQ '99991231'.

Table i_apqi has a list of user ids. This is taking around 6 minutes which is too much. Any ideas to improve this?

Will be happy to reward points for the answers

Regards

Veena

[Former Member]

Hi Veena,

Check this below thread... the same kind of issue discussed here with the both of these tables....

u may get a better idea to tweak your select statement accroding to your requirement.

[Former Member]

Please optimize your internal table and delete the duplicate datas.

[Former Member]

Hi,

First check whther the driver table i_apqi is empty or not by using if i_apqi[] is not initial.

Delete all the duplicate creator & sort it before making this select.

Try to use more where conditon, spl some key field

[Former Member]

This query is having join + For all entries.

Try having all keys(primary) used in join condition. Before using FOR ALL ENTRIES IN i_apqi, try having sort table based on i_apqi-creator and delete adjacent duplicates comparing i_apqi-creator. I hope you are putting a check If not i_apqi[] is initial.

Also try using as many keys as possible in where conditions, this will help using all indexes available in data fetching process.

Hope this helps..

G@urav

[Former Member]

Hi,

I think you could use this code.

IF i_apqi[] IS NOT INITIAL.
SELECT PA0105~USRID PA0001~BUKRS PA0001~GSBER
INTO TABLE I_pa0001
FROM PA0105 INNER JOIN PA0001 ON PA0001~PERNR = PA0105~PERNR
FOR ALL ENTRIES IN i_apqi
WHERE pa0105~usrid = i_apqi-creator
AND PA0001~ENDDA EQ '99991231'
AND PA0001~BUKRS IN S_bukrs
AND PA0001~GSBER IN S_GSBER
AND PA0105~SUBTY EQ '0001'
AND PA0105~ENDDA EQ '99991231'
%_HINTS ORACLE 'FIRST_ROWS'.
ENDIF.

But your table i_apqi must have than first row creator

Brgds

Julien

[Former Member]

Thank you all! I replaced this join statement with Select for all entries statments and the performance has improved. Thanks for all your help!