ABAP Performance Test: Hashed Tables vs Database Buffers

Thank you Kerem for sharing, great blog! I tested on my HANA system and results are interesting, my table has 81 rows with only one primary key. Table also contains additional 6 columns.

This is my first test with the same code without doing any changes; [BUFFERING IS NOT ACTIVATED YET]

Starting hash read
Runtime result:
5.896
Starting select
Runtime result:
90.742

First i added a sorted table with uniqe key;

  DATA:
      lt_dummy
        TYPE SORTED TABLE OF ZTEST
        WITH UNIQUE KEY primary_key COMPONENTS DEV_ID.

Here is the result;

Starting hash read
Runtime result:
6.003
Starting sorted read
Runtime result:
1.721
Starting select
Runtime result:
86.529

Now i added a secondary index with non-unique sorted key;

DATA:
      lt_dummy
        TYPE STANDARD TABLE  OF ZTEST
        WITH NON-UNIQUE SORTED KEY key1 COMPONENTS DEV_ID.

here is the result with secondary index;

Starting hash read
Runtime result:
7.532
Starting sorted read
Runtime result:
2.302
Starting sorted secondary index read
Runtime result:
1.901
Starting select
Runtime result:
92.297

now i activated fully TABLE buffering only; [ for the select statements for hash, sorted and sorted_secondary methods from table, i am bypassing buffering to be fair ]

Here is the result;

After buffering, first RUN

Starting hash read
Runtime result:
6.049
Starting sorted read
Runtime result:
1.783
Starting sorted secondary index read
Runtime result:
1.523
Starting select
Runtime result:
16.972

and i re-run the program, ( i think first run moved the records to the shared memory area that is why it took 16.972)

Here is the result after re-run;

After buffering SECOND RUN 
Starting hash read
Runtime result:
4.684
Starting sorted read
Runtime result:
1.960
Starting sorted secondary index read
Runtime result:
1.632
Starting select
Runtime result:
2.365

i updated one of the record details from table and here is the result;

Starting hash read
Runtime result:
5.947
Starting sorted read
Runtime result:
1.956
Starting sorted secondary index read
Runtime result:
1.760
Starting select
Runtime result:
9.551

and re-run results of same records;

Starting hash read
Runtime result:
5.145
Starting sorted read
Runtime result:
1.943
Starting sorted secondary index read
Runtime result:
1.741
Starting select
Runtime result:
2.120

If we change the order of methods, results are changing as well. Somehow first select statement always slow even you bypass buffering; 

I have another test here, a dummy select was added to the execute method just to select the table 1 time, i think on the backend there is another shared area handled by DB layer that is why first select was always slow as per HASH read results;

Here is the code;

 method execute.
    data: lt_test type table of ztest.
    go_text = cl_demo_text=>get_handle( ).

* dummy select
    select * into table lt_test from ztest bypassing buffer.

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

    start_chrono( ).
    sorted( ).
    stop_chrono( ).

    start_chrono( ).
    sorted_secondary( ).
    stop_chrono( ).

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

    go_text->display( ).

  endmethod.                    "execute

and results:

Starting hash read
Runtime result:
1.834
Starting sorted read
Runtime result:
1.630
Starting sorted secondary index read
Runtime result:
1.678
Starting select
Runtime result:
2.043

Interesting results

Thank you again for sharing. I have never used buffering on my developments but above results gave me some ideas.