databasesortingprimary-keyabapopensql

Why is a full TADIR select with ORDER BY PRIMARY KEY much slower than INTO sorted table?


Having the following statements:

SELECT * FROM tadir ORDER BY PRIMARY KEY INTO TABLE @DATA(lt_tadir).
DATA lt_tadir TYPE SORTED TABLE OF tadir WITH UNIQUE KEY pgmid object obj_name.`
SELECT * FROM tadir INTO TABLE @lt_tadir.

Why is the first one around 4 times slower (verified on multiple systems? Relevant statements from the documentation:

For performance reasons, a sort should only take place in the database if supported by an index. This guaranteed only when ORDER BY PRIMARY KEY is specified

If a sorted resulting set is assigned to a sorted internal table, the internal table is sorted again according to the sorting instructions.

First I thought maybe column storage is an issue, but I tried another column storage table where both statements are around similar (even though the second one seems to be a bit faster each time). Same for the row storage I tested.

Also I somehow would have expected that the sort in ABAP (second Docu snipped) would have an performance impact. But it outperforms the primary key index select.

Tables aren't fully buffered, and even if that was the case, ORDER BY PRIMARY KEY does use the buffer.

Any ideas?


Solution

  • I ran the test (several times) and got at least similar execution times for both statements. The Application server sort version about 25% faster than HANA sort. So my mileage was different.

    That HANA sort isnt "faster" is only mildly surprising until you look at the table definition. Sorting the entire inverted hash index not what it was designed for. :)

    HANA TADIR definition

    Some "rules" are meant to be broken.
    Sorting 5 Million keys with inverted hashes might a good example. And now you have 5 Million records in memory, reading rows quickly by key will favor the internally sorted table. anyway ;)

    DATA lt_tadir TYPE SORTED TABLE OF tadir WITH UNIQUE KEY pgmid object obj_name
    

    is access friendly than the simple Standard Table anyway. @Data(lt_tab)

    There are known disadvantages with inverted hash indexes. With typical access not normally noticed. https://www.stechies.com/hana-inverted-individual-indexes-interview-questionsand-ans/