Regarding Table Buffering..

[Former Member]

Hi,

I have a requirement to get the data from SKAT table i.e - Text field (TXT20) for the corresponding GL Account.

My issue is I have to get the TXT20 for the corresponding GL accounts selected into an internal table.

But as SKAT table is buffered it has performance issue..

I have to use..select single...

Please let me know how to do that..

To get TXT20 from SKAT for the corresponding GL Accounts in the internal table.

Reagrds,

Priyanka.

[Former Member]

Hi,

Use below code

PARAMETERS: 
COA LIKE T004-KTOPL DEFAULT 'CAEK',

SKA1 = SPACE.

SELECT * FROM SKA1 WHERE KTOPL = COA.

SELECT SINGLE * FROM SKAT WHERE SPRAS = 'E' AND
  KTOPL = COA AND
  SAKNR = SKA1-SAKNR.

IF SY-SUBRC = 0.
  ITAB-TXT20 = SKAT-TXT20.
ENDIF.

[Former Member]

>

> But as SKAT table is buffered it has performance issue.

Buffering is done to improve performance.

Rob

[Former Member]

Hi Rob,

But my Code Inspector is throwing warning saying..

Select single record ad it is buffered table.

Please let me know how to remove that warning.

Priyanka..

[Former Member]

It gives no errors or even warnings when I try it. Are you using the full primary key in the SELECT?

Rob

[Former Member]

Hi Rob,

I am taking the HKONT values from bseg into an internal table.

Select spras ktopl saknr txt20 into table it_skat

for all entries of it_bseg

where spras = 'E'

and ktopl = 'PCOA'

and saknr = it_bseg-hkont.

This is my query...

Priyanka...

[Former Member]

I guess if you are not using SELECT SINGLE, it will give this warning. I'd ignore it.

Rob

[Former Member]

Hi Rob,

How can I do that using select single?

Also can u please let me know how to reduce the Data Base Performance.

Regards,

Priyanka.

[Former Member]

This gives no warnings:

LOOP AT it_bseg.
  SELECT SINGLE spras ktopl saknr txt20
    FROM skat
    INTO it_skat
    WHERE spras = 'E'
       AND ktopl = 'PCOA'
       AND  saknr = it_bseg-hkont.
  IF sy-subrc = 0.
    APPEND it_skat.
  ENDIF.
ENDLOOP.

But performance will be worse if you do it this way. That's why I suggested ignoring the warning.

Rob

This is more a performance question, so I'm moving it there.

Edited by: Rob Burbank on May 21, 2009 10:30 AM

[former_member192616]

Hi Rob,

Hi Priyanka,

in my system SKAT is single record buffered.

The ONLY way to read from single record buffer up to 7.0 EhP1 is

to use a SELECT SINGLE and the fully specified primary key (MANDT, SPRAS, KTOPL, SAKNR).

So actually this:

 
LOOP AT it_bseg.
  SELECT SINGLE spras ktopl saknr txt20
    FROM skat
    INTO it_skat
    WHERE spras = 'E'
       AND ktopl = 'PCOA'
       AND  saknr = it_bseg-hkont.
  IF sy-subrc = 0.
    APPEND it_skat.
  ENDIF.
ENDLOOP.

should be faster than this (10 times or even more):

 
Select spras ktopl saknr txt20 into table it_skat
for all entries of it_bseg
where spras = 'E'
and ktopl = 'PCOA'
and saknr = it_bseg-hkont.

because the FAE (FOR ALL ENTRIES) does bypass the table buffer.

Of course it will be only faster when the buffer is filled. There fore run

it several times if you want to compare it.

Try it and post the result.

You can use ST05 SQL Trace and ST05 Table Buffer Trace to see

which OPEN SQL statement uses the buffer and which statement

bypasses it.

Kind regards,

Hermann

[Former Member]

>

> ...
> should be faster than this (10 times or even more):

Hmmmm..... I think that's right Hermann. Mea Culpa

But wouldn't bseg_int have to be sorted in hkont order for this to be effective?

And I don't think that is even right - sorting shouldn't make a difference.

Rob

Edited by: Rob Burbank on May 21, 2009 12:37 PM

[Former Member]

OK - enough of this being wrong all the time. I did a quick test to find out.

The SELECT SINGLE/APPEND/ENDSELECT ran about 3 times faster in a development environment than FAE whether or not IT_BSEG was sorted.

However, in a test environment, the FAE was slightly faster!.

But I also tried another method - the one which I normally use in this sort of situation. Instead of doing the SELECT SINGLE/APPEND/ENDSELECT for each entry in IT_BSEG, I first used a binary search on IT_SKAT to see if it had already been retrieved. If it had, I didn't bother SELECTing again. This ran about 4 times faster than the other two methods (and would get rid of the warning from the code inspector).

Rob

[former_member192616]

Hi Rob,

>

> However, in a test environment, the FAE was slightly faster!.
> Rob

that is quite interesting.

Have you double checked with ST05 that the SELECT SINGLE uses the

table buffer? In ST05 you should have yellow lines for FAE and

blue lines for SELECT single, if you switch on ST05 SQL Trace and ST05

Table Buffer Trace at the same time. The SELECT SINGLE should be faster...

if it uses the SAP table buffer (blue line). Could it be that your SELECT SINGLE

couldn't use the SAP table buffer e.g. because the SKAT table was not in it?

If you could provide the details showing the the faster DB Access compared to the

SAP table buffer acces I would be keen to get more details from you.

>

> But I also tried another method - the one which I normally use in this sort of situation. Instead of doing the SELECT SINGLE/APPEND/ENDSELECT for each entry in IT_BSEG, I first used a binary search on IT_SKAT to see if it had already been retrieved. If it had, I didn't bother SELECTing again. This ran about 4 times faster than the other two methods (and would get rid of the warning from the code inspector).
> Rob

yes.

Talking about buffered Access (efficient key accesses, no linear serach):

- internal tables are faster than the SAP Table buffer

- SAP table buffer is faster than buffered DB (cache)

- buffered DB (cache) is faster then unbuffered DB

that is at least the theory and it matches my experience so far.

That's why I'm so interested in your faster FAE... .

Thank you already in advance.

Note:

Buffering in internal tables (in the internal mode) is the fastest. But it is as well

the buffer mode which needs the most memory. Since in the internal mode the

memory for table sktat is allocated per USER (executing the Program). For the SAP table buffer

the memory for the table skat is allocated once in apllication server level (not per user).

Kind regards,

Hermann

[former_member192616]

Hi Rob,

i just did it myself here.

For the attached trace you can see:

- REOPEN / FETCH pairs for DB Access (403 micro seconds, 207 micro seconds and 227 micro seconds...)

- the READ SI for the SAP Table Buffer acces. (30 micro seconds, 18 micro seconds, 21 micro seconds...)

Duration  Obj. name  Op.     Recs. RC     Statement

        8 SKAT       REOPEN             0 SELECT WHERE "MANDT" = '100' AND "SPRAS" = '1' AND
      395 SKAT       FETCH       5   1403
       13 SKAT       READ SI     1      0 P 36 1001C0010010010100
        5 SKAT       READ SI     1      0 P 36 1001C0010010010200
        4 SKAT       READ SI     1      0 P 36 1001C0010010110000
        4 SKAT       READ SI     1      0 P 36 1001C0010010110001
        4 SKAT       READ SI     1      0 P 36 1001C0010010110002
        7 SKAT       REOPEN             0 SELECT WHERE "MANDT" = '100' AND "SPRAS" = '1' AND
      200 SKAT       FETCH       5   1403
        4 SKAT       READ SI     1      0 P 36 1001C0010010010100
        3 SKAT       READ SI     1      0 P 36 1001C0010010010200
        4 SKAT       READ SI     1      0 P 36 1001C0010010110000
        4 SKAT       READ SI     1      0 P 36 1001C0010010110001
        3 SKAT       READ SI     1      0 P 36 1001C0010010110002
        6 SKAT       REOPEN             0 SELECT WHERE "MANDT" = '100' AND "SPRAS" = '1' AND
      222 SKAT       FETCH       5   1403
        5 SKAT       READ SI     1      0 P 36 1001C0010010010100
        4 SKAT       READ SI     1      0 P 36 1001C0010010010200
        4 SKAT       READ SI     1      0 P 36 1001C0010010110000
        4 SKAT       READ SI     1      0 P 36 1001C0010010110001
        4 SKAT       READ SI     1      0 P 36 1001C0010010110002
...

ST12 result:

Form DATABASE_ACCESS = 1517 microseconds. (for 5 times FAE)

Form DATABASE_ACCESS = 225 microseconds. (for 5 times SELECT SINGLE)

__ Call                                              No.   Gross =    Net Gross (%) Net (%)

   Form DATABASE_ACCESS                                5  1,517  = 1,517      13.0    13.0
   Select SKAT                                         5  1,453  = 1,453      12.5    12.5
   ...
   Form SAP_TABLE_BUFFER_ACCESS                        5    225  =   225       1.9     1.9
   Select Single SKAT                                 25    152  =   152       1.3     1.3

You should get similar results.

KInd regards,

Hermann

[Former Member]

>

> Have you double checked with ST05 that the SELECT SINGLE uses the 
> table buffer? In ST05 you should have yellow lines for FAE and
> blue lines for SELECT single, if you switch on ST05 SQL Trace and ST05
> Table Buffer Trace at the same time. The SELECT SINGLE should be faster...
> if it uses the SAP table buffer (blue line). Could it be that your SELECT SINGLE
> couldn't use the SAP table buffer e.g. because the SKAT table was not in it?

Well, the FAE entries are in bright yellow, but the SELECT SINGLE entries are in dull yellow. SKAT is single record buffered and we are in a DB2 environment.

Rob

[Former Member]

But I also tried another method - the one which I normally use in this sort of situation. Instead of doing the SELECT SINGLE/APPEND/ENDSELECT for each entry in IT_BSEG, I first used a binary search on IT_SKAT to see if it had already been retrieved. If it had, I didn't bother SELECTing again. This ran about 4 times faster than the other two methods (and would get rid of the warning from the code inspector).

Rob

Can u please explain?

Priyanka.

[Former Member]

>

> Can u  please explain?

Like this:

  loop at it_bseg.
    read table it_skat with key
        spras = 'EN'
        ktopl = p_bukrs
        saknr = it_bseg-hkont
        binary search.
    if sy-subrc NE 0.
      select single spras ktopl saknr txt20
        from skat
        into it_skat
        where spras = 'E'
           and ktopl = p_bukrs
           and saknr = it_bseg-hkont.
      if sy-subrc = 0.
        append it_skat.
        sort it_skat by saknr.
      endif.
    endif.
  endloop.

This uses standard tables and binary searches. You may want to use sorted or hashed tables.

Rob

[former_member194613]

for single record buffered tables you MUST use

LOOP at itab
   SELECT SINGLE
                 FROM dbtab
                 WHERE ... fully specified key with equal conditions
ENDLOOP.

It will be much faster than any other access. But when yoz measure, you should not measure

how the buffer is filled!

that is also a reason, why AVOID SELECT SINGLE IN A LOOP is nonsense!

it always depends.

Siegfried

[former_member194613]

> the FAE entries are in bright yellow, but the SELECT SINGLE entries are in dull yellow

meaning that they appear in the SQL trace, which is actually the database trace.

The SELECT SINGLE should disappear from the db-trace and move to the buffer trace (must be switched on), then they are blue (dull and bright :).

I did not look up the table, but here SELECT SINGLE means the primary key must be specified,

not just any key with SELECT SINGLE, otherwise single-record buffer will not be accessed.

Siegfried

[Former Member]

Well, I'd never used the bufrer trace, so I'm learning something today! But the FAE DB trace remained in bright yellow; nothing for the SELECT SINGLE DB trace. Both buffer traces were in bright blue.

Full primary key specified in both cases.

Rob

[former_member192616]

Hi Rob,

all yellow lines = statements are sent to the DB.

all blue lines = statements are not sent to the DB but executed in the SAP table buffer.

you should find FAE lines = yello because they can not use the table buffer and the select single lines in blue because they should use the table buffer (if full key is specified and SINGLE is in the statement and the record searched for is in the buffer.)

You can as well put each Statement in a form and run SE30. If nothing else but the statement itself is in the form you can use the gross time of the form for performance comparison like i did.

select single on buffered entries should be much faster.

Kind regards,

Hermann

[Former Member]

I wonder if it has something to do with FAE eliminating duplicates. My it_bseg has 10,000 entries, but it_skat only 68 for FAE, but 10,000 for SELECT SINGLE.

Rob

[former_member192616]

Hi Rob,

could you post yellow (FAE) and blue (Select SINGLE) lines from ST05.

As well as the OPEN SQL Statements?

DB2 you said...

DB2 for zSeries (we call it DB2) or

DB2 for iSeries (we call it DB4) or

DB2 for LUW (we call it DB6)?

For DB6 (DB2 for LUW blocking factor is 30 or 60)... mine was a ORACLE DB where it is 5.

Are the keys in the Buffer? Do you have free space in the single record buffer? swaps?

Kind regards,

Hermann

Addition, sorry i got it wrong... i meant you meassured the time... but you are talking about

record numbers. got it.

So you have duplicates (regarding the key) in the driver table? ...

hm....

Yes, FAE is distinct... it depends on which DB are you using if the distinct is done on

DB or Application Server level... . DB6 i guess... does it on DB...

then your meassurements make sense. you send 10000 values to the db. the db does

distinct and a nested loops on the distinct values.

You compare 68 (or similar low value (depends on data) db accesses with 10000 table buffer Access....

Remove the duplicate before you select in order to make a fair comparison...

add the runtime of removing the dupplicates in ABAP to select single... then it should

be fair...

Kind regards,

Hermann

Edited by: Hermann Gahm on May 22, 2009 6:48 PM

[former_member192616]

Hi,

on DB6

actually your 10000 lines are send to the DB in packages (dependent on max_blocking_factor or max_in_blocking_factor).

If that is 60 you get 167 db calls. for each call (containing the 60 values) the duplicates

are removed and a nested loop is done.

This leads to a relatively low number of records to be accessed with unique key compared

to 10000 buffered key accesse. The 10 times faster access on the table buffer could be

easily worhless since you are doing a lot more calls.

Thats why you have to make it comparable.

Remove the duplicates on application server

and access the table buffer 68 times.

Compare that with your FAE.

If then your FAE is still faster... hm... would not let me fall asleep i think...

Kind regards,

Hermann

[Former Member]

>

> Thats why you have to make it comparable.

I understand and agree with what you are saying.

Up to a point.

But I'm not trying to make a comparison between equals; I'm trying to figure out what works best in the real world. So I think that depending on the number of duplicates in the driver, one or the other may be faster. As Siegfried said - it depends.

But again, in the real world, I wouldn't use either of the methods. As we said earlier, doing a SELECT single only for unique values outperforms both.

Rob

PS - hope you can sleep better now

[former_member192616]

Hi Rob,

sure i understand your point (and i had a good sleep).

You are right in this example it depends.

It depends on the number of duplicates, blocking factor size and the database used.

As far as i know FAE works in this way (sending the values, removing duplicates, doing nested loop)

only in DB6 and MSSQL.

For me, i try to remove the duplicates from driver tables to avoid unnecessary calls (to whatever layer) if possible, since avoidance is still the best optimization.

> Rob Burbank wrote:}

> But again, in the real world, I wouldn't use either of the methods.

Yes, with your internal table approach you are effectively removing the identical SELECT SINGLES from the table buffer (and execute these identical calls as a READ on an internal table).

The speed up you get has to be paid with memory consumption since you buffer now twice (the SAP table buffer with your SELECT single does and your ABAP program in the internal tables does.

The faster you want to go, the more memory you need seems to be the general rule

Thanks for that interesting discussion.

Kind regards,

Hermann

[former_member194613]

I did not follow the whole discussion, but even in real world ... if you expect that a driver table

for FAE contains duplicates, then use SORT and DELETE ADJACENT DUPLICATES before

you execute the SELECT.

DISTINCT for FAE, I don't understand how thiis could be done on the Database side, the blocks

are send to the DB and processed independently, duplicates in different blocks must be removed

on the application server.

=>but if anybody knows that better, please tell me how it works.

And please never sort inside a loop !!!!!

loop at it_bseg.
    read table it_skat with key
        spras = 'EN'
        ktopl = p_bukrs
        saknr = it_bseg-hkont
        binary search.
    if sy-subrc NE 0.
      select single spras ktopl saknr txt20
        from skat
        into it_skat
        where spras = 'E'
           and ktopl = p_bukrs
           and saknr = it_bseg-hkont.
      if sy-subrc = 0.
        append it_skat.
        sort it_skat by saknr.
      endif.
    endif.
  endloop.

Use a sorted table or combine a READ BINARY SEARCH with a INSERT INDEX this is the insert for

the sorted standard table.

Siegfried

[former_member192616]

Hi Siegfried,

>

> DISTINCT for FAE, I don't understand how thiis could be done on the Database side, the blocks 
> are send to the DB and processed independently, duplicates in different blocks must be removed
> on the application server.
> =>but if anybody knows that better, please tell me how it works.
> Siegfried

I was talking about duplicates within a block. Duplicates in different blockes are removed in the

database interface before the result set is copied to the internal table in the ABAP application, thats true.

Let's compare 2 execution plans from 2 databases for the same thing.

FAE with 5 values, 3 distinct.

For ORACLE:

SELECT
  *
FROM
  "T100"
WHERE "SPRSL" = :A0 AND "ARBGB" = :A1 AND "MSGNR" = :A2 
OR         "SPRSL" = :A3 AND "ARBGB" = :A4 AND "MSGNR" = :A5 
OR         "SPRSL" = :A6 AND "ARBGB" = :A7 AND "MSGNR" = :A8 
OR         "SPRSL" = :A9 AND "ARBGB" = :A10 AND "MSGNR" = :A11 
OR          "SPRSL" = :A12 AND "ARBGB" = :A13 AND "MSGNR" = :A14

SELECT STATEMENT ( Estimated Costs = 1 , Estimated #Rows = 0 )

       *3 INLIST ITERATOR*

           2 TABLE ACCESS BY INDEX ROWID T100
             ( Estim. Costs = 1 , Estim. #Rows = 5 )
             Estim. CPU-Costs = 6,107 Estim. IO-Costs = 1

               1 INDEX RANGE SCAN T100~0
                 ( Estim. Costs = 1 , Estim. #Rows = 5 )
                 Search Columns: 3
                 Estim. CPU-Costs = 4,473 Estim. IO-Costs = 1
                 Access Predicates

From the ORACLE documentation:

The INLIST ITERATOR operation iterates over the next operation in the plan for each value in the IN-list predicate.

In 10.2.0.2 this lead to 20 buffer gets. 5 x 4 (3 for index range scan, and 1 for the table).

In 10.2.0.4 it is only 12 buffer gets. 3 x 4.

According to the documentation (10.2.0.4) we should have seen 20 as well since we sent 5 value pairs.

Since i don't have 10.2.0.2 at hand at the moment i can not check how 10.2.0.2 behaves now. Maybe it was a bug, or it was optimized with a fixpack or in 10.2.0.4. The 10.2.0.4 documentation is not precise here.

Btw, we should see a index uniqe scan here since we use primary key with equals... seems to be another.... hm ... bug? display issue? I'll follow up...

For DB6 please see second posting... space limit regarding formating...

Kind regards,

Hermann

[former_member192616]

Hi Siegfried,

>

> DISTINCT for FAE, I don't understand how thiis could be done on the Database side, the blocks 
> are send to the DB and processed independently, duplicates in different blocks must be removed
> on the application server.
> =>but if anybody knows that better, please tell me how it works.
> Siegfried

This is the 2nd part:

FAE with 5 values, 3 distinct.

For DB6:

 SELECT
   *
 FROM
   "T100" T_00 , ( SELECT * FROM ( VALUES ( CAST ( ? AS VARCHAR(3) ),
   CAST ( ? AS VARCHAR(60) ), CAST ( ? AS VARCHAR(9) ) ),
   ( CAST ( ? AS VARCHAR(3) ), CAST ( ? AS VARCHAR(60) ),
   CAST ( ? AS VARCHAR(9) ) ), ( CAST ( ? AS VARCHAR(3) ),
   CAST ( ? AS VARCHAR(60) ), CAST ( ? AS VARCHAR(9) ) ),
   ( CAST ( ? AS VARCHAR(3) ), CAST ( ? AS VARCHAR(60) ),
   CAST ( ? AS VARCHAR(9) ) ), ( CAST ( ? AS VARCHAR(3) ),
   CAST ( ? AS VARCHAR(60) ),
   CAST ( ? AS VARCHAR(9) ) ) ) AS T_01_TMP ( "C_01", "C_02",
   "C_03" ) GROUP BY  "C_01", "C_02", "C_03" ) AS T_01
 WHERE
   T_00 . "SPRSL" = T_01 . C_01 AND T_00 . "ARBGB" = T_01 . C_02 AND T_00 .
   "MSGNR" = T_01 . C_03  WITH UR

   0 SELECT STATEMENT ( Estimated Costs =  7,574E+01 [timerons] )

          1 RETURN

              2 NLJOIN

                  3 [O] TBSCAN

                      4 SORT

                          5 TBSCAN GENROW

                  6 <i> FETCH T100

                      7 IXSCAN T100~0 #key columns:  3

In the SQL Text you can see GROUP BY. But not in the plan.

I assume that T_01 is build before the first step of the execution plan

5 TBSCAN GENROW is executed.

So the unique values are sorted ( 4 SORT) and the output is used as outer table for the NL Join (3 [O] TBSCAN). The sorting is due to optimize the clustering factor before accessing the table.

(7 IXSCAN T100~0 AND 6 FETCH T100)

DB6 Docu: Group BY, Groups rows by common values of designated columns.

According to my understanding, here the DB removes duplicates within a

package with GROUP BY.

Let's test it:

5 values with 3 distinct values

This lead to 9 data pool logical reads.

That is 3 times 3 ( And it is 2 for the index and 1 table block for each execution since the index has 1 level less compared to my Oracle DB.).

So it seems to work, otherwise we would have seen 15 data pool logical reads.

Kind regards,

Hermann

[former_member192616]

Hi,

small addition:

Removing duplicates within blocks is an interesting topic. DB6 and MSSLQ remove duplicates

before executing the distincts. (MSSQL does a union on th input (which removes duplicates) and

then a nested loop on the remaining values).

I just realized that ORACLE does it as well on 10.2.0.4 allthough it is not obvious from

the plan or the documentation.

I have no clue about DB2 and DB4 and MAXDB .

You can guess from the plans... .

MAXDB (UNION ALL for each avlue, then distinct)

- seems to execute all (no removal of duplicates for input) but remove the duplicates from the result set (output).

DB4 (OR CONCATENATION for each avlue)

- seems to execute and return all to the DBI

DB2 (UNION ALL for each avlue)

- seems to execute and return all to the DBI

For MAXDB i checked the page accesses it looks like all values are executed 5 page accesser per row. In MAXDB the primary index has more levels since it contains the data.

For the others, you never know... see ORACLE example.

It is (for me) not so easy to find it out, because it is DB specific and sometimes you can not get these deails within SAP but only on DB level. But even if on SAP level the information is available

you have to know how to get, where to find and how to read it... . But i'll try when i have time... .

Removing duplicates within blocks is of course more efficient if we have big blocks:

DB6 uses for OR concat 30 and for IN 30 or 60 (default, depends on SAP release).

MSSQL uses for OR concat 5 and for IN 255 (default, depends on SAP release).

ORACLE uses 5 in any case.

Generaly these values must not be changed on system wide level. For optimizing reasons we increase

it sometimes on statement level, on ORACLE databases.

But removing duplicates is not the reason for that. As already said, this should

be done in ABAP (DELETE ADJACENT DUPLICATES).

We do that for another reason: to have less DB calls and therefore less communication

overhead. But this is a different topic.

Kind regards,

Hermann

[former_member194613]

> But I also tried another method

you should actually not mix up the two buffering method, if table buffering is applicable then you should use it and you shoud not use the self-made buffer.

Buffer-methods/functions/forms are very useful, and necessary in cases where the table buffering does not work.

[former_member194613]

Hallo Hermann,

thank you for the detailed explanation, actually I did not see, that you talked about in block

duplicates.

Larger blocksizes are however quite rare:

I saw them only on Max DB

> DB6 uses for OR concat 30 and for IN 30 or 60 (default, depends on SAP release).

> MSSQL uses for OR concat 5 and for IN 255 (default, depends on SAP release).

> ORACLE uses 5 in any case.

this is true, but usually rsdb/prefer_in_itab_opt is zero on DB6 and MSSQL.

Siegfried

[former_member192616]

Hi Siegfried,

>

> this is true, but usually rsdb/prefer_in_itab_opt is zero on DB6 and MSSQL.
> Siegfried

yes, rsdb/prefer_join is set instead (since they do a join).

DB6:

max_blocking_factor = 30, max_in_blocking_factor = 60,

min_blocking_factor = 1, min_in_blocking_factor = 1,

prefer_union_all = 1, prefer_join = 1,

prefer_fix_blocking = 0, prefer_in_itab_opt = 0,

MSSQL:

max_blocking_factor = 50, max_in_blocking_factor = 255,

min_blocking_factor = 5, min_in_blocking_factor = 10,

prefer_union_all = 1, prefer_join = 1,

prefer_fix_blocking = 0, prefer_in_itab_opt = 0,

With a SELECT like this (i know it doesn't make sence, lets just assume msgnr

would be the pirmary key):

select * from t100 into table it_t100 bypassing buffer

for all entries in it

where msgnr = it-msgnr.

i get 60 bind variables on DB6 per Block with the above settings.

Seems like max_in_blocking_factor = 60 used here.

On MSSQL it's 50, seems like max_blocking_factor = 50 is used there.

It is not so uncommon to see such sizes i think.

Since I work more often on DB6 than on MSSQL i can only tell for DB6.

There i see it a lot of times working quite well i would say.

I think that could explain why Rob's FAE with 10000 values and 68 distincts

on DB6 is slightly faster then 10000 select single on the table buffer... .

Kind regards,

Hermann