SELECT SINGLE FROM @itab vs. READ TABLE itab

Question

We just had a discussion with some colleagues about the Title. We currently don't have the opportunity to test it on our Systems, cause the Dev-Systems are being patched right now.

Can someone answer the Question, which of the following is faster, even though READ TABLE is much more used:

SELECT SINGLE col
FROM @itab
INTO @DATA(dat).

READ TABLE itab ASSIGNING FIELD-SYMBOL(<dat>) TRANSPORTING col.

SELECT SINGLE *
FROM @itab
INTO @DATA(dat).

READ TABLE itab ASSIGNING FIELD-SYMBOL(<dat>).

I know SELECT is way more flexible, cause you can use pretty much the entire OpenSQL-Syntax, but just for these Examples, which will be faster? Does it change if we add a Condition (WHERE or WITH KEY/WITH TABLE KEY)? Does it change with the Size of itab?

Answer 1

For a theoretical question here's a theoretical answer:

The documentation states:

The SELECT statement handles the internal table of the application server like a database table on the database. [...] There are two different cases here:

• The data in the internal table is not required on the database. In this case, the data of the internal table is accessed on the application server and the table is handled like a table in the table buffer.
• The data in the internal table is required on the database. In this case, the data must be passed to temporary tables in the database before the query is actually executed.

Table buffering takes place in the shared memory of each application server. The table buffer consists of a central management structure, an alphabetical directory of tables, and a data area. The individual areas of a generically buffered table or database view are managed as individual fully buffered tables. The buffered data is saved in the data area as internal tables.

So as long as your SELECT FROM @itab statement does not contain anything that requires the query to be pushed down to the database, it will perform some algorithm on an internal table. Likewise READ TABLE will perform some algorithm on an internal table. So conceptually - as long as equivalent operations are performed by both statements - there should be no difference between the two.

In reality however I would assume that READ TABLE performs some short circuits that SELECT does not and vice versa, so whether one is faster or not can likely differ on a case by case basis.

That said, I would personally prefer SELECT over READ TABLE as it is well known and analogous to selection from database tables, also it is way more flexible and powerful.