PRPS Performance Issue

[Former Member]

Hey All,

I am having serious performance issues with the below select and needed some inputs to try optimize it.

IF  i_imakz[] IS NOT INITIAL.   
select c~posnr
           a~post1
           b~psphi
           a~pspnr
           a~pspid
           a~werks
           a~vgsbr
           a~objnr
           from ( ( proj as a inner join prps as b on a~pspid = b~posid )
           inner join imakz as c on p~objnr = c~objnr )
           into corresponding fields of table i_fproj
           for all entries in i_imakz
           where p~objnr eq i_imakz-objnr
ENDIF.

I did a SQL analysis for the Querry and found out that it somewhat does a full table scan on PRPS as i dont have any where condition which forms the index for the table. Any input is highly appreciated

Moderator message - Moved to the correct forum

Edited by: Rob Burbank on Oct 15, 2009 12:02 PM

[Former Member]

Hi Aparna,

Perhaps you should create an index on field OBJNR so... Or perhaps you can change (but I don't know a lot these tables...) the logic like this :

IF  i_imakz[] IS NOT INITIAL.   
SELECT c~posnr a~post1 b~psphi a~pspnr a~pspid
       a~werks a~vgsbr a~objnr
  FROM proj AS a
  INNER JOIN prps AS b ON a~pspid = b~posid 
" I have replace the join on POSNR rather than OBJNR
  INNER JOIN imakz AS c ON b~posnr = c~posnr 
  INTO CORRESPONDING FIELDS OF i_fproj
  FOR ALL ENTRIES IN i_imakz
" I have change the table on which we make FAE
    WHERE c~objnr EQ i_imakz-objnr  
ENDIF.

Best regards,

Samuel

[Former Member]

THanks for you input, but there is no posnr field in PRPS table.

[Former Member]

Yes you're right : I have take PSPNR for POSNR!!!

But you can still create an index : I have check that on my project, there is a specific index on field OBJNR...

And if you still have problem, I suggest you to split the join into a full scan on PRPS and then to FAE on the 2 other tables since the problem with FAE is that you treat the entry in your input table (here i_imakz) 5 by 5 (or another value that you can see in RZ11) and that you do full scan a lot of time instead of one time at the beginning...

Hope it will help!

Samuel

[anusurbab]

Hi,

1. Try deleting duplicate entries from i_imakz based on objnr.

2. Sort i_makz by objnr

3. Create a dummy range table for PSPNR field.

4. Create a secondary index based on PSPNR, pspid and objnr

All together you can write your query as follows:

//Code

sort i_imakz by objnr.

delete adjusant duplicates from i_makz comparing objnr.

IF i_imakz[] IS NOT INITIAL.

select c~posnr

a~post1

b~psphi

a~pspnr

a~pspid

a~werks

a~vgsbr

a~objnr

from ( ( proj as a inner join prps as b on a_{pspid = b}posid )

inner join imakz as c on p_{objnr = c}objnr )

into corresponding fields of table i_fproj

for all entries in i_imakz

where PSPNR in s_pspnr and //s_pspnr - range table

a_{pspid = b}posid and

p~objnr eq i_imakz-objnr

ENDIF.

Edited by: Suresh Babu Suresetti on Oct 16, 2009 12:44 PM

[RaymondGiuseppi]

PRPS/PROJ

In your code you have

proj as a inner join prps as b on a~pspid = b~posid

Shouldn't it be (using foreign key)

proj as a inner join prps as b on a~pspnr = b~psphi

which would use Index PRPS~P.

IMAKZ

The link with CO objects is in table IMZO. (index IMZO~001 is MANDT, OBJNR)

Regards,

Raymond

[Former Member]

Thanks for all your inputs

@Samuel: PSPNR is not the same as POSNR and Index is my last option.

@Suresh: I do delete duplicates before using FAE. Not sure if dummy range will help me.

@Raymond. I did try the PSPHI join condition earlier, but for some reason the ST05 SQL analysis tool did not show any improvement in the select after changing the join condition. THe explain statement does not show that the optimizer is using Index P. Not sure why it wouldnt take the Index. As far as IMZO is concerned, do u think it will help with the select?? There is a index on OBJNR for IMAKZ also and it uses it.

I will try implement the new join condition and see if there is a difference in the runtime. Maybe use a Hint in the select which i dont prefer..