Select Statement Performance

[former_member1063151]

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]

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

[vinodkumar_thangavel]

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.

[jay_dalwadi]

This message was moderated.

[former_member1063151]

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

[SimoneMilesi]

The answer is pretty simple:

the 1st run you fetch data and put in cache memory, so if you run the report just after it ends its 1st run, sap got all data in cache and do not need to retrieve it from db.

After some time (your 10 mins or the day after for example), cache is being reset and SAP got to access the DB again so you do "loose" the performance improvement.

[former_member1063151]

Hi Simone,

But shouldn't it be retrieving from the suitable secondary index copy? Secondary index are used for the select statement according to performance trace. 

Thanks!

[SimoneMilesi]

The index could be not the best one.

For example (pretty silly i know) you got a selection of all the Customer (KNA1) with the same country (LAND1): you can have TONS of records with the same country and this slow down performances.

When the DB uses an index this doesn't mean that's the best.

It SHOULD be but if you do a selection like the previous (on KNA1-LAND1) and you do not have an index on it, the DB try to find out the most suitable one.

i hope i'm clear enough, i find hard to explain what i got in mind at this point of the day (9 hrs of dev under the belt for me now )

[former_member1063151]

Hi Simone,

At this point I have come to the conclusion that it is a combination of bad indexes, increased in data volume and a oder by in the statement when the field to be ordered is not in the index.

A closer look at the performance trace shows that out of the 6 fields in the secondary index, only 2 key columns are satisfied.  On top of that, we have 4 rather similar indexes that differs only by the last 2 fields. It basically confuses the DB optimizer to go through each index and finally uses the last index because it is the last. Adding to the load is the order by syntax.

On the same table we have another index just with the transaction ID and search on transaction ID is super fast.

Does that sounds like the explanation?

Thanks!

[Former Member]

It depends on a lot of things. Even if you have a good index and are selecting using all of the fields in the index, the database may decide to use a full table scan if many records will be returned. And if you don't use the index effectively (by not selecting based on the first field(s) of the index, that will cause problems. I wrote a blog on this a while ago. (It didn't format properly when SCN changed to the new style.) But you might find it useful:

http://scn.sap.com/community/abap/testing-and-troubleshooting/blog/2006/09/13/using-an-index-when-yo...

Rob

[SimoneMilesi]

Yes, this could be the explanation: similar indexes, none of them fully satisfied are a mess.

I guess you got tons of custom indexes, right?

In this case my suggestion is: try to change the select statement passing the closer conditions (fields) of an existing fields (maybe you cannot satisfy all the 6 fields, but surely something like 4 is better)

[former_member1063151]

Hi Somine,

We have 5 secondary indexes , now 6. 4 of them were created to speed up performance few years ago but in my opinion they are not efficient when the dynamic where clause contains more fields than the index and order by which is non-existent in the sec. index. Especially the data has grown so much in size after these years. It is clear now that the DB took its time to search for the matches because of the partial index.

Now with the new sec. index (containing exactly the fields as the where clause in the exact same order as well as the order by field) and the slimmed down where clause, it cuts down to less than 1 sec! And yes, its hitting 4 key columns!

Now I get it why the saying 'the system is slow' since I joined a year ago. Ticket on performance was finally raised before Christmas last year. With indexes like these...there is a limit to how fast it can go.

Thank you all for your input!

Cheers!

[SimoneMilesi]

You are welcome!

Anyway i learned on my shoulders that adding custom secondary indexes is not a good solution on long term

Did you consulted the BASIS team before creating it?

It's just matter of experience.

[former_member1063151]

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.