Select ....FOR ALL ENTRIES.... performance tuning
I have the following SELECT statement:
SELECT recn, recnroot, ippers
INTO CORRESPONDING FIELDS OF TABLE <ITAB1>
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
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 ?
Hermann Gahm replied
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.
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.