Skip to Content

If you are an experienced ABAP Developer, then you most likely know the classical performance guidelines for using Open SQL (if not, then you should look them up immediately).

One of the frequently asked questions we receive is:

“What changes in the context of SAP HANA regarding these guidelines?”

Let us first reconsider the existing guidelines. In a nutshell, they are usually summarized in the “5 golden rules”:

Icon Rule Details / Examples

/wp-content/uploads/2013/03/1_186738.png

Keep the result sets small

  • Do not retrieve rows from the database and discard them on the application server using CHECK or EXIT, e.g. in SELECT loops
  • Make the WHERE clause as specific as possible
/wp-content/uploads/2013/03/2_186721.png Minimize amount of transferred data
  • Use SELECT with a field list instead of SELECT * in order to transfer just the columns you really need
  • Use aggregate functions (COUNT, MIN, MAX, SUM, AVG) instead of transferring all the rows to the application server
/wp-content/uploads/2013/03/3_186734.png Minimize the number of data transfers
  • Use JOINs and / or sub-queries instead of nested SELECT loops
  • Use SELECT … FOR ALL ENTRIES instead of lots of SELECTs or SELECT  SINGLEs
  • Use array variants of INSERT, UPDATE, MODIFY, and DELETE
/wp-content/uploads/2013/03/4_186735.png Minimize the search overhead
  • Define and use appropriate secondary indexes
/wp-content/uploads/2013/03/5_186733.png

Keep load away from the database

  • Avoid reading data redundantly
  • Use table buffering (if possible) and do not bypass it
  • Sort Data in Your ABAP Programs

Well, the short answer regarding the question above is

  • All existing (standard and custom) ABAP code runs on SAP HANA without modifications
  • All the existing  guidelines are still valid for SAP HANA as general recommendation

However, the priorities of some rules are changing, i.e. some aspects are less important due to the nature of the In-Memory Column Store but there are also certain patterns of non-optimal coding with higher impact on SAP HANA. Furthermore, there are completely new opportunities for performance tuning on SAP HANA which were not possible in the past, e.g. by pushing complex operations to the database.

In this blog series, Hermann Gahm and myself will drill into more details of the slightly adapted performance recommendations, and give some background information. In addition, we try to give some guidance for several frequently asked questions.

The planned structure of this blog series is as follows:

  • Performance Guidelines for ABAP Development on the SAP HANA Database (this post)
  • Details and background information on adapted guidelines (to come)
  • Frequently Asked Questions for ABAP Development on SAP HANA (to come)

The following recommendations are derived from measurements and experiences based on SAP Business Suite using SAP NetWeaver AS ABAP 7.4 running on SAP HANA SPS5. They will be added to the standard ABAP 7.4 documentation and also supported in standard tools such as the ABAP code inspector.

Guideline Additions in the context of SAP HANA
/wp-content/uploads/2013/03/3_186734.png

As on all database systems, there is a performance overhead associated with every database access for connection handling, SQL parsing, execution plan determination, etc.

The following existing guidelines should be prioritized higher on SAP HANA:

  • For modifying operations (INSERT, UPDATE, DELETE) using array operations should be preferred to single operations when changing many data records
  • Nested SELECT loops should be avoided or replaced if possible by
    • Changing the nested single SELECT statement to an appropriate SQL construct (e.g. FOR ALL ENTRIES, JOIN, sub-query, etc.)
    • Avoiding repeated access to the same data via SQL
    • Using the ABAP table buffer (see existing guidelines for the ABAP table buffer)
/wp-content/uploads/2013/03/4_186735.png

In most cases, SAP HANA does not require secondary indices for good search performance.

To reduce main memory consumption, and to improve insert performance all existing non-unique secondary database indices on columnar tables are removed during migration or do not get created during installation for all AS ABAP systems from SAP NetWeaver 7.4 onwards.

For some use cases secondary indexes can still be beneficial. This is especially true for highly selective queries on non-primary key fields. These queries can be significantly improved by indexes on single fields which are most selective. SAP Note 1794297 describes the procedure to find and create these indexes.

/wp-content/uploads/2013/03/5_186733.png

The guideline is renamed to “Keep load away from the database, but push data-intensive calculations to the database where applicable”

On SAP HANA, it is beneficial to move data-intensive calculations into the database. Nevertheless, it is not recommended to execute the same operations redundantly, e.g. in different user contexts or different dialog steps of the same user. Meaningful buffering of results on the application server should be applied.

The following recommendation should be considered in this light on SAP HANA

Sorting data:In general, the recommendations for sorting remain as before, i.e. if the database does not use the same index for sorting as for selection, then it may be in some situations  more efficient to sort in the application server in particular if all data to be sorted has to be fetched from the application server anyway. However, if the sorting is part of determining the result set (e.g. select top n customers by revenue) or the sorting is part of a larger calculation logic (e.g. within a procedure), it should be done in SAP HANA.

As outlined above, the next post contains some more technical background information for the reasoning behind these recommendations, and some answers to frequently asked questions.

To report this post you need to login first.

14 Comments

You must be Logged on to comment or reply to a post.

  1. P. van Os

    Hi Eric,

    According to note 1662726 the performance of a select statement with a FOR ALL ENTRIES (FAE) clause could be poor. The note described to use a DB Hint.

    However, one of the two prerequisites is that the select statement must not be a join statement.

    What do you recommend in case of a FAE in combination with a JOIN?

    Is a database view the only option?

    Regards,

    Patrick

    (0) 
    1. Hermann Gahm

      Hi Patrick,

      the note can be beneficial in some cases.

      Regarding the FAE with the join there is no hint necessary / available. A join / view (avoiding the FAE) can (but does not have to)  be beneficial. I would recommend to try different variants. It also depends on the version of HANA that you have in use since FAE processing in HANA might be different.

      Kind regards,

      Hermann

      (0) 
      1. Justin Molenaur

        I would second the recommendation to try to eliminate the FAE completely and use a join instead.

        I have a specific example at a customer where a cascading FAE was used as follows:

        Select EKKO where columns in <ranges>.

        Select EKPO FAE in it_EKKO.

        Select EKET FAE in it_EKPO.

        I imagine you’ll find this technique all over the place. Anyhow, when running this program normally on DB2, this specific section of code takes about 80 seconds (out of a total of 100 seconds) runtime.

        When I convert this to a join statement, not even a HANA view, just a simple join – I get a total runtime of about 1.5s, so 52x+ faster.

        SELECT X

        FROM EKKO A

        INNER JOIN EKPO B

        INNER JOIN EKET C

        So I think the name of the game is finding these scenarios where you can make the DB do the heavy lifting that it’s meant to do.

        Happy HANA,

        Justin

        (0) 
  2. Camille Bommart

    Hello Hermann

    I have some basic questions on SAP HANA perfomance with ABAP.

    If we migrate to a SAP HANA database on ECC, will our custom programs be more performant even if we don’t optimize them for HANA?

    Same questions for the BW extractors and for some extraction programs generated by the Informatica tool?

    Globally, what can we expect in terms of performance increase on programs that are not reviewed/optimized after migration to SAP HANA for ECC?

    Thank you

    Richard

    (0) 
  3. Peter Inotai

    Hi Eric,

    Very great summary!

    Is there any change regarding fully buffered tables (eg customizing tables)?

    Or everything stays the same (except that it’s also in memory in DB side)?

    Thanks,

    Peter

    (0) 
    1. Hermann Gahm

      Hi Peter,

      regarding buffered tables there is no change for the golden rules. We continue to use them like we did in the past. Local application server memory access (buffered table in-memory of the local application server) is still faster than remote memory access of the table in-memory of the databse server. With SAP Netweaver 7.4 there is a new table buffer based on internal tables that allows to have secondary indexes in the table buffer. With that we have even more buffering options.

      Kind regards,

      Hermann

      (0) 
  4. Francesco Boccia

    Hi Guys, any news about the next blog series?

    Details and background information on adapted guidelines)

    Frequently Asked Questions for ABAP Development on SAP HANA

    Regards

    (0) 

Leave a Reply