10-22-2009 8:30 PM
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 ?
10-23-2009 12:48 AM
It has been a while since I looked at this on a client site running DB2, but there the limit of 10 was set by SAP system parameters and related to issues with the database optimiser failing to use correct indexes if the number was set too high.
I believe there were several SAP Notes on these parameters and how they should be set for different database platforms.
I ended up using the same solution you describe with some reports, as changing the parameters was judged as likely to have a negative effect on other areas of system performance, and not worth the risk for a few reports.
Andrew
10-23-2009 12:48 AM
It has been a while since I looked at this on a client site running DB2, but there the limit of 10 was set by SAP system parameters and related to issues with the database optimiser failing to use correct indexes if the number was set too high.
I believe there were several SAP Notes on these parameters and how they should be set for different database platforms.
I ended up using the same solution you describe with some reports, as changing the parameters was judged as likely to have a negative effect on other areas of system performance, and not worth the risk for a few reports.
Andrew
10-23-2009 10:08 AM
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
10-23-2009 8:29 AM
this depends highly on the database,
there are several system parameters which determine how the FAE is processed.
For example if there is only one FAE-condition then an IN-list can be used. And some databases uses higher
numbers for in-lists.
If there are several conditions, then ORs or even JOINs with temporary datababse tables are used.
Siegfried
10-23-2009 8:41 AM
An important parameter is "/rsdb/max_blocking_factor", check out the very good documentation in tx RZ11. For DB2 the default value is 10.
My guess is that your WHERE-condition is problematic, no primary key, and only an LE condition on the secondary key field VALFR. So by using a range instead of FAE there is only one access to the DB instead of N / 10, and the adverse effect appears only once as a consequence. Maybe you can enhance your WHERE-condition to make better use of the available indexes.
Thomas