Can this syntax be improved?

[former_member852447]

Hello ABAP gurus,

The following piece of syntax is causing extremely long run times.

SELECT banfn bnfpo gsber ps_psp_pnr

FROM ebkn

INTO CORRESPONDING FIELDS OF TABLE gt_ebkn

FOR ALL ENTRIES IN gt_prps

WHERE ps_psp_pnr = gt_prps-pspnr.

Internal table gt_prps only has 1 line so I cannot understand why the code is taking up to 30 minutes to execute. Can anybody suggest a refinement to this line of code that will give faster run times?.

Thank you

[Former Member]

Does the table has a ps_psp_pnr index in your system?

If yes, run st05 to see if it's using the index.

[Former Member]

Take the value for gt_prps-pspnr for the one record you mentioned and count the number of records in SE16 for EKBN. How many records? If it is hundreds of thousands of records, then you should expect the program to take some time because all the data has to come through the network.

More probable cause is that you are thinking there is one record in the internal table, but the internal table is empty. Make doubly sure that the itab is just one row by verifying in debug.

Finally, ask DBAs to run stats on the table.

There is an SAP delivered index on that field - so there is really no reason for slow performance. You SQL looks OK.

[Former Member]

Moderator message - Please see before posting - you should give some information on index usage by this.

[former_member852447]

Thanks for the replies, the internal table definetely only has one entry but table EBKN is huge with field PS_PSP_PNR = 0 in most cases. PS_PSP_PNR is not a secondary index of table EBKN....are you suggesting that the program searches through every record of table EBKN.

Thanks

[Former Member]

Iis index EBKN~P not active in your system? What release are you on?

Rob

[former_member852447]

Hello Rob, Thanks for reply....I am on version ECC 6.0.......are you suggesting code like this or similar %_HINTS ORACLE 'INDEX ("EKKN" "EKKN~P")'.

[Former Member]

I am not suggesting hints.

I'm suggesting you look at ST05 to get more information. The "explain" function should show you if it is using an index.

You said that PS_PSP_PNR is not part of any index, but it is used in the index I mentioned.

But if most of the values of the field are the one in your where, then it will likely not use the index.

Rob

[HermannGahm]

Hi,

please activate ST05 while you are executing the program.

Please post the SQL text as it is sent to the database and the Execution plan.

(You get both with the EXPLAIN function in ST05).

Kind regards,

Hermann

[Former Member]

you can do the very simple test, use the SE11 and give in the value of gt_prps-pspnr, how long does this need.

Do the same and activate the ST05, copy the explain.

[former_member852447]

thank you all that replied. I will work as instructed