This is a test message to test the length of the message box.
Login
ABAP Select
Created by Software-Heroes

ABAP - Performance for the SELECT

1178

In this article we will look at a few special cases with the SELECT and examine the performance of these constructs. We'll show you the current alternatives and give you little tips while reading.



We already introduced you to the new SELECT in an older article and what the advantages of working with the inline declaration are. In this article, we want to go into the special cases and what is no longer so popular or high-performance.

 

Structure

For the examples shown, we use table T001, which represents the company codes in the system and is mainly used in the finance area. We have created our own table for this purpose, which contains some of the fields from T001 and its own field.

 

SELECT ... ENDSELECT

Also known as a select loop, this is a SELECT that fetches the data records as a package or individual record and makes them available to the statements between SELECT and ENDSELECT. The database interface is addressed for each package. Let's take a look at an example in which we read data records from the database, prepare them and make them available in an internal table.

DATA:
  lt_company_code TYPE STANDARD TABLE OF z60bs_db_ccodes,
  ls_company_code TYPE zbs_db_ccodes.

SELECT *
  FROM t001
  WHERE land1 = 'DE'
  INTO @DATA(ls_database).

  ls_company_code = CORRESPONDING #( ls_database ).
  ls_company_code-settings = ls_database-bukrs && ` ` && ls_database-waers.

  IF ls_database-bukrs_glob IS NOT INITIAL.
    ls_company_code-bukrs = ls_database-bukrs_glob.
  ENDIF.

  INSERT ls_company_code INTO TABLE lt_company_code.
ENDSELECT.

 

In the S/4 HANA environment, such loops are extremely slow and your software would run longer in the end. For this purpose, in the past, the entire data was read from the database with one access and the individual data records were then adjusted via loop. By now, you should even use code pushdown to move most of the operations to the database. In this case the statement looks like this at the end:

SELECT coalesce( bukrs_glob, bukrs ) AS bukrs, butxt, land1, waers, concat_with_space( bukrs, waers, 1 ) AS settings
  FROM t001
  WHERE land1 = 'DE'
  INTO TABLE @DATA(lt_company_code_new).

 

As you can easily see, there is not much left of the original construct and the coding has been merged into one SELECT statement, since we can do everything with what we needed above. This relieves the load on the ABAP stack and lets our high-performance HANA database work.

Hint: The code pushdown or the creation of a CDS view are only really efficient if a HANA database is used. In systems without a HANA database, these constructs can even be slower than the old way.

 

INSERT FROM SELECT

Would you like to merge data from one table and insert it into a new table? In the past, several steps were necessary for this and the data had to be loaded onto the application server beforehand, which cost time and performance. The following small example in which we want to load the data from the company codes into a separate table.

DATA:
  lt_company_codes TYPE STANDARD TABLE OF zbs_db_ccodes.

SELECT bukrs, butxt, land1, waers
  FROM t001
  WHERE land1 = 'DE'
  INTO CORRESPONDING FIELDS OF TABLE @lt_company_codes.

INSERT zbs_db_ccodes FROM TABLE @lt_company_codes.

 

With the first selection, all company code data is loaded into the internal table in the report, which means a loss of performance for the database interface. When the data is inserted into the table, further performance is then lost because the data has to be loaded back into the database. Here you can save some time and money and load the data directly from one table into the other without having to go through the database interface.

INSERT zbs_db_ccodes FROM (
  SELECT bukrs, butxt, land1, waers
    FROM t001
    WHERE land1 = 'DE'
).

 

The Select, which provides the data, is used directly as the source for the insert. This could also be a join over several tables, for the sake of simplicity we have used a small select.

 

S/4 HANA

In the S/4 HANA environment and when using the HANA database, you should always make sure that you read from the database with high performance and avoid using the wildcard * if possible. Operations that you want to carry out later in the program can be carried out on the database. If you want to reuse such logics and views, you should map them via a core data service.

 

Conclusion

The new SELECT offers you a variety of benefits that also have an impact on your code. Since developers are not paid based on the number of lines they write, such measures can make your coding clearer and easier to maintain.


Included topics:
New ABAPSELECTPerformance
Comments (1)



And further ...

Are you satisfied with the content of the article? We post new content in the ABAP area every Friday and irregularly in all other areas. Take a look at our tools and apps, we provide them free of charge.


ABAP - XCO Strings

Category - ABAP

In this article we look at the XCO class for generating and processing strings for ABAP Cloud and compare it with the classic statements.

11/22/2024

ABAP - XCO Libraries

Category - ABAP

What can you do with the library in ABAP and ABAP Cloud and how do you use the objects the best way? Find out more here.

11/12/2024

ABAP - Type Casting

Category - ABAP

How do you actually get the original type of a class or instance if it is passed in a generic table? In this article we examine the possibilities.

04/16/2024

ABAP - RETURN value

Category - ABAP

After all these years, the “real” return in ABAP has finally arrived. In this article we will show you how it works and what it can do.

02/13/2024

ABAP Deep Dive - FOR (Loops)

Category - ABAP

Let's take a closer look at the FOR loop. How does it work? What do I have to consider and what can I do with it?

04/14/2023