Common performance issues with the inline declarations

[jihoon_kim]

The Inline declaration provides an effortless way to create data objects and better readability to code readers. Since SAP NetWeaver ABAP 7.4 introduced this new feature, it has been the preferred way over explicit data declaration.

We will always have to consider the data volume for the internal tables to avoid possible performance issues. It is more critical when we use the inline declaration because it creates only standard-type tables. Performance issues happen if the internal table created by the inline declaration contains many rows and we access it inside a loop.

A common performance issue 

select * from dd03l FOR ALL ENTRIES IN @lt_tables

  where tabname = @lt_tables-tabname

  into table @data(lt_tabfields).



loop at lt_tables ASSIGNING FIELD-SYMBOL(<fs_tab>).

  loop at lt_tabfields ASSIGNING FIELD-SYMBOL(<fs_tabfields>) 

                    where tabname = <fs_tab>-tabname.

  endloop.

endloop.

The internal table LT_TABFIELDS is created with the inline declaration at the SELECT statement. As we can see in the debugger, the table type is the standard and it does not have any keys.

 

Suppose we have to access the internal table inside a loop. In that case, we will have to define the internal table explicitly as sorted or hashed types or with secondary sorted keys to avoid possible performance issues.

Explicit data declaration example #1 (with sorted type internal table)

data : lt_tabfields2 type sorted table of dd03l with non-UNIQUE KEY tabname.



loop at lt_tables ASSIGNING FIELD-SYMBOL(<fs_tab>).

  loop at lt_tabfields ASSIGNING FIELD-SYMBOL(<fs_tabfields>) 

       where tabname = <fs_tab>-tabname.

  endloop.

endloop.

Explicit data declaration example #2 (with secondary sorted key)

data : lt_tabfields type table of dd03l with non-UNIQUE sorted KEY k1 COMPONENTS tabname.



loop at lt_tables ASSIGNING FIELD-SYMBOL(<fs_tab>).

  loop at lt_tabfields ASSIGNING FIELD-SYMBOL(<fs_tabfields>) 

         using key k1 where tabname = <fs_tab>-tabname.

  endloop.

endloop.

Response time comparison for the nested loop (test case : 10K rows in the LT_TABLES, 115K rows in the LT_TABFIELDS)

Standard type (via inline declaration)	147,219ms
Sorted type (via explicit declaration)	81ms
Standard type with secondary key (via explicit declaration)	134ms

As you can see in the runtime comparison above, the inline declaration can cause performance issues when the developer uses it without considering the future data volume. For better performance, it's always recommended to use the code inspector (transaction SCI) before transporting the ABAP programs to the production system. The check variant will have to include the low-performance operation for standard type internal table to detect performance issues with the inline declaration.

Thank You For Reading, please share feedback and thoughts in a comment. And consider following the ABAP Development environment topic and jihoon.kim for future similar content.

 

[Rodrigo-Giner]

How come the standard table with sorted secondary key takes that much, it should not as the sorted type ?

Standard type (via inline declaration)	147,219ms
Sorted type (via explicit declaration)	81ms
Standard type with secondary key (via explicit declaration)	134ms

I mean it's closer to standard than the sorted.... why ?

[jihoon_kim]

Hi thanks for the comment. By the way, the response time for standard type is 147219 ms which is 147.2 seconds. it is way longer than other two.

[Jelena]

Um... Maybe I'm off base here but with the title "performance issues with the inline declarations" I was expecting to see some alarming revelations about the fact that using inline declarations is somehow slower vs, say, regular declarations. It's not the case though.

What you are describing is not an issue "with the inline declarations". It's an issue with someone using inline declarations while not understanding that this will declare a plain standard table. I'm too lazy to search but 100% sure this is stated in documentation.

So, if someone is writing code for a scenario where a sorted or hashed table would be beneficial then just don't use inline declarations and declare the table type that you need. This is in no way different from the ABAP development way before inline declarations were introduced. "The right table type for the right task" has always been a good principle to follow.

As you correctly noted though, you need to consider data volume. If we don't expect large volume, then simpler inline declaration could still be advantageous because it makes the code easier to write and maintain. (Personally, I'm a big fan of internal table declarations in SELECT because it saves so much effort that previously we had to spend on pre-declaring every field.)

I'm sure 10 people will now jump with the comments like "it's not big volume today but TOMORROW!!!" and that's exactly why I'm not a fan of some kind of dogmatic proclamations here. It's "dealer's choice", the developer need to understand possible options, understand the business case, know the business and company. And make the decision that they feel is best, all things considered.

And please, let's stop using Hungarian notation already.

[afordham]

As a big fan of Hungarian notation, can I take my Like back? 😉

[matt]

No. Your lack of taste in one area doesn't mean your taste is bad in all.

(I liked your comment, but loath Hungarian notation, so the same applies to me!)

[matt]

In ADT quick assist (Ctrl-1) on an inline declaration offers the chance to explicitly declare the internal table. From there you can easily change the type and add keys.

[matt]

I'm sure 10 people will now jump with the comments like "it's not big volume today but TOMORROW!!!"

I believe that modern coding practice is to code only for today, so absolutely agree with you. In the past I wouldn't have though!

[kilian_kilger]

When introducing Inline Data Declarations in SELECT we immediately thought about a possibility to support using hashed or sorted tables. Until now we did not find any time to implement something in this direction.

Do you have a good syntax?

What came to my mind then was:

SELECT key field1, key field2, field3 FROM table INTO HASHED TABLE @DATA(foo).

The problematic part is the key specification in the SELECT list. As we already have old syntax (separated by spaces) and new syntax (separated by comma) this will be some weird mix. I do not know if that is a good idea.

Of course you also have all the goodness of unreserved keywords when somebody has a column named KEY in his/her table. But, fortunately, this is forbidden in TRESE as I looked up in SE16 a second ago ;-). But there might still be some old tables from <= 3.0 which use KEY as a field. The TRESE checks in DDIC are only active (to my knowledge) for new tables which came after release 3.0. But this shouldn't bother us much anyhow.

Do you have a better syntax?

Specifying the key after the column would be probably more easy from a grammatical standpoint.

SELECT field1 AS blub KEY FROM table INTO SORTED TABLE @DATA(bar).

[gabmarian]

And please, let's stop using Hungarian notation already.

Maybe as a Hungarian I should a campaign against it? Would it be taken more seriously then? 🙂

<fs_name> is my favorite one though, you need more emphasis in case someone overlooked the angle brackets.

[UweFetzer_se38]

Hi Kilian,

I'd prefer the first syntax variant (looks more like the CDS syntax). I don't think this would look like mixed because it's definatly new syntax (commas and @).

Tables with "key" in the field list may work also: SELECT key key, key field_2, field_3 ....

so -> start coding 🙂

Cheers, Uwe

[UweFetzer_se38]

We may ask rich.heilman what he thinks about Hungarian Notation 😉

[kilian_kilger]

Hi Uwe,

the problem always starts when you only have 1 field in the select list, like:

SELECT key field1 FROM blub ...

Fun fact: As there are also SELECTs without INTO clause, there are selects which have *neither* old or new syntax.

Example:

SELECT count(*) FROM table.

This statement has no old nor new syntax. It is timeless.

Of course, in our case we would not allow KEY without inline declaration and thus into clause, so we always know if we have old or new syntax. But it is very annoying for people if they can disambiguate the SELECT list only after reading the INTO clause (maybe 1 page below).

In any case, we solved many of these problems in the past (for example a larger problem when we introduced expressions in ORDER BY). We can solve them here too. But an unambiguous syntax would be definitively beneficial.

[horst_keller]

Pattern or Anti Pattern?

SELECT *

       FROM scarr

         INNER JOIN spfli ON scarr~carrid = spfli~carrid

       INTO TABLE @DATA(itab).



TYPES line LIKE LINE OF itab.

DATA jtab TYPE SORTED TABLE OF line WITH NON-UNIQUE KEY scarr-carrid spfli-connid.

jtab = itab.

[kilian_kilger]

Definitively anti - pattern.

Why use the inline data declaration at all in this case?

We should implement it correctly ...

[horst_keller]

"Why use the inline data declaration at all in this case?"

To avoid tedious coding such as:

TYPES BEGIN OF wa.

INCLUDE TYPE scarr AS scarr RENAMING WITH SUFFIX _scarr.

INCLUDE TYPE spfli AS spfli RENAMING WITH SUFFIX _spfli.

TYPES END OF wa.

TYPES itab TYPE SORTED TABLE OF wa

           WITH NON-UNIQUE KEY carrid_scarr connid_spfli.

xxx

 

[afordham]

This gets my most-useful-post-of-the-week award!

[matt]

I see that one so often. Also

<lfs_name>.

 

[gabmarian]

<lfs_name>

It's even better, especially with inline declaration, since you can double-emphasize its "localness" as well!

[matt]

Strange. I'm sure that something like this used to dump.

TYPES: BEGIN OF ty_line,

         a TYPE i,

       END OF ty_line.



DATA itab TYPE STANDARD TABLE OF ty_line.

DATA jtab TYPE SORTED TABLE OF ty_line WITH NON-UNIQUE KEY a.



itab = VALUE #( ( a = 2 )

                ( a = 1 ) ).



jtab = itab.

 

Has something changed since 46c?

 

[matt]

I'm torn. From one perspective it's like using a cast... but I don't like using two variables when one will do.

[pokrakam]

Hi Kilian,

That would be a great addition. I think this makes the most sense using the newish FIELDS syntax, which would be consistent with CDS too:

SELECT FROM table

  FIELDS KEY f1 as myKey, 

         KEY f2 as someField, 

             f3 as anotherField

  WHERE ...

  INTO SORTED TABLE @DATA(itab).

Personally I wouldn't be opposed to the idea to make FIELDS the required syntax to specify specialised table types, I'd write it this way for readability.

Alternative: KEY field definitions of SORTED tables could be omitted if there is an ORDER BY clause, where the keys could be derived.

[horst_keller]

Nothing that I know of. I.m.h.o. you could always assign standard tables to sorted tables and sorting took place.

[matt]

And all these years I've been using INSERT LINES OF...

Ah well, learned something today. 😀

[mahlzeit1948]

As already pointed out the standard table is WAY slower.

The difference between primary sorted and secondary sorted is probably due to the secondary indices being "lazy". This means they are only generated the first time they are used. So the first read/loop-where statement using a secondary index will be slower, as the index needs to be built. So it's not really slower....just slower at a different point in processing.

[gabmarian]

Am I correct, that the assignment

jtab = itab.

Does not trigger a "physical duplication" until either of the tables change?

So when dealing with large data sets, where the session memory cannot handle another copy the following is appropriate to avoid out of memory dump?

jtab = itab.

CLEAR itab.

...continue with jtab adjustments

 

[horst_keller]

The concept is called table sharing ...

[gabmarian]

Thanks for the link! I remembered the concept but not the name.

[SuhaSaha]

When i see <LFS...> it gets on my nerves!

[Rodrigo-Giner]

Lol my bad. In my country "," it's de decimal separator 😛 Now it makes sense

[horst_keller]

It is correct that currently, only standard tables with empty key and no secondary keys can be declared inline as internal tables in the INTO clause of a SELECT statement.

But this doesn't mean that we have "common performance issues with the inline declarations".

It rather means, that a (large) internal table declared inline at that position is mainly suitable for index accesses but not for key accesses.

If key accesses are important, the internal table should not be declared inline but with a declaration statement and with an appropriate key.

With other words, the proramming guideline "Selecting the Table Category" must be considered here as for all positions, where an internal table is declared.

[Rodrigo-Giner]

I wouldn't change the SELECT statement, because you will need to deal with LOTs of addition. And also the internal table definition should have nothing to do with the SELECT statement itself.

In my opinon the option to create a SORTED, HASHED should be within the "@DATA(itab)" space, perhaps using curly brackets { } like when you deal with variable in strings with pipes| |

Where you can have options like:

TYPE = STD | HASHED | SORTED

KEY = UNIQUE | NON-UNIQUE

COMPONENTS = ( FIELD1, FIELD2, ...)

So you end up with something like this:

SELECT FROM table

  FIELDS f1, 

         f2,

         f3 as anotherField

  WHERE ...

  INTO TABLE @DATA({ itab TYPE = SORTED 

                           KEY = NON-UNIQUE 

                    COMPONENTS = ( f1, f2 ) }).

[Rodrigo-Giner]

Like I posted up in the other Reply, using curly brackets within the "@DATA(itab)" space. 

TYPE = STANDARD | HASHED | SORTED

KEY = UNIQUE | NON-UNIQUE | EMPTY | DEFAULT

COMPONENTS = ( FIELD1, FIELD2, ...) -> Only needed with UNIQUE or NON-UNIQUE

So you end up with something like this:

SELECT FROM table

  FIELDS f1, 

         f2,

         f3 as anotherField

  WHERE ...

  INTO TABLE @DATA({ itab TYPE = SORTED 

                           KEY = NON-UNIQUE 

                    COMPONENTS = ( f1, f2 ) }).

[Jelena]

I think an official statement from the Hungarian government on this is way overdue. 🙂 Your country deserves better things to be named after it.

[Jelena]

As usual, the real learning is in the comments. 🙂

[Jelena]

I started following YAGNI principle more and it made development so much simpler.

[former_member566586]

I would keep the same key syntax which can be found in a TYPE declaration, as the options for keys can be quite extensive. It would also be easy to remember and copy-paste across places.

TYPES: tt_people TYPE HASHED TABLE OF person WITH UNIQUE KEY name.

SELECT name, age FROM person

  INTO HASHED TABLE @DATA(people) WITH UNIQUE KEY name.

[Michael_Keller]

I support that! Please video record the discussion with non-developers from goverment about Hungarian notation. After a short time, they may suspect that they are in a scene from a new Borat film...

[pokrakam]

I've changed my mind, this is by far my favourite option 🙂

It doesn't interfere with the rest of the syntax, mostly just tacks on to the end. Safe and easy to update existing statements and all possible declaration options can be used without learning any new syntax.

[UweFetzer_se38]

Deal

[m_unger]

Note: Quick assist supports explicit declaration only for local scope (within method / form / FM ). If you try the same in simplistic, non-modularized ordinary program (i.e. declaring the variable in global scope), the option of explicit declaration is not provided.