software, software.sap

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. We ignore other internal table types because hashed tables are proven to be the fastest approach when reading single records.

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.

Screen Shot 2017-10-20 at 19.12.24

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.

Screen Shot 2017-10-20 at 19.14.15

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
*----------------------------------------------------------------------*
*
*----------------------------------------------------------------------*
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( ).

 

Standard

2 thoughts on “ABAP Performance Test: Hashed Tables vs Database Buffers

  1. Pingback: ABAP Nested Loop Performance – Dr. Kerem Koseoglu

  2. Pingback: Using Indexes with Internal Tables to Increase Performance – Dr. Kerem Koseoglu

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s