Use of (primary) key in internal tables

[Clemenss]

Hi,

this question is for the real experts (like Horst Keller) so please don't answer if not really know...

I delare an internal table with various fields as SORTED TABLE WITH UNIQUE KEY table_line - I won't have any duplicates as I fill from a database (customizing) table.

In a LOOP AT this table WHERE <field> = <value>, will the loop use the key if it's the first key?

What are the rules of index use in LOOP?

I mean NOT secondary index as they can be defined now.

Thanks a lot and best regards,

Clemens

[custodio_deoliveira]

Hi Clemens,

I really can't believe that a respected topaz level member like yourself would discriminate other users by asking them not to reply to your question. Surely you are being VERY disrespectful to other members.

If you have a direct question to Dr Horst Keller (or someone "like" him), I'm sure you can figure out his email address. (tip: it's first_name dot last_name at company dot com)

Regards,

Custodio

[Clemenss]

Hi Custodio,

I feel very sorry that I have hurt you.

My wording was definitely not correct. Usually, if I read something that hurts me, I prefer not to reply at all except I have an idea for a solution.

I did not contact Horst directly because I dont' want to add to the pressure he is exposed to.

I will try to find an answer elsewhere still hoping to get it here.

Try to find better expression in the future.

Best regards

Clemens

[matt]

I disagree. I spend a lot of time rejecting posts that show that certain responders (who are just hunting for points rather than wanting to help) have not understood the first thing about the question. Am I discriminating against them?

Anyone who feels they are an expert can reply, but it might make people really think before they do. That can only be a good thing.

In my view, discrimination against newbies in this way is not disrespectful in any way, shape or form.

[custodio_deoliveira]

Hi Matt,

I wanted to let this part of the discussion die after Clemens recognized that he didn't do well (kudos to him), but I think you deserve a quick reply, at least in one point:

Matthew Billingham wrote:

I disagree. I spend a lot of time rejecting posts that show that certain responders (who are just hunting for points rather than wanting to help) have not understood the first thing about the question. Am I discriminating against them?

No, you are not. You are simply doing your job as moderator (very well, btw). It's completely different from asking only "real experts" (whatever the heck it means) to answer your question. Not even close.

I could go much further, but as I said, I want to let it die, so I hope Clemens get his answer, be it from a "real expert", a fake expert, a newbie, from doco, whatever.

Cheers,

Custodio

[raymond_giuseppi]

Clemens... that's is the doc, look at Optimization of the WHERE Condition (so here answer is YES)

Regards,

Raymond

[matt]

Bit of a newbie question wasn't it - when the answer is "read the ABAP help!"

[paul_bakker2]

Shhh. REAL experts only. Everyone else keep quiet.

[Clemenss]

Hi Matthew,

thank you for the support regarding my initial post.

Well, I tried to get the answer from the documentation.

My question is about a table declared as SORTED TABLE WITH UNIQUE KEY table_line.

What about a LOOP ... WHERE <field> = <value>?

For the database, we know that an index is used, when then first field is (all fields are) first part of the primary index (or another index). Using index field n without giving index field n-1 results in sequential read.

In my internal table, this would be the field MANDT because it is declared TYPE <ddic table>. For database, implicitly SY-MANDT will be used. To be sure I specified mandt = sy-mandt in the WHERE clause and the the following but not all fields.

The first n fields (say field1 ..fieldn) I know and I can specify as <field> = <value>. The last fields (field n+1 ... field n+x) are the attributes I am interested in. They are also part of the key table_line.

The only thing I found is about a table of references of the table structure using table_line-><component> for the access. That will probably result in code some of my colleagues would not fully understand.

So my question is not really answered. I could not find information about using the key named table_line. Will the compiler know that my fields used in the WHERE clause are part of the key table_line and optimize accordingly?

Actually it would cost me some time to setup a measure routine to check. So I still hope for some experts guidance. Of course, everyone who feels he can add more to the solution than posting links it invited to help. Raymond: Thank you for the links. I coudn't find the string 'table_line', please help.

Thanks and regards

Clemens

[Clemenss]

Hi Paul,

don't know how to handle: Just ask the question and get tons of useless answers. Hard to filter signal out of noise. Ask the question and ask for qualified answers starting a flame war. Hard to filter signal out of noise. But more fun 🙂

Regards

Clemens

[paul_bakker2]

Clemens,

Yes, it is more fun , but with that approach I think you may be filtering out some fine signal!

An interesting question can provoke some innovative, huh-never-thought-of-that answers from people who know their stuff - or are willing to investigate on your behalf - but aren't German PhDs...

"Actually it would cost me some time to set up a measure routine to check."

Many hungry newcomers would be happy to do this for you, but now you've put them off.

cheers

Paul

[Private_Member_7726]

Hi,

No, I believe (why should the table_line, whatever the resulting key, cause something to differ from the normal rules..?), based on this:

There is no implicit selection of a suitable key or index. The used table key or table index is always specified uniquely. The syntax check issues a warning if there is a suitable secondary table key but this table key is not used. This warning should be removed through using the key. However, in exceptional cases, it can be bypassed using a pragma.

The  values for using primary key would need to be provided in full, if I remember correctly.

No,

LOOP AT this_sorted_table USING KEY primary_key

  WHERE

     ...

compiles with partial key, as long as the "beginning of key" is somewhere in WHERE condition (and rest of WHERE optimization conditions are fulfilled). My understanding of the red statement above still is - without the addition USING KEY, no key will be used by LOOP...

cheers

Jānis

Message was edited by: Jānis B

[Clemenss]

... an also the documentation says

If no explicit table key name is declared after USING KEY, the order in which the rows are read depends on the table type as follows:

  Standard tables and sorted tables

The rows are read by ascending row numbers in the primary table index.

So I might expect that the where clause will also take the key into account.

Who could write a test program and measure the difference?

  - declare a table with fields f1 to fn (char fields) as sorted table with unique key table_line - may take any customizing table.

  - fill it with like 10, 100, 1000 records with (random) values (from existing customizing table).

  - loop at the table where f1 = value1 and f2 = value2 transporting no fields

  - loop at the table using key primary_key where f1 = value1 and f2 = value2 transporting no fields

Check the time difference.

Please ...

Best regards

Clemens

[paul_bakker2]

You wrote:

Who could write a test program and measure the difference?

Is this request / challenge open to everyone?

cheers

Paul

[Clemenss]

Just do it... 🙂

[raymond_giuseppi]

I wrote a small sample (read account item from 3 company codes, with up to n rows, so I extract 1000000 records from a company and 1000 records for 2 others) same data in the SORTED BY TABLE_LINE and a standard not sorted table. Then looped for records of one of the small (1000 records) company , but I also add MANDT in the WHERE statement, and used CLIENT SPECIFIED in the SELECT statement. The LOOP was more than 100 x faster in the SORTED table with full line record key, without the first key field MANDT, durations were similar.

But for me,  its only prove that the Prerequisites

The logical expression of the WHERE condition can be transformed to a key specification.

is verifyed when we have the full record in the key, so conform to documentation (Abap is able to identify first fields of structure... )

Regards,

Raymond

[Clemenss]

Hi Raymond.

thank your for evaluation. I did it myself today with comparable results. I used a customzing table with 28 records and 6 key fields, defined as sorted table with unique key table_line.

There is absolutely no significant difference between LOOP ...USING PRIMARY KEY WHERE ..., LOOP ... WHERE (no primary key), also no difference what fields I used in the WHERE clause. I'd like to know the algorithm for scanning the internal table key - use HANA?

I did not try defining the internal table with unique key f1 f2 f3..., this is what I will do next (when I have time to spare).

Interesting story, any more volunteers? 🙂

BTW: Yes, an internal table without any key specification is always the worst solution - good proof!

AND: The SELECT ... CLIENT SPECIFIED causes only to select records from all CLIENTS present in the system, probably exactly one. The CLIENT field is transferred always for MANDT = SY-MANDT if CLIENT SPECIFIED is not given

[matt]

Can I ask, once all the analysis is complete, that someone blog about it please!

[Private_Member_7726]

Yes, I have misread the sentence I quoted above; the primary key of a sorted table will be used regardless of whether it's specified in LOOP, as long as the beginning of the key (including MANDT...) is in the condition (on 702).

(reposted code separately)

cheers

Jānis

Message was edited by: Jānis B

[raymond_giuseppi]

Also the expected rate of success of the where clause will give differents. If nearly every record  checks the where clause, I wont expect many difference for the different table type, even the standard table may be faster for load+loop performance...

Regards,

Raymond

[Private_Member_7726]

The code:

REPORT  zjbtst20.

TYPES: BEGIN OF ty_item,

         mandt TYPE symandt,

         group(3) TYPE c,

         counter(8) TYPE n,

         a TYPE sysuuid_c32,

         b TYPE sysuuid_c32,

         c TYPE sysuuid_c32,

         d TYPE sysuuid_c32,

         e TYPE sysuuid_c32,

       END OF ty_item,

       tyt_sorted_items TYPE SORTED TABLE OF ty_item

         WITH UNIQUE KEY table_line .

DATA: gt_sorted_table TYPE tyt_sorted_items .

DATA: gs_sorted_table_item TYPE ty_item .

DATA: gx_ex TYPE REF TO cx_uuid_error .

DATA: go_timer TYPE REF TO if_abap_runtime .

DATA: g_runtime TYPE int4 .

DATA: g_groupk(3) TYPE c .

DATA: g_counterk(8) TYPE n .

LOAD-OF-PROGRAM .

  gs_sorted_table_item-mandt = sy-mandt .

  DO 3 TIMES .

    WRITE sy-index TO gs_sorted_table_item-group RIGHT-JUSTIFIED .

    DO 10000 TIMES .

      TRY .

          gs_sorted_table_item-counter = sy-index .

          gs_sorted_table_item-a = cl_system_uuid=>create_uuid_c32_static( ).

          gs_sorted_table_item-b = cl_system_uuid=>create_uuid_c32_static( ).

          gs_sorted_table_item-c = cl_system_uuid=>create_uuid_c32_static( ).

          gs_sorted_table_item-d = cl_system_uuid=>create_uuid_c32_static( ).

          gs_sorted_table_item-e = cl_system_uuid=>create_uuid_c32_static( ).

        CATCH cx_uuid_error INTO gx_ex.

          MESSAGE gx_ex TYPE 'E' .

      ENDTRY .

      INSERT gs_sorted_table_item INTO TABLE gt_sorted_table .

    ENDDO .

  ENDDO .

START-OF-SELECTION .

  g_groupk = 2 .

  go_timer = cl_abap_runtime=>create_hr_timer( ) .

  go_timer->get_runtime( ). "init

  DO 10000 TIMES .

    g_counterk = sy-index.

    LOOP AT gt_sorted_table INTO gs_sorted_table_item

      WHERE

        mandt = sy-mandt AND

        group = g_groupk AND

        counter = g_counterk .

      EXIT .

    ENDLOOP .

  ENDDO .

  g_runtime = go_timer->get_runtime( ) .

  WRITE: / g_runtime.

  go_timer = cl_abap_runtime=>create_hr_timer( ) .

  go_timer->get_runtime( ). "init

  DO 10000 TIMES .

    g_counterk = sy-index.

    LOOP AT gt_sorted_table INTO gs_sorted_table_item

      USING KEY primary_key

      WHERE

        mandt = sy-mandt AND

        group = g_groupk AND

        counter = g_counterk .

      EXIT .

    ENDLOOP .

  ENDDO .

  g_runtime = go_timer->get_runtime( ) .

  WRITE: / g_runtime.