Performance issues and HANA DB Secondary Indexes

[Former Member]

Dear All

I am not a technical consultant. I hope I will be able to clearly put together my question:

In our company we are using HANA DB with SAP ERP 6.0 (SAP_APPL Release 618, SP 0002). In the near future our company is not planning to move into S/4 HANA.

We are currenlty having serious performance issues while saving PM workorders, reporting PS projects, initiating a PPM Items, MRS is performing slower than acceptable levels etc. I know there might be several reasons for that and we are trying to approach performance issues froms everal different perspectives. We are looking for performance related OSS notes on each module etc.

Our table sizes are huge, but not that big for SAP to be handled properly. For example:

COEP 46 million records

COSS 19 m.

COBK 5 m.

AFVC 3 m.

IFLOT - 3m.

EQUI - 3m.

AFKO 600k

MPOS - 1 m.

And these numbers are getting bigger every day.

We are thinking to and were recommeded to start using secondary indexes for especially CO tables mentioned above but no one in our team has ever turned on secondary indexes for HANA DB. Here are my questions:

1. First of all we are not sure whether this should be done.
2. Secondly, if we decide to go that path, what might be the potential "side effects" of turning on secondary indexes in HANA DB.

I could not find any OSS notes or information which are clearly giving guidance on secondary indexes on a HANA DB. Once again, we are not on S/4 HANA .

All helps and suggestions will be highly appreaciated.

Cheers

Savas

[lbreddemann · Accepted solution]

Not sure why you didn't find any notes, when I just looked for "secondary indexes hana" I found plenty general as well as application specific notes.

The most relevant for you is probably "2160391 - FAQ: SAP HANA Indexes" https://launchpad.support.sap.com/#/notes/2160391 .

However, you mentioned that there are performance problems while saving or changing data. I recommend to have a look at where the application actually spends its time and make changes according to that.

Deploying secondary indexes is a very specific measure that might not even affect your problem set.

[former_member183326]

As you mentioned there could be many reasons for this. I'd first ask what revision of HANA are you on?

Have your tech guys checked if there are delta merge issues?

You could also check what statements are being executed the most and on what table? I.e. UPDATES, INSERTS, etc. There are many recommendations for different tables for HANA.

In general secondary indexes are not recommended for HANA as they are not needed a lot of the time.

[Former Member]

Michael and Lars

Many thanks for the initial-quick responses.

A quick correction to what I told: performance is slow when we run some search and list objects (IW38, IW37N, PPM Portfolio Structure and dashboard etc.

@Lars - I could not find that note for some reason which seems very useful. I found another one: 1794297 which might be useful for other users in the future who will take a look at this thread. The notes which I found were not applicable in our system due to our level of support packages.

There are plenty of module specific, non-indexing relevant OSS notes on performance improvemnts. We will implement those as well.

Another "parameter" which is slowing us down seems to be using NWBC. There is an observable difference between GUI and NWBC performance. On top of that, Webdynpro applications like the ones in SAP PPM are slower than classical transactions.

If I understand you correctly, both of your recommendations are similarly noting that Secondary Indexing might not improve the performance issue. First step might be analysing the cases and understanding what is causing the delays. If these delays are related with queries, secondary indexing might become useful.

And similarly, I believe it will be useful to think moving some of the tables to memory as it seems our CPU and Memory usage is quite low at the moment.

I will come update the thread back with further details and I will share the experience ine the future.

Cheers

Savas

[lbreddemann]

But can you please correct me if I am misunderstanding everything: HANA DB does not use columnar database in business suite and with this, greatest performance improvements cannot be achieved. Is this a correct understanding of the concept of HANA DB in Busienss suite?

This is not correct at all.

In fact, the vast majority of all tables used by the Business Suite running on SAP HANA are designed as column store tables. With that, they can make use of the benefits of column store tables (e.g. high compression, column-as-an-index, parallel access, ...) automatically. While there are certainly additional possible performance improvements that can be found in S/4 HANA (e.g. due to the massive data re-modelling and ABAP code re-optimisation), many performance related benefits of SAP HANA are definitively available also for the Business Suite.

[former_member365886]

Hi,

Lars is absolutely right and I have been using Suite on HANA(SOH) landscape for past 3 years,we have very large tables ,even larger than most of the mentioned statistics and we do not face these kind of issue on day to day basis.Business is running as usual.

Howvere,I think to be using HANA DB with such lower SP0002 level is causing you these issues.You need to analyse your application and apply may be series of OSS notes.A better suggestion is to do patch upgrade.

We have recently done upgrade for EHP8, but few months back we were on EHP7 and SP13.

Regards,

Avik