Skip to Content

performance issues due to FOR ALL ENTRIES

Hi,

I had the following select query in my code: -

SELECT vbeln posnr FROM vbup

INTO TABLE lt_del_item

FOR ALL ENTRIES IN lt_del_nos

WHERE vbeln = lt_del_nos-vbeln

AND wbsta NE gc_goods_mvmt_stat_comp

AND pdsta NE gc_pod_complete.

The above query was taking up a lot of execution time which I thought was because the 'lt_del_nos' table has about 800+ records.

In order to optimize my code, I replaced the query above with the one below: -

LOOP AT lt_del_nos INTO ls_del_header.

ls_vbeln-low = ls_del_header-vbeln.

ls_vbeln-sign = gc_inclusive_sign.

ls_vbeln-option = gc_equal_option.

APPEND ls_vbeln TO lr_vbeln.

CLEAR ls_del_header.

CLEAR ls_vbeln.

ENDLOOP.

SELECT vbeln posnr FROM vbup

INTO TABLE lt_del_item

WHERE vbeln IN lr_vbeln

AND wbsta NE gc_goods_mvmt_stat_comp

AND pdsta NE gc_pod_complete.

I found that the execution time improved significantly.

Why did the performance improve? After all the table lt_del_item and the range lr_vbeln have the same number of records. Is there any difference in the manner the the first query and the second query work?

Thanks in advance,

Divyaman

Add a comment
10|10000 characters needed characters exceeded

Related questions

7 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 09:33 AM

    Hello Divyman,

    The performance would get improved futher if you can do away with NE Condition in where Condition. Can you modify your logic in such a manner so that

    <b>wbsta NE gc_goods_mvmt_stat_comp

    AND pdsta NE gc_pod_complete</b>

    can have EQ instead of not NE. Also you have gone for a range object and the query give a dump if the number of entries in the range object is large say 4000 or more.

    Regards,

    <b>Reward Points if Useful</b>

    Saket Sharma

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 22, 2007 at 09:35 AM

    The ranges is of course faster than the FOR ALL ENTRIES, that is no suprise.

    The FOR ALL ENTRIES is split into blocks.

    But still, FOR ALL ENTRIES is the one you should use when the table becomes large. The range is more intended for munual selection screens, and will dump if the ranges table becomes too large.

    Check index suppoort, the NE conditions will not help, so there is only vbeln.

    Are there identical vbeln in lt_del_nos?

    <b>Run the SQL trace, what the is average and minimal time per record for this statement?</b> See SQL trace:

    /people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 22, 2007 at 12:19 PM

    Before you think about Equal or not equal you must check the index which contains the field vbeln, what other fields are in the index?

    If wbsta and pdsta are not in the index then a change into equal will not help.

    Actually I think, it will never help, that's why I did not mention it. Changing NE into an EQ with many conditions can help to fill an index gap, i.e. if you have further fields coming later in the index. But for trailing fields it will not help.

    Siegfried

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 08:44 AM

    try

    delete adjacent duplicates from lt_del_nos comparing vbeln

    before using for all entries.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 22, 2007 at 09:09 AM

    quite interesting...800+ entries does not sound that much to me.

    was your table LT_DEL_NOS sorted by VBELN before the FOR ALL ENTRIES statement?

    If not, that might be the reason for the difference in execution time. It seems that when using ranges as in your second code version, the values are sorted by the DB interface before the select is executed. Maybe somebody knows this for sure.

    Cheers

    Thomas

    Add a comment
    10|10000 characters needed characters exceeded

    • Yes Thomas, lt_del_nos was sorted by vbeln.

      The range is also sorted.

      What my questions really is that why should one query do better than the other when the range and the internal table being used have the same contents and both of them are sorted?

      I am myself searchin on the internet to find an answer to this question. Will post here if I find anything.

      Cheers,

      Divyaman

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 12:28 PM

    Hi,

    There will be a lot of difference if you remove NE in your Query.

    comment those conditions and get the data into Internal Table.

    now write below code

    Delete lt_del_item
      where wbsta EQ gc_goods_mvmt_stat_comp and pdsta EQ gc_pod_complete.

    Regards,

    Satish

    Message was edited by:

    Satish Panakala

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 22, 2007 at 04:33 PM

    I think there was some sort of buffering (not SAP buffering) going on. Quite often when you execute similar queries one after the other, the second one will have much less execution time.

    Try running each query a number of times and take the lowest of each. I'd expect them to be similar.

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.