topic Re: READ TABLE with OR condition in Application Development and Automation Discussions

READ TABLE with OR condition

carljschmidt — Fri, 01 Mar 2024 16:10:31 GMT

Hi experts,

I would like to read the most recent entry from an internal table itab based on a condition.
Let's assume the table looks like this and is sorted by the field Timestamp:

Flag	Timestamp (DD-MM-YYYY HH:MM:SS)
A	01-03-2024 12:00:00
C	01-03-2024 11:00:00
A	29-02-2024 15:27:00
B	29-02-2024 14:13:00
A	28-02-2024 10:00:00

I would like to fetch the most recent entry for which Flag is either B or C and I know that a possible solution would be to loop through the table:

LOOP AT itab into wa. IF wa-flag = 'B' or wa-flag = 'C'. EXIT. ENDIF. ENDLOOP. * Continue processing using wa

However, since I am already perfoming this opration in a nested loop, I would like to avoid adding another loop to the logic.

I also found solutions suggesting to use READ TABLE statements for each of the conditions, but they so not seem to be applicable in the situation described above:

Are there any alternatives to looping through the entire table to fetch the most recent entry for which Flag is either B or C? Would SELECT - FROM @itab for example be a valid and recommended approach in this situation?

I'd highly appreciate any thoughts and suggestions in this regard.

Re: READ TABLE with OR condition

RaymondGiuseppi — Fri, 01 Mar 2024 16:14:10 GMT

Did you consider a SELECT

SELECT - FROM @itab

Re: READ TABLE with OR condition

carljschmidt — Fri, 01 Mar 2024 17:03:24 GMT

Hi @RaymondGiuseppi,

thank you very much for your reply!
I haven't yet used SELECT - FROM @itab and after reading

SELECT is from DATABASE and READ is for INTERNAL table.

in the first reply for The read table is the same thing that a Select Single I wasn't sure when to use SELECT - FROM @itab or when READ TABLE shoud be preferred.

But the post is from 2006, so I assume that SELECT - FROM @itab simply did not exist back then.

Re: READ TABLE with OR condition

Sandra_Rossi — Fri, 01 Mar 2024 18:08:56 GMT

However, since I am already perfoming this opration in a nested loop, I would like to avoid adding another loop to the logic.

This is not a good argument: you are confusing the rule to avoid nested SELECT, with nested loops.

Keep your loop.

In case itab is not a small table, one alternative is to have an index on itab and use one read on A, one on B, instead of OR, for a better performance.

I guess SELECT FROM @itab will have an additional overhead but I never tested/never saw a blog post about FROM @itab performance.

Re: READ TABLE with OR condition

carljschmidt — Sat, 02 Mar 2024 00:22:20 GMT

Hi @Sandra_Rossi,

I appreciate your answer, thank you a lot.

However, since I am already perfoming this opration in a nested loop, I would like to avoid adding another loop to the logic.
This is not a good argument: you are confusing the rule to avoid nested SELECT, with nested loops.
Keep your loop.

Are you saying that nested SELECT statements should be avoided, but nested loops are fine?
I assumed that having a cascade of loops is just not a good idea in terms performance due to growing complexity, but please correct me, if I am wrong here.

But I'm happy to use SELECT - FROM @itab for the problem described above. Thanks again!

Re: READ TABLE with OR condition

Sandra_Rossi — Sat, 02 Mar 2024 11:17:28 GMT

Yes, I'm saying that nested SELECT statements should be avoided, but nested loops are fine.

Nested loops have never been a problem if you need them. Doing separate SELECT disables inherently any performance improvement by the database, it's why one big SELECT with JOIN is to be preferred (will be optimized by database), and you can also optimize your ABAP code to query the result.

Are you happy to use SELECT - FROM @itab because YOU THINK it improves the performance, or you really did a test to compare performance?

e.g. example of comparison I did -> Solved: Re: Count the Records in an internal table withou... - SAP Community

Re: READ TABLE with OR condition

christoph_weyd — Sat, 02 Mar 2024 18:11:19 GMT

Nested LOOPs are only acceptable from a performance point of view, if the inner loop's are using a sorted/indexed table or if the tables are very small (less than 20-50 records), otherwise you will have a non-linear runtime behavior. The same non-linear runtime behavior O(n2) will happen if you place READ TABLE ... WITH KEY (without binary search), MODIFY WHERE ..., DELETE WHERE ... into a loop when the inner table is not sorted or indexed. The runtime can be improved if the inner table can be sorted before entering the outer loop. Sorting a standard table within a LOOP will also result in an O(n2) non-linear runtime.

I highly recommend the book ABAP Performance Tuning by Hermann Gahm (SAP Press, ISBN 978-1-59229-555-5) - see chapter 7 Processing of internal tables.

Re: READ TABLE with OR condition

christoph_weyd — Sat, 02 Mar 2024 18:01:38 GMT

If the WHERE condition of the inner loop is hard coded (B or C) but sorted descending by timestamp, then there should be a way to build this table without the Flag = A in the first place. Once you achieve that you only need to read the first entry.

Re: READ TABLE with OR condition

RaymondGiuseppi — Mon, 04 Mar 2024 09:47:31 GMT

You could also perform two READ TABLEs, one for each value, and compare the timestamp to select the best record?

A nested LOOP could also be suitable, if you have an index (primary or secondary) to optimize it.

You need to run optimization tests if the size of your internal table is large.

Re: READ TABLE with OR condition

matt — Mon, 04 Mar 2024 10:01:40 GMT

Nested loops are only a problem if, as the tables increase in size linearly, the time taken increases rather more rapidly. I.e. if with twice as many records it takes more than twice as long.

This can be avoid often by defining the tables as HASHED or SORTED with a good key.