Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Former Member
0 Kudos

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 ?

1 ACCEPTED SOLUTION

Former Member
0 Kudos

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

4 REPLIES 4

Former Member
0 Kudos

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

0 Kudos

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

former_member194613
Active Contributor
0 Kudos

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

ThomasZloch
Active Contributor
0 Kudos

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