Difference between select..end select and for all entries

[Former Member]

Hi experts?

Pls tell me the difference between the select.....end select and for all entries ?

Why select...end select takes more time to execute compare to for all entries .

Thanks.

[Former Member]

Hi

Select..Endselect will take more time because the Database table is locked for most of the time and everytime the data record has to be fetched from the database table in a loop.

so better to use INTO table ITAb ..to avaoid Endselect..

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

Reward points for useful Answers

Regards

Anji

[Former Member]

Hi,

To gain a better understanding go through this

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

Regards,

Suruchi

[Former Member]

HI,

The for all entries statement will be less performant if you have a lot of records ( and don't forget, you have in most cases to delete duplicates, so ... )

In my opinion, it really depends.

If you have doubt in your report, just do a test ...

Anyway, something sure is that obviously there is no better statement between "for all entries" and "Inner Join". If there was, everybody would know it.

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

Specifying Two or More Database Tables as an Inner Join

In a relational database, you normally need to read data simultaneously from more than one database table into an application program. You can read from more than one table in a single SELECT statement, such that the data in the tables all has to meet the same conditions, using the following join expression:

SELECT...

...

FROM <tab> [INNER] JOIN <dbtab> [AS <alias>] ON <cond> <options>

...

where <dbtab> is a single database table and <tab> is either a table or another join expression. The database tables can be specified statically or dynamically as described above. You may also use aliases. You can enclose each join expression in parentheses. The INNER addition is optional.

A join expression links each line of <tab> with the lines in <dbtab> that meet the condition <cond>. This means that there is always one or more lines from the right-hand table that is linked to each line from the left-hand table by the join. If <dbtab> does not contain any lines that meet the condition <cond>, the line from <tab> is not included in the selection.

Inner join is basically the intersection of two sets based on certain condition.

You can read data from more than one database table in a single SELECT statement by using inner or left outer joins in the FROM clause.

The disadvantage of using joins is that redundant data is read from the hierarchically-superior table if there is a 1:N relationship between the outer and inner tables. This can considerably increase the amount of data transferred from the database to the application server. Therefore, when you program a join, you should ensure that the SELECT clause contains a list of only the columns that you really need. Furthermore, joins bypass the table buffer and read directly from the database. For this reason, you should use an ABAP Dictionary view instead of a join if you only want to read the data.

The runtime of a join statement is heavily dependent on the database optimizer, especially when it contains more than two database tables. However, joins are nearly always quicker than using nested SELECT statements.

For all entries

The WHERE clause of the SELECT statement has a special variant that allows you to derive conditions from the lines and columns of an internal table:

SELECT ... FOR ALL ENTRIES IN <itab> WHERE <cond> ...

<cond> may be formulated as described above. If you specify a field of the internal table <itab> as an operand in a condition, you address all lines of the internal table. The comparison is then performed for each line of the internal table. For each line, the system selects the lines from the database table that satisfy the condition. The result set of the SELECT statement is the union of the individual selections for each line of the internal table. Duplicate lines are automatically eliminated from the result set. If <itab> is empty, the addition FOR ALL ENTRIES is disregarded, and all entries are read.

The internal table <itab> must have a structured line type, and each field that occurs in the condition <cond> must be compatible with the column of the database with which it is compared. Do not use the operators LIKE, BETWEEN, and IN in comparisons using internal table fields. You may not use the ORDER BY clause in the same SELECT statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

Regards

Sudheer

[varma_narayana]

Hi,

Select .. Endselect retrieves row by row from DB which gives a Poor performance.

where as

FOR ALL ENTRIES is used to fetch the data into an Internal table from a DB table

by comparing the records of another itab. uses array fetch. better performance.

Regards.

[Former Member]

Murugan

Select ... endselect.. Means nothing but looping the database tabel .

Each loop need to connect the database:for example if we have 100000 records connection will be 100000 times.That is the reason it will take more time.

For all entries ::At a strech(one time) data can extra from database no need to connect database for all the time

Don't forget to reward if useful....

[Former Member]

Hi,

<b>FOR ALL ENTRIES vs DB2 JOIN</b>

1. duplicate rows are automatically removed

2. if the itab used in the clause is empty , all the rows in the source table will be selected .

3. performance degradation when using the clause on big tables.

In this post I'd like to shed some light on the third issue. Specifically i'll discuss the use of the "for all entries" clause as a means to join tables in the abap code instead of in db2.

Say for example you have the following abap code:

Select * from mara

For all entries in itab

Where matnr = itab-matnr.

If the actual source of the material list (represented here by itab) is actually another database table, like:

select matnr from mseg

into corresponding fields of table itab

where ….

Then you could have used one sql statement that joins both tables.

Select t1.*

From mara t1, mseg t2

Where t1.matnr = t2.matnr

And T2…..

So what are the drawbacks of using the "for all entires" instead of a join ?

At run time , in order to fulfill the "for all entries " request, the abap engine will generate several sql statements (for detailed information on this refer to note 48230). Regardless of which method the engine uses (union all, "or" or "in" predicates) If the itab is bigger then a few records, the abap engine will break the itab into parts, and rerun an sql statement several times in a loop. This rerun of the same sql statement , each time with different host values, is a source of resource waste because it may lead to re-reading of data pages.

returing to the above example , lets say that our itab contains 500 records and that the abap engine will be forced to run the following sql statement 50 times with a list of 10 values each time.

Select * from mara

Where matnr in ( ...)

Db2 will be able to perform this sql statement cheaply all 50 times, using one of sap standard indexes that contain the matnr column. But in actuality, if you consider the wider picture (all 50 executions of the statement), you will see that some of the data pages, especially the root and middle-tire index pages have been re-read each execution.

Even though db2 has mechanisms like buffer pools and sequential detection to try to minimize the i/o cost of such cases, those mechanisms can only minimize the actual i/o operations , not the cpu cost of re-reading them once they are in memory. Had you coded the join, db2 would have known that you actually need 500 rows from mara, it would have been able to use other access methods, and potentially consume less getpages i/o and cpu.

In other words , when you use the "for all entries " clause instead of coding a join , you are depriving the database of important information needed to select the best access path for your application. Moreover, you are depriving your DBA of the same vital information. When the DBA monitors & tunes the system, he (or she) is less likely to recognize this kind of resource waste. The DBA will see a simple statement that uses an index , he is less likely to realize that this statement is executed in a loop unnecessarily.

In conclusion I suggest to "think twice" before using the "for all entries" clause and to evaluate the use of database views as a means to:

a. simplify sql

b. simplify abap code

c. get around open sql limitations.

Regards

Sudheer

[Former Member]

hi,

select and end select is like a loop to select the data.

for all enties is used when you have selectes data in one table nad you want to select in another table as compare to previous table.

*/ Retrieve Purchasing Document Number , Creation date

• Purchasing Organization , Purchasing group from EKKO

SELECT ekorg ekgrp ebeln aedat

INTO TABLE gt_ekko FROM ekko

WHERE aedat BETWEEN gv_year AND gv_date

AND ekorg IN s_ekorg

AND ekorg NE 1000.

IF sy-subrc NE 0.

MESSAGE s003.

ENDIF.

*/ Retrieve Purchasing Document Number , Item Number of Purchasing

• Document from ekpo for all entries in gt_ekko

IF gt_ekko[] IS NOT INITIAL.

SELECT ebeln ebelp INTO TABLE gt_ekpo FROM ekpo

FOR ALL ENTRIES IN gt_ekko

WHERE ebeln EQ gt_ekko-ebeln.

IF sy-subrc NE 0.

MESSAGE s007.

ENDIF.

ENDIF.

regads,

Ruchika

[rodrigo_paisante3]

Hi,

If you need to do something with a field, use select...endselect. If you need to check or test, or to append/include other value.

SELECT id name FROM scustom INTO CORRESPONDING FIELDS OF ti_scustom.

ti_scustom-date = sy-datum.

IF...

APPEND ti_scustom.

ENDIF.

ENDSELECT.