topic Re: Can this syntax be improved? in Application Development and Automation Discussions

Can this syntax be improved?

former_member852447 — Fri, 26 Feb 2010 16:15:12 GMT

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

Re: Can this syntax be improved?

Former Member — Fri, 26 Feb 2010 16:19:36 GMT

Does the table has a ps_psp_pnr index in your system?

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

Re: Can this syntax be improved?

Former Member — Fri, 26 Feb 2010 16:26:41 GMT

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.

Re: Can this syntax be improved?

Former Member — Fri, 26 Feb 2010 16:41:06 GMT

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

Re: Can this syntax be improved?

former_member852447 — Fri, 26 Feb 2010 16:45:08 GMT

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

Re: Can this syntax be improved?

Former Member — Fri, 26 Feb 2010 16:48:54 GMT

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

Rob

Re: Can this syntax be improved?

former_member852447 — Fri, 26 Feb 2010 16:54:02 GMT

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")'.

Re: Can this syntax be improved?

Former Member — Fri, 26 Feb 2010 17:03:19 GMT

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

Re: Can this syntax be improved?

Former Member — Mon, 01 Mar 2010 08:05:20 GMT

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.

Re: Can this syntax be improved?

HermannGahm — Mon, 01 Mar 2010 08:39:04 GMT

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

Re: Can this syntax be improved?

former_member852447 — Mon, 01 Mar 2010 15:23:33 GMT

thank you all that replied. I will work as instructed