Maximum number of records for usage of "For all entries"

[Former Member]

Hi,

Is there a limit on maximum number of records to be selected from the database using "For all entries" statement ?

Thanks in advance

[Former Member]

There is no limit.

Beware using FOR ALL ENTRIES : Always check there are some entries on the internal table you are using in the FOR ALL ENTRIES statement otherwise it will read everything from the database table !!

Kind regards

Colin.

[nablan_umar]

So far I am not aware of any limitiation.

[Former Member]

I think there may be a limit, but it is based on the maximum query length for SQL statements. I believe that the "for all entries" clause gets expanded to a SQL "where" clause; each table row is "OR-ed" together. For instance, if your table had 3 rows, with values "X1", "X2" and "X3", the generated SQL would be something like:

 ... where tabfld eq 'X1' or 
                 tabfld eq 'X2' or 
                 tabfld eq 'X3' ..... 

The length of the generated statement is limited to something (1024K sounds vaguely familiar), so if your internal table has a coiuple of million rows you might blow out the statement length limit. For most applications, though, this would never be a problem.

Regards,

D.

[Former Member]

From my experience, the "For all Entries" statement does not have a limit. I have ran into the limit error explained by Dorian when I have a range or select-option with lots of entries.

If your select statement uses a range or select-option, then the SQL statement is generated how Dorian explained. You can see this if you run SQL trace on your program. Using the "For all Entries" clause is the way to avoid this issue.

[nablan_umar]

Jerrod is right. I have tried FOR ALL ENTRIES with over million entries in the internal table. Is still works. No abending. So I don't see there is a limit.

[brad_bohn]

Well, you could have tried it with 100 million entries in your internal table (assuming you could hold that much data in memory) and it still wouldn't fail if your 'RSDB/MAX_BLOCKING_FACTOR' parameter was set correctly. If you don't have the parameter set correctly, you will dump when using excessively large internal tables because the generated SQL statement will be too big.

Most Basis administrators and some experienced developers know about this parameter, so it's usually set in a system. The company I work for has huge data volumes (millions of customers), and they had infrequent problems with this (and they didn't know what was causing it), until I came here and set the value. For more info, you can look up OSS notes on the subject.

Brad

[Former Member]

Hi,

Actually when you use "For All Entries", SAP converts it to multiple select statements with 5 OR statments each. You can see each of the select statment if you switch on performance trace while the program is executing.

Raj

[Former Member]

There is no set limit, but you have to ask yourself, at what number do I see a huge performance degradation? I recommend that you instead append the entries into a range if you can for the most key fields, and do multiple selects with ranges not exceeding 200 to 500 entries in each.

So for example, if you had 5000 entries in your table, and you used the range. you'd append 500 entries into the range, do a select, and repeat, for a total of 10 times.

Then after the fact filter the results down based on other specifications.

We ran tests on this method vs. "for all entries" and this method was significantly faster.

Hope it helps.

Gareth de Bruyn

[Former Member]

what is the maximum number for entries in the IN-Operation:

In note 635318 is written .max 1000 entries ...

Is this right ?

[ChristianFi]

Dont have access to the note right now but, as far as I know the limit is determined by the length of the whole sql statement and is about 8k(?)

So you cannot just consider the number of entries of the in statement - you will take into account the length of the fields in your where conditions and the number of entries fot all ranges.

Christian

Message was edited by: Christian Finkbeiner

(That was referring to the in operator)

[Former Member]

Hi All,

Thank you very much for your inputs

[Former Member]

Hi,

Just wanted to say that to know the exact size limitations for Open SQL statements, take a look at SAP note number 635318.

Regards

[Former Member]

There is only limit for entries if you are using RANGES statement and generate your own selection criteria like SELECT-OPTIONS.

[Former Member]

There is a UNDOCUMENTED(??) behaviousr

FOR ALL ENTRIES does ahidden SELECT DISTINCT & drops duplicates.

http://web.mit.edu/fss/dev/abap_review_check_list.htm

3 pitfalls

"FOR ALL ENTRIES IN..." (outer join) are very fast but keep in the mind the special features and 3 pitfalls of using it.

(a) Duplicates are removed from the answer set as if you had specified "SELECT DISTINCT"... So unless you intend for duplicates to be deleted include the unique key of the detail line items in your select statement. In the data dictionary (SE11) the fields belonging to the unique key are marked with an "X" in the key column.

-

---

^^!!!!

(b) If the "one" table (the table that appears in the clause FOR ALL ENTRIES IN) is empty, all rows in the "many" table (the table that appears in the SELECT INTO clause ) are selected. Therefore make sure you check that the "one" table has rows before issuing a select with the "FOR ALL ENTRIES IN..." clause.

(c) If the 'one' table (the table that appears in the clause FOR ALL ENTRIES IN) is very large there is performance degradation Steven Buttiglieri created sample code to illustrate this.

[ChristianFi]

Yes but it is not undocument but described very well in the online help to the select (where) :

Variant 2

... 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.

Christian

[Former Member]

Firstly, there is a limitation because everyones system will have some kind of setting which will limit either the size of SQL statement that can be used or the number of Union'ed SELECT statements that can be joined.

If you're on a DB2/6 database then you might want to take a look at OSS note 833224.