topic Re: select single vs. read table in Application Development and Automation Discussions

select single vs. read table

Former Member — Tue, 02 Oct 2012 09:25:17 GMT

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.

Re: select single vs. read table

Azeemquadri — Tue, 02 Oct 2012 09:32:40 GMT

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

Re: select single vs. read table

Former Member — Tue, 02 Oct 2012 09:38:31 GMT

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

Re: select single vs. read table

Former Member — Tue, 02 Oct 2012 09:38:33 GMT

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

Re: select single vs. read table

Azeemquadri — Tue, 02 Oct 2012 09:43:14 GMT

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.

Re: select single vs. read table

valentin_catalin2 — Tue, 02 Oct 2012 10:26:12 GMT

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

Re: select single vs. read table

RaymondGiuseppi — Tue, 02 Oct 2012 11:27:30 GMT

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

Re: select single vs. read table

Former Member — Tue, 02 Oct 2012 13:06:47 GMT

Thank you for your helpful answers