buffered tables in join

[Former Member]

Hi all,

Can below select give much poor performance.

used tables are buffered tables. i have used them in join, so buffering will be by passed.

if so plz suggest other way.

SELECT a~WERKS INTO TABLE ITAB_WERKS

FROM T001W as a

inner join T001K as b on a_{bwkey eq b}bwkey

WHERE a~WERKS eq p_werks

and b~bukrs EQ '9101'.

[Former Member]

Hi

<b>Database access using Buffer concept</b>

Buffering allows you to access data quicker by letting you

access it from the application server instead of the database.

<b>Advantages of buffering</b>

Table buffering increases the performance when the records of the table are read.

As records of a buffered table are read directly from the local buffer of the application server on which the accessing transaction is running, time required to access data is greatly reduced. The access improves by a factor of 10 to 100 depending on the structure of the table and on the exact system configuration.

If the storage requirements in the buffer increase due to further data, the data that has not been accessed for the longest time is displaced. This displacement takes place asynchronously at certain times which are defined dynamically based on the buffer accesses. Data is only displaced if the free space in the buffer is less than a predefined value or the quality of the access is not satisfactory at this time.

Entering $TAB in the command field resets the table buffers on the corresponding application server. Only use this command if there are inconsistencies in the buffer. In large systems, it can take several hours to fill the buffers. The performance is considerably reduced during this time.

<b>Concept of buffering</b>

The R/3 System manages and synchronizes the buffers on the individual application servers. If an application program accesses data of a table, the database interfaces determines whether this data lies in the buffer of the application server. If this is the case, the data is read directly from the buffer. If the data is not in the buffer of the application server, it is read from the database and loaded into the buffer. The buffer can therefore satisfy the next access to this data.

The buffering type determines which records of the table are loaded into the buffer of the application server when a record of the table is accessed. There are three different buffering types.

With full buffering, all the table records are loaded into the buffer when one record of the table is accessed.

With generic buffering, all the records whose left-justified part of the key is the same are loaded into the buffer when a table record is accessed.

With single-record buffering, only the record that was accessed is loaded into the buffer.

<b>Synchronizing local buffers</b>

The table buffers reside locally on each application server in the system. However, this makes it necessary for the buffer administration to transfer all changes made to buffered objects to all the application servers of the system.

If a buffered table is modified, it is updated synchronously in the buffer of the application server from which the change was made. The buffers of the whole network, that is, the buffers of all the other application servers, are synchronized with an asynchronous procedure.

Entries are written in a central database table (DDLOG) after each table modification that could be buffered. Each application server reads these entries at fixed time intervals.

If entries are found that show a change to the data buffered by this server, this data is invalidated. If this data is accessed again, it is read directly from the database. In such an access, the table can then be loaded to the buffer again.

<b>Using buffered tables improves the performance considerably</b>

Bypassing the buffer increases the network considerably

SELECT SINGLE * FROM T100 INTO T100_WA

BYPASSING BUFFER

WHERE SPRSL = 'D'

AND ARBGB = '00'

AND MSGNR = '999'.

The above mentioned code can be more optimized by using the following code

SELECT SINGLE * FROM T100 INTO T100_WA

WHERE SPRSL = 'D'

AND ARBGB = '00'

AND MSGNR = '999'.

<b>reward if usefull</b>

[ThomasZloch]

If the select statement is executed only once in your code to fill that internal table, it does not really matter, since both are customizing tables with a limited number of entries.

Only when repeatedly selecting (e.g. within loops) should you avoid using joins on buffered tables, instead read the single tables into workareas/internal tables and work with the data from there.

Cheers

Thomas

[Former Member]

Buffered tables should not be used in joins, because the joins are not reallized by

the SAP buffers:

Use nested SELECTS:

SELECT bwkey
              INTO b_bwkey
              FROM t001k
              WHERE bukrs = '9101'.

   SELECT werks
                INTO a_werks  
                FROM t001w 
                WHERE bwkey = b_bwkey
                AND      werks = p_werks.

APPEND a_werks TO TABLE itab_werks.

    ENDSELECT.
ENDSELECT.

Siegfried

[Former Member]

Siegfried - I wrote a small program based on your code snippet:

REPORT ztest MESSAGE-ID 00.

TABLES: t001k, t001w.

PARAMETERS: p_bukrs TYPE t001k-bukrs,
            p_werks TYPE t001w-werks.

DATA: b_bwkey   TYPE t001k-bwkey,
      a_werks   TYPE t001w-werks,

      BEGIN OF itab OCCURS 0,
        bwkey   TYPE t001k-bwkey,
      END   OF itab.

DATA: start     TYPE i,
      end       TYPE i,
      dif       TYPE i,
      count     TYPE i.

DO 5 TIMES.
  CLEAR count.
  GET RUN TIME FIELD start.
  SELECT bwkey
    INTO b_bwkey
    FROM t001k
    WHERE bukrs = p_bukrs.

    SELECT werks
      INTO a_werks
      FROM t001w
      WHERE bwkey = b_bwkey
        AND werks = p_werks.
      count = count + 1.
    ENDSELECT.
  ENDSELECT.
  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for nested SELECTs on buffered tables: ', dif,
              'microseconds for', count, 'records'.
ENDDO.

SKIP 1.

DO 5 TIMES.
  CLEAR count.
  GET RUN TIME FIELD start.
  SELECT t001k~bwkey t001w~werks
    INTO (b_bwkey, a_werks)
    FROM t001k JOIN t001w
      ON t001k~bwkey = t001w~bwkey
    WHERE t001k~bukrs = p_bukrs
      AND t001w~werks = p_werks.
    count = count + 1.
  ENDSELECT.
  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for JOINED SELECTs on buffered tables: ', dif,
              'microseconds for', count, 'records'.
ENDDO.
SKIP 1.

DO 5 TIMES.
  CLEAR count.
  GET RUN TIME FIELD start.
  SELECT bwkey
    INTO TABLE itab
    FROM t001k
    WHERE bukrs = p_bukrs.
  SELECT werks
    INTO a_werks
    FROM t001w
    FOR ALL ENTRIES IN itab
    WHERE bwkey = itab-bwkey
      AND werks = p_werks.
    count = count + 1.
  ENDSELECT.

  GET RUN TIME FIELD end.
  dif = end - start.
  WRITE: /001 'Time for FOR ALL ENTRIEs on buffered tables:', dif,
              'microseconds for', count, 'records'.
ENDDO.

The results were interesting:

the JOIN quite often takes much more time than the nested SELECTS, but other times the times are quite comperable. But the clear winner in this case is FOR ALL ENTRIES.

Rob

[Former Member]

Nope,

Your code is perfectly fine.

Since it's ur intention to bypass buffer that's being done.

In this case there is no other better way .

regards

Nishant