Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

Select ....FOR ALL ENTRIES.... performance tuning

I have the following SELECT statement:

SELECT recn, recnroot, ippers

INTO CORRESPONDING FIELDS OF TABLE <ITAB1>

FROM CCIHT_IP

FOR ALL ENTRIES IN <ITAB2>

WHERE ippers = <ITAB2>-ippers

AND valfr LE sy-datum

AND valto GE sy-datum

AND iptype = 'INJ'.

Did a trace, and the SQL executed is:

SELECT recn, recroot, ippers

FROM CCIHT_IP

WHERE mandt= ?

AND ippers IN (?1, ..., ?10)

AND valfr <= ?

AND valto >= ?

AND iptype = ?

FOR FETCH ONLY WITH UR

This is very slow.

To speed it up, I programmatically break up the SQL using a range table:

i.e.: WHERE ...

AND IPPERS IN <RANGE TABLE>

...

With the range table containing 1500 entries which is near the limit for IN statement. This is much faster.

The question is why with the FOR ALL ENTRIES the IN statement contains only 10 values and not the maximum allowed, is this a database config issue ?

Former Member
replied

Hi,

as Thomas said for this case rsdb/max_in_blocking_factor is the parameter in question.

And yes, Andrew, you are right, FAE parameters should not be changed system wide

since the delivered default values are those values that turned out to be the best values

in systemwide tests.

However you can increase the value on statement level with a hint. So you can have both

the FAE and a non default blocking for a specific statement.

example:

SELECT recn, recnroot, ippers
INTO CORRESPONDING FIELDS OF TABLE <ITAB1>
FROM CCIHT_IP
FOR ALL ENTRIES IN <ITAB2>
WHERE ippers = <ITAB2>-ippers
AND valfr LE sy-datum
AND valto GE sy-datum
AND iptype = 'INJ'
%_hints db2 '&max_blocking_factor 500&&max_in_blocking_factor 500&u2019.

Use with care.

Kind regards,

Hermann

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question