Thursday, 9 November 2017

ABAP Performance Test: Hashed Tables vs Database Buffers

There are two significant techniques to avoid repetitive access to database records: Hashed internal tables and database buffers.

If we decide to use the former method, we need to get the database record into a hashed internal table. Whenever we need to access the record, we read the internal table instead of the database table.

If we decide to use the latter method, all we need to do is to activate the table buffer in SE11. If we do that, the accessed data is cached on the application server, and the cache is used whenever possible. Thus, database access is reduced. There are exceptional cases where the buffer is bypassed, but that’s beyond the scope of this article.

Wondering how they compare in terms of performance, I created a fully buffered database table with example data.

SAP ABAP Development, SAP ABAP Guides, SAP Live, SAP Certifications

I also wrote a program, which accesses the data using both techniques and compares the runtime.

It turns out that hashed access is 3-4 times faster than database buffering.

SAP ABAP Development, SAP ABAP Guides, SAP Live, SAP Certifications

You can use the attached source code yourself and repeat the test; I would like to hear about any disproving results.

REPORT  zdummy01.

CLASS main DEFINITION.
  PUBLIC SECTION.
    CLASS-METHODS execute.
  PROTECTED SECTION.
  PRIVATE SECTION.

    CONSTANTS:
      c_do_times TYPE i     VALUE 100,
      c_matnr    TYPE matnr VALUE 'M1'.

    CLASS-DATA:
      go_text  TYPE REF TO cl_demo_text,
      gv_begin TYPE i.

    CLASS-METHODS:
      hash,
      select,
      start_chrono,
      stop_chrono.

ENDCLASS.                    "main DEFINITION

CLASS main IMPLEMENTATION.

  METHOD execute.

    go_text = cl_demo_text=>get_handle( ).

    start_chrono( ).
    hash( ).
    stop_chrono( ).

    start_chrono( ).
    select( ).
    stop_chrono( ).

    go_text->display( ).

  ENDMETHOD.                    "execute

  METHOD hash.

    DATA:
      lt_dummy
        TYPE HASHED TABLE OF zdummy01
        WITH UNIQUE KEY primary_key COMPONENTS matnr.

    FIELD-SYMBOLS:
      <ls_dummy> LIKE LINE OF lt_dummy.

    go_text->add_line( 'Starting hash read' ).

    SELECT * INTO TABLE lt_dummy FROM zdummy01.

    DO c_do_times TIMES.

      READ TABLE lt_dummy
        ASSIGNING <ls_dummy>
        WITH TABLE KEY primary_key
        COMPONENTS matnr = c_matnr.

    ENDDO.

  ENDMETHOD.                    "hash

  METHOD select.

    DATA ls_dummy TYPE zdummy01.

    go_text->add_line( 'Starting select' ).

    DO c_do_times TIMES.

      SELECT SINGLE *
        INTO ls_dummy
        FROM zdummy01
        WHERE matnr EQ c_matnr.

    ENDDO.

  ENDMETHOD.                    "select

  METHOD start_chrono.
    GET RUN TIME FIELD gv_begin.
  ENDMETHOD.                    "start_chrono

  METHOD stop_chrono.

    DATA:
      lv_diff  TYPE i,
      lv_difft TYPE cl_demo_text=>t_line,
      lv_end   TYPE i.

    GET RUN TIME FIELD lv_end.
    lv_diff = lv_end - gv_begin.
    WRITE lv_diff TO lv_difft LEFT-JUSTIFIED.

    go_text->add_line(:
      'Runtime result:' ),
      lv_difft ).

  ENDMETHOD.                    "stop_chrono

ENDCLASS.                    "main IMPLEMENTATION

START-OF-SELECTION.
  main=>execute( ).
SAP Online Guides, Tutorials, Materials and Certifications.

Related Posts

2 comments:

  1. Curious about whether your Apple Watch can sync up with your Android phone? This article has all the answers!
    Don't miss out on this informative read – click now to explore compatibility options and unlock a world of possibilities!

    ReplyDelete
  2. I’ve learned so much from this post. I now have a clear action plan for ABAP Performance Test: Hashed Tables vs Database Buffers Thanks for such informative article. Great content.
    Data Science Courses in China




    ReplyDelete