Skip to Content
avatar image
Former Member

Select Statement Performance

Hi All,

Currently we are having an unacceptable performance while reading a transactional table. This table has secondary indexes and from performance trace, the secondary index is used. The original select statement looks like this:

select x from table

        into itab

          where (lv_where)

          order by (lv_orderby).

1. After reading through some investigation doc done in previous years, one of the suggestions was to use up to x row in the select statement.

select x from table

       into itab

          up to y row

          where (lv_where)

          order by (lv_orderby).

2. Now, the order by field is not in the secondary index. Some performance tuning blog suggested that order by should be avoided if the field is not part of the index as this might cause more load to the DB. Instead, sort should be done after data are retrieved and returned the desired rows of record.

select x from table

       into itab

          where (lv_where).

if sy-subrc = 0.

sort itab by lv_orderby descending.

endif.

My observations:

Option 1: There is no obvious improvement on first run. But 2nd, 3rd continuous try shows much better performance. The same happens on the next day, that means 1st run is slow but 2nd and 3rd are better. My hunch is that the DB needs to rebuilt the index due to the oder by field, am I right?

Option 2: The performance is not that consistent but is generally ok.

Which select is actually performance friendlier? Kindly share your knowledge!

Thanks in advance!

Cheers,

cady

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • avatar image
    Former Member
    Jan 08, 2015 at 07:02 AM

    Hi Rob,

    The select are executed after one another - the transaction is started, query executed and transaction is ended. Repeat. Performance improves significantly on the second try. But once there is a lag in between, like after 10 mins or the next day, the performance is slower again. I could not put my finger on this as the index should have built up after the first exe but it did not gives a consistent performance on subsequent execution.


    p/s: Before I wrote this reply, I ran the transaction twice one after another. The second execution took half the time on the same select. But when I tried again after finishing this reply ( maybe 15 mins ) , the time spend on the same select doubles again.

    Hi Vinod,

    All 3 variations of the select statement - the original, 1st option and 2nd option has the same where clause and the same order by field which is not in the secondary index used. They use the same secondary index as shown in ST05.


    Thanks!

    Cheers,

    cady

    Message was edited by: Hian Lim Koay

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Simone Milesi


      In fact we contacted SAP whom did the the baseline setup. The first reaction was -- create another index! According to the expert, its no big deal to have 6 of them.

      However, I do share the same concern as you. But I feel with the correct sec. index with proper archiving in place, this should not create any huge bottleneck. Unless the system has 4 similiar indexes like mine and only 2 key columns are hit with no archive  pairing with nonsense where clause, I foresee more problems will show up sooner or later.

      By the way, that 4 similiar indexes were a recommendation from the experts. 😉

  • avatar image
    Former Member
    Jan 07, 2015 at 07:10 PM

    Did you run the SELECTs in the same order on subsequent days? Usually the first time you execute a SELECT will be the longest. So try running them in a different order. ST05 should give you a better idea of which is best. Option 2 seems dangerous because you might miss data. I would remove the ORDER BY and sort the table after the select.

    Rob

    Add comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    Jan 08, 2015 at 04:27 AM

    Check whether the secondary index are fetched in order.if so then the first query works, in case if the secondary index is not applied for lv_where then the second works.

    Regards,

    Vinod.

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 08, 2015 at 04:50 AM

    This message was moderated.

    Add comment
    10|10000 characters needed characters exceeded