Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

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

Former Member

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question