select single vs. read table

[Former Member]

Hi,

is there a specific number of loop cycles where you prefer using READ TABLE instead of SELECT SINGLE (inside the LOOP).

For example I have an internal table (itab1) with 200.000 rows and loop this internal table.

What do you suggest?

a) 200.000 SELECT SINGLEs on the database

b) Before LOOP: SELECT all entries from the database into another internal table (itab2) where one row of the internal table (itab1) is equal the key of the database table. And then in the LOOP i perform a READ TABLE on the other internal table (itab2).

Thanks for your help in advance.

[Azeemquadri]

Option b is better. That way all the records are retrieved from the db in an internal table and then further processing is done .

[Former Member]

Is this option always better or only if there is a specific number of loop cycles?

[Azeemquadri]

Getting all the records in one shot is better most of the times.

Even if you have 30 -40 records better to select them in an internal table first and then do the processing.

[Former Member]

Better option is option b and while reading internal table use binary search. Binary search should give better performance over linear search.

[valentin_catalin2]

Hi Thomas,

I hope I understood well your problem. I'm not sure that option b) it's always valid because maybe you will have some issues with the memory (like not having enough and ending in a short-dump).

I this case with big amounts of data I sugest also to take a look at "OPEN CURSOR" and "FETCH" commands to work with smaller amount of data and avoid killing the memory.

BR,

Valentin

[RaymondGiuseppi]

Usually I use option b, but not always

- if the secondary table is buffered, why perform a second buffer

- if the secondary table is too big to be stored, memory problems,

- sometimes I use a main SELECT ... PACKAGE SIZE, and in the SELECT/ENDSELECT I perform the select of the secondary table for the records of the package.

- sometime a JOIN in first select performs better than a or b options

- I often write two versions of the program or add a "switch" parameter in the program (if not in a hurry) and perform some stress performance test (in a quality/sandbox system with enough data) (SAT/SE30 and ST05)

- This kind of question would be better posted in sub-space ABAP Testing and Troubleshooting where there are already many threads/documents/questions, start with FAQ's, intros and memorable discussions in the ABAP Testing and Troubleshooting Space. (former name was "ABAP Performance and Tuning" in the good old time of sdn)

Regards,

Raymond

[Former Member]

Thank you for your helpful answers