Why "INTO CORRESPONDING" is much better than its reputation

[ThomasZloch]

Purpose

 

In the SCN ABAP discussions I often read that using the “INTO CORRESPONDING” construct in ABAP SQL statements would degrade performance. With the example described in this document I would like to prove this “urban legend” wrong.

Please note that screenshots and results are based on an Oracle DB and might look different for other databases.

 

Example case

 

In this example, I want to read three columns from table DD03L for a specific TABNAME = “BUS_DI” (I am choosing these objects because they should be available in any ABAP stack environment, so you can hopefully run the attached little program).

EDIT: following subsequent comments by proven experts I would like to stress that reading DD03L in actual requirements is not recommended, and that the messages here apply to non-buffered tables.

 

The proper way is to declare a target structure with just the required columns.

 

TYPES: BEGIN OF ty_dd03l_required_columns,
tabname   LIKE dd03l-tabname,
fieldname LIKE dd03l-fieldname,
reftable  LIKE dd03l-reftable,

       END OF ty_dd03l_required_columns.
DATA: t_dd03l_req TYPE STANDARD TABLE OF ty_dd03l_required_columns.

 

Often times though, the target structure is simply defined based on the table definition.

 

DATA: t_dd03l_full TYPE STANDARD TABLE OF dd03l.

CONSTANTS: c_tabname LIKE dd03l-tabname VALUE 'BUS_DI'.

 

Now let us compare six different ways to code the ABAP SQL statement for the selection.

 

(1) The standard and of course correct way is to list the required columns.

 

SELECT tabname
fieldname
reftable
       INTO TABLE t_dd03l_req
       FROM dd03l
       WHERE tabname = c_tabname.

 

(2) Another way to do it is using “*” in conjunction with INTO CORRESPONDING. This is where the “urban legend” has it that this would affect performance.

 

SELECT * INTO CORRESPONDING FIELDS OF TABLE t_dd03l_req
       FROM dd03l
       WHERE tabname = c_tabname.

 

(3) Just for comparison, let us also include this combination of field list and INTO CORRESPONDING.

 

SELECT tabname
fieldname
reftable
       INTO CORRESPONDING FIELDS OF TABLE t_dd03l_req
       FROM dd03l
       WHERE tabname = c_tabname.

 

(4) Now switching to the full target structure, a combination of field list and INTO CORRESPONDING will likely not affect the run time, however more memory will be occupied this way.

 

SELECT tabname
fieldname
reftable
       INTO CORRESPONDING FIELDS OF TABLE t_dd03l_full
       FROM dd03l
       WHERE tabname = c_tabname.

 

(5) The combination of “*”, INTO CORRESPONDING and the full target structure must be avoided. This looks very similar to number 2 and probably helped leading to the bad image of this construct.


SELECT * INTO CORRESPONDING FIELDS OF TABLE t_dd03l_full
       FROM dd03l
       WHERE tabname = c_tabname.

 

(6) Of course there might be cases where you really need (almost) all of the columns of a table.

 

SELECT * INTO TABLE t_dd03l_full
       FROM dd03l
       WHERE tabname = c_tabname.

 

The attached test program will run all these statements three times, activating the SQL Trace only for the last loop pass (in order to avoid buffering effects on the measurement) and calling the SQL Trace results for immediate investigation. You should create it in your sandbox as temporary object with name Z_CORRESPONDING (or any name, for that matter).

 

After running the program, you will arrive at the SQL Trace “Detailed Trace List”. This is also what you see when you start transaction ST05 manually. From the detailed list, please go straight to the “Combined Table Accesses” overview.



Let us focus on column „Access Time“.



You can see that our first four SQL statements took roundabout the same time to complete, whereas the last two took more than three times as much time. You might be surprised that statements 1 and 2 show no significant difference.

 

In order to understand why, please return from the summary to the detailed list. Now put the cursor on the row with the second REOPEN operation and click the “Explain” button.



This will show the SQL statement as it arrives on the database.



As you can see, despite the „*“ in our ABAP SQL statement there is now a list of the columns as they were declared for the target structure. Obviously the interface between ABAP and the underlying database (a.k.a. “DBI”) is smart enough to pass only the required columns, so that the statements 1 and 2 are identical.

 

Conclusion

 

The standard way to implement database selections from ABAP is to provide the field list together with “INTO TABLE”. However, do not hesitate to also use “* INTO CORRESPONDING FIELDS OF TABLE” when the context justifies it, just because it allegedly affects performance. You have seen that it doesn’t.

Quite to the contrary, INTO CORRESPONDING allows for some dynamic programming techniques. For example, think of an ALV list using CL_SALV_TABLE with the field catalogue defined as a DDIC structure. If you later need to add fields, in most cases it is sufficient to just add them to the DDIC structure, leaving the actual program code unchanged.
You can even use it with JOINs, however then you must be aware of columns with identical names but different values (e.g. EKKO-AEDAT and EKPO-AEDAT), possibly leading to unwanted results. As per my observation, the values from the table appearing later in the JOIN statement will “win” in this case.

In any case, I hope I have convinced you that INTO CORRESPONDING in select statements is not such a bad thing at all. Just make sure that your target structure contains the required columns only.

Please let me know in case you arrive at different results when you run the program in your sandbox.