Read internal table using secondary key and binary search

[Former Member]

Hi, I just checking related to read internal table using secondary key and using binary search.

I have taken measurement for both processing block.

Binary search on sorted internal table taking less time compare to secondary key in read table statement.

Kindly suggest which one is preferable in which scenario.

if secondary key takes too much time compare to read table binary search then why we need of secondary key??

Thanks & Regards

Kishan Rana

[former_member84041]

If you are using non-unique secondary keys, the index is not built until first use, this might create a lag in the performance. The triggering abap command (loop etc) can be found to be more time consuming.

Have you also considered the time it requires to sort the internal table?

[Former Member]

Hi Jino,   I have measured the time for sorting the internal table. In read table with binary search with SORT internal table having less time compare to read table using secondary key.   So By considering the performance, READ TABLE with BINARY SEARCH, SORT Internal table is best compare to READ Table with secondary key.   The question is if this is the case then what is the usage of secondary key if we are already having great performance in BINARY SEARCH. Thanks & Regards Kishan Rana

[former_member84041]

Hi Kishan,

Binary search works reliably only on sorted data. SORT is one of the most expensive statements in ABAP. For larger tables with many thousands of rows of data the SORT commands will bring down performance considerably.

And as I mentioned before, the Secondary key refresh is not performed until it is first accessed, so you will see a higher time there. If you were to read the table with Secondary Keys a second time and measure the time, you will be able to see the difference.

Now you ask what is the use of Secondary keys if there is already great performance using binary search on standard table that is sorted. Try using a huge internal table and see for yourself.

So in a nutshell:

• Secondary keys are recommended if there are many read operations on the internal table
• For huge volume of data

Kind regards,

Jino

[Former Member]

Hi Jino,
Consider the below example for the same.


  DATA : ist_mara TYPE TABLE OF mara,

        ist_mseg TYPE TABLE OF mseg WITH NON-UNIQUE SORTED KEY k1 COMPONENTS matnr,

          t0    TYPE i,

          t1    TYPE i,

          rtime TYPE decfloat16.

  FIELD-SYMBOLS : <fs_mara> TYPE mara,

    <fs_mseg> TYPE mseg.

  CONSTANTS    n_mess  TYPE i VALUE 100.

  SELECT * FROM mara INTO TABLE ist_mara UP TO 1700 ROWS.

  SELECT * FROM mseg INTO TABLE ist_mseg FOR ALL ENTRIES IN ist_mara WHERE matnr = ist_mara-matnr.

  GET RUN TIME FIELD t0.

  SORT ist_mseg BY matnr.

  UNASSIGN .

  LOOP AT ist_mara ASSIGNING <fs_mara>.

    READ TABLE ist_mseg ASSIGNING <fs_mseg> WITH KEY matnr = <fs_mara>-matnr BINARY SEARCH.

  ENDLOOP.

  GET RUN TIME FIELD t1.

  rtime  = ( t1 - t0 ) / n_mess.

      WRITE rtime.

  GET RUN TIME FIELD t0.

  UNASSIGN <fs_mara>.

  LOOP AT ist_mara ASSIGNING <fs_mara>.

    READ TABLE ist_mseg ASSIGNING <fs_mseg> WITH KEY k1 COMPONENTS matnr = <fs_mara>-matnr.

  ENDLOOP.

  GET RUN TIME FIELD t1.

  rtime  = ( t1 - t0 ) / n_mess.

    WRITE rtime.

still secondary key taking much time compare to sort and binary search.
Kindly suggest change for above scenario.
Thanks & Regards
Kishan Rana

[Former Member]

Hi Kishan,

Binary search works  only on sorted data. SORT is one of the most expensive statements in ABAP. For larger tables with many thousands of rows of data the SORT commands will bring down performance considerably.

While Coming to Secondary Key, this construct is used for Huge volumes of data.

IF you want to see the advantage of Secondary Key Please Run the Program Twice and measure the time now and you can see lot of performance improvements.

[Former Member]

Hi Satheesha,

I have tried to see the advantage of secondary key by running program multiple time but still its taking much time compare to binary search.

I'm not yet getting the benefits of secondary key over the sort binary search construct.

kindly consider above example for the same.

Thanks & Regards

Kishan Rana

[former_member194613]

Hallo,

please add a loop around the 2 measurements and run it 10 or 20 times.

Show what you did measure. Do not normalize and show just integer numbers for you measured,

(t2 - t1)   and (t1 - t0)

The sort should not be inside the measurement!, it is done once. The secondary key is also created once and if necessary adapted incrementally. 

At the end, the binary search is slightly faster, but you can neglect it. The secondary key gives you the opportunity to optimize a second access type.

The actual test for the advantage of the seconday key is completely different. Measure to intermixed accesses, resort is no option. Primary and secondary access can give you good numbers for both accesses.

best regards       Siegfried