Internal tables background

[Former Member]

Hello Experts,

i have been going through many threads but no answer found.

Assume i define internal table as for instance DATA itab TYPE SORTED TABLE OF dtab WITH NON-UNIQUE KEY field_a.

1. If the database table is activated, then for primary keys the index table is created in database. But what happens with the internal table above? Is there a similar background process? Also the hashed table.

2. Assume i want to LOOP itab INTO wa WHERE field_b = 'aaa'. How is in this case table scanned? Is there a full table scan applied? The same with READ statement with addition WITH KEY field_b.

3. Also i dont understand the definition that for "structured line type only not numerical fields are included in defauld key". But i can use WHERE statement for fields that are not included in default key. I know there are 2 ways how to access internal table, index and key access.

But base on my experiences every field in table can be accessed, regardless i have explicitly defined it or not. How is this handled by runtime?

I would appreciate your help, but please avoid using the guide definitions.

Thank you

[Former Member]

Hello Experts,

i have been going through many threads but no answer found.

Assume i define internal table as for instance DATA itab TYPE SORTED TABLE OF dtab WITH NON-UNIQUE KEY field_a.

1. If the database table is activated, then for primary keys the index table is created in database. But what happens with the internal table above? Is there a similar background process? Also the hashed table.

2. Assume i want to LOOP itab INTO wa WHERE field_b = 'aaa'. How is in this case table scanned? Is there a full table scan applied? The same with READ statement with addition WITH KEY field_b.

3. Also i dont understand the definition that for "structured line type only not numerical fields are included in defauld key". But i can use WHERE statement for fields that are not included in default key. I know there are 2 ways how to access internal table, index and key access.

But base on my experiences every field in table can be accessed, regardless i have explicitly defined it or not. How is this handled by runtime?

I would appreciate your help, but please avoid using the guide definitions.

Thank you

[Former Member]

Hi,

Regarding your questions:

1. Noting happens to the internal table above when the underlying transparent table is activated. The primary key index of the internal table is build up at runtime when you insert lines into the internal table. (At runtime, the current activated definition of the transparent table is used.)

2. If you perform a LOOP with a WHERE condition and the condition is a field not contained in the primary key of the internal table, a full table scan is performed. The same hold for a READ TABLE.

3. Yes, you can use any field of the table definition for a WHERE condition (for standard and sorted tables; for hashed tables you have to use the exact key). In case you use fields not contained in the primary key (or a secondary key) the system may perform a full table scan (because no suitable index is available to speed up access).

I hope that helps.

Kind regards,

Valentin

[Former Member]

Hi Valentin,

regarding the 1. question. To be 100% sure, lets assume my internal table has 5 fields. So when i define internal table as for instance DATA itab TYPE SORTED TABLE OF dtab WITH NON-UNIQUE KEY field_a, then index table, something like this is created in runtime?

field_a	Index
a	1
b	2
…	…

Thanks, the rest questions were answered clearly.

Jan

[Former Member]

Sorted tables have an internal index so yes in main memory there would be a similar table available during the program runtime.

[Former Member]

Hello Jan,

I'm not sure what you refer to when you say "then index table." Every sorted table is per definition also an index table (like any standard table). Anyhow, with the definition of the sorted table with non-unique key some kind of non-visible table is created by the ABAP run-time system - as you wrote - with two columns "field_a" and "start_index". When entries are inserted into the table the table is built up. After inserting

Entries

field_a = "a", ...

field_a = "b", ...

field_a = "a", ...

the table contents look like

field_a     start_index

a               1

b               3

Kind regards,

Valentin

[Former Member]

Valentin,

i was refering to Index, meaning when you create database table then copy of this table having few number of fields (Primary keys) is created (Index table).

My last question, assume i create two tables with 5 fields:

1. DATA itab TYPE STANDARD TABLE OF dtab, then i sort this table with field_c. Perform binary search with key access field_c.

2. DATA itab TYPE SORTED TABLE OF dtab WITH NON-UNIQUE KEY field_c. Here i perform too search with key access field_c.

Which search is faster?

Thank you

Jan

[Former Member]

Hi Jan,

My gut feeling says that building up the standard table contents is faster (because the system just has to append entries) than buiding up the sorted table (because the system may re-sort existing entries during the insert process), sorting the standard table kind of compensates for this and the speed of the key access (e.g. with a READ TABLE statement) is the same for both table types.

My brains says that the only way to find out any difference is by building a complete example and doing a performance measurement.

Regards,

Valentin

[Former Member]

Yes you're right , it is, fetching same set of records from db to itab and measuring runtimes, I found out sorted table takes slightly more time, but its very minimal.

You can measure this by writing a very small piece code and measuring run times using get runtime field.

[matt]

In most cases, maintainability and stability are more important than speed. Making a program run faster doesn't usually impact a company's profits. Having a program that is hard to maintain, or fails often, is costly, and so does impact the profits.

As a matter of design, you should use the table type appropriate for your application. If you're looking up data by unique key, then that should be a HASHED table. If you need data to be sorted, then a SORTED table.

The area I work in typically deals with millions of records. For me, performance and memory consumption are things I really have to take into consideration. Funnily enough, when I have to optimise a program, I've achieved great gains by switching STANDARD table to HASHED or SORTED. I've never had to switch back to a STANDARD table.

[Jelena_Perfiljeva]

Jan, I suspect you might be just confused by READ... WITH KEY. Here "key" is not the "key" in the same sense as in the database tables.

In the ABAP Editor there are examples available including the performance ones. It's all there, so check it out. Also we can use Runtime Analysis to track the performance and test any theory.

+1 to what Matthew said. I've never really wondered how exactly hashed or sorted tables work "behind the scenes" (there could be some ABAP gnomes for all I care), but these basic rules of which table type is best for what are a must-know.

[matt]

Close. It's ABAP pixies.

[Former Member]

Thank you all for your answers, i appreciate it.