Performance Issue

[Former Member]

Hi all,

Which is better :

1. loop at itab.

select statement from dbtable

into table itab2

where ...

endloop.

OR

2. select statement

for all entries in itab

into itab2

where ....

Does the performance difference vary depending on the entries in itab??

Advance Thanks

[Former Member]

Hi Adarsh,

Performance wise, For all entries are better, but make sure you use all Keys for the selected Table else it will not give you the result you are expecting.

Thanks,

Anita

[Former Member]

Hi aadarsh,

FOR ALL ENTRIES IN itab WHERE cond

Effect

Only selects the records that meet the logical condition cond when each replacement symbol itab-f is replaced with the value of component f of the internal table itab for at least one line of the table. SELECT ... FOR ALL ENTRIES IN itab WHERE cond returns the union of the solution sets of all SELECT statements that would result if you wrote a separate statement for each line of the internal table replacing the symbol itab-f with the corresponding value of component f in the WHERE condition.Duplicates are discarded from the result set. If the internal table itab does not contain any entries, the system treats the statement as though there were no WHERE cond condition, and selects all records.

Performance

Specify all conditions in the WHERE clause. This means that you do not transport redundant data over the network only to filter it out in your program (using CHECK, for example).

If you regularly use a SELECT statement, you should create an index. In the WHERE clause, you should use the fields of the index, linked using AND, and checking for equality. Fields of an index that occur after a field for which you do not use an equality comparison in the WHERE clause (EQ or 😃 cannot be used to restrict the search.

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.

In a loop, a statement block is executed several times in succession There are four kinds of loops in ABAP:

Unconditional loops using the DO statement.

Conditional loops using the WHILE statement.

Loops through internal tables and extract datasets using the LOOP statement.

Loops through datasets from database tables using the SELECT statement.

second statement is better.

reward points for helpful answers and close the thread if your question is solved.

regards,

venu.

[Former Member]

hi, the better should be comparative.

For the 1st choose.

The count of access DB will be too many, if the itab is large. if the select only select one entry in one time(select single), the cost won't be too large. But you write 'select into table' here, then I think the cost will be large.

For the 2nd choose.

Using FOR ALL ENTRIES looks better, because it looks you only need to run one SELECT in the code. So 1 access DB.

That's true in many occasion.

But if the itab is large, the problem will be same, your SELECT will be split into pieces SELECT by ABAP runtime, and send to DB layer. So the count of access DB will increase .

But you can control the itab size by yourself. like try to SELECT out the result in several SELECT. E.G. itab has 2000 entries, you can split it into 2*1000 table, and

do the FOR ALL ENTRIES twice.

You'b better find a balance of the 2 choose, and don't forget there is many other factor on the optimize, INDEX sap buffer, and etc.

Hope it will be helpful

thanks

[Former Member]

Option 2 is better in all cases as Database will only be accessed once in this case. ( Only make sure that itab has at least one line. If no lines in ITAB all rows from database is selected )

In Option 1 number of database accesses will be equal to the number of lines in ITAB. And this will affect the performance.

Yes performance will depend on no of lines in ITAB.

[Former Member]

hi,

especialy inside loop we should avoid select statement unless it's unavoidable, because it access the database table at every time.

Cheers,

Sasi

[Former Member]

If you do a performance trace for a select for all entries in... I think you'll see that the database is not just hit once. It may be configurable, but in our system, SAP sends a group of selects to DB2. Each DB2 select looks for 5 records (not all of them). So it's faster than loop/endloop but still has overhead. If you can do a join, you should be able to do it in one databse operation.

Rob

[former_member221770]

Hi Aadarsh,

Just to throw my support for "FOR ALL ENTRIES". Have you also considered using an INNER JOIN? If you can use an INNER JOIN here I would recommend you use it as INNER JOINS can be even faster than FOR ALL ENTRIES.

Look up the SAP Help (F1 help in ABAP Editor) under SELECT for more info.

Hope this helps.

Cheers,

Pat.