Skip to Content
avatar image
Former Member

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 ?

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 22, 2009 at 11:48 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

  • Oct 23, 2009 at 07: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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 23, 2009 at 07: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

    Add comment
    10|10000 characters needed characters exceeded