SQL processing in ABAP

[Former Member]

I'm learning ABAP from the book

Introduction to ABAP/4 Programming for SAP

by Gareth M. de Bruyn and Robert W. Lyfareff

and read now that SQL SELECTs run like loops. I worked with SQL before and didn't have that idea in mind, I wonder if this is something peculiar to ABAP or indeed, in common RDBMS, such as Oracle, it always works like that.

The book says that the

	SELECT * FROM tab [WHERE condition].	
	...	
	ENDSELECT.

functions like a loop - I always thought that the whole result was buffered at once (or at least the first ***** rows for immediate use). Looping for me is a functionality related to Cursors.

Excerpt from the book:

SELECT is a looping command, as you can see by the presence of the ENDSELECT command. When a SELECT is issued against a table, it returns all rows of data contained in that table, one at a time. So the first row returned is available for processing during the first pass of the loop. The second row is available during the second pass of the loop, and so on, until all rows have been read from the table.

Please leave your comments.

Thanks

Edited by: Avraham Kahana on Jun 29, 2008 10:14 AM

[Former Member]

hi avraham,

Yes the statement works like a loop statement.

SELECT * FROM DATABASE TABLE INTO WORK AREA.

append WORKAREA TO INTERNAL TABLE.

ENDSELECT.

*PROCESSING LOGIC CAN BE WRITTEN INSIDETHE LOOP.

Now i always try to avoid using this statement and instead i use this statement for mass transfer of data to internal table

SELECT * FROM DATABASE TABLE INTO CORRESPONDING FIELDS OF TABLE (INTERNAL TABLE NAME)

WHERE CONDITION.

THIS IS BETTER WHEN YOU LOOK IN TERMS OF PERFORMACE WISE.

regards,

Varghese

Edited by: varghese oommen on Jun 29, 2008 4:31 PM

[Clemenss]

Hi Avraham,

you are right SELECT ... ENDSELECT defines a loop. But the system will fetch the data in blocks of (I believe) 32k size. That means with small tables and few fields SELECT ... ENDSELECT can be preferred over SELECT ... INTO TABLE because no further loop and less memory is needed and the whole content will be fetched with one database access.

Regards,

Clemens

[Former Member]

Hi Avraham,

Select-Endselect just behaves like a looping statement, which fetches the data from database table record by record.

But, from the point of performance, this is not a good way to select data from the database table using Select-Endselect.

Instead of going for Select-Endselect and writing the data within the loop, we select data from database into an internal table and then write the data.

The code snippet goes as follows and it is better in performance also:

Select * 
  from <dbtab>
  into table itab
 where <condition>

Then we go for the display of the records.

Hope this helps you.

Any queries, get back to me.

Regards,

Chandra Sekhar