Buffer Table

[Former Member]

What is Buffer table and

what is the condition of buffering a table and

how do we buffer the table?

[Former Member]

Hi,

Just go through below link,

http://help.sap.com/saphelp_webas620/helpdata/en/cf/21eadf446011d189700000e8322d00/content.htm

Reward if useful!

[Former Member]

The table buffer is located between the application and the database. Its goal is to cache portions of a database table in the application server to reduce database load and network communication

http://help.sap.com/saphelp_nw04/helpdata/en/47/12ba1cd9ed584a957e5cf6bf243625/content.htm

[Former Member]

Hi,

Go through this info.

Buffering

You must define whether and how a table is buffered in the

technical settings for the table. There are three possibilities

here:

1> Buffering not permitted: Table buffering is not permitted,

for

example because application programs always need the

most

recent data from the table or the table is changed too

frequently.

2> Buffering permitted but not activated: Buffering is

permitted from the business and technical points of view.

Applications which access the table execute correctly with

and without table buffering.

Whether or not table buffering will result in a gain in

performance depends on the table size and access profile

of the table (frequency of the different types of table

access).

Table buffering is deactivated because it is not possible to

know what these values will be in the customer system. If

table buffering would be advantageous for the table size and

access profile of the table, you can activate it in the customer

system at any time.

3> Buffering activated: The table should be buffered. In this

case you must specify a buffering type.

Buffering types:

1> Single-Record buffering

With single-record buffering, only the records that are actually

read are loaded into the buffer. Single-record buffering therefore

requires less storage space in the buffer than generic and full

buffering. The administrative costs in the buffer, however, are

greater than for generic or full buffering. Considerably more

database accesses are necessary to load the records than for

the other buffering types.

When Should you Use Single-Record Buffering?

Single-record buffering should be used particularly for

large tables where only a few records are accessed with

SELECT SINGLE. The size of the records being accessed

should be between 100 and 200 KB.

Full buffering is usually more suitable for smaller tables

that are accessed frequently. This is because only one

database access is necessary to load such a table with full

buffering, whereas several database accesses are

necessary for single-record buffering.

2> Generic buffering

With generic buffering, all the records in the buffer whose

generic key fields match this record are loaded when one

record of the table is accessed. The generic key is a part of

the primary key of the table that is left-justified.

3> Full buffering

With full buffering, either the entire table is in the

buffer or the table is not in the buffer at all. All the

records of the table are loaded into the buffer when

one record of the table is read.

When Should you Use Full Buffering?

When deciding whether a table should be fully buffered, you

should take into account the size of the table, the number of

read accesses,

and the number of write accesses. Tables best suited to full

buffering are small, read frequently, and rarely written.

Full buffering is recommended in the following cases:

Tables up to 30 KB in size. If a table is accessed frequently, but

allaccesses are read accesses, this value can be exceeded.

However, youshould always pay attention to the buffer

utilization.

Larger tables where large numbers of records are frequently

Regards,

Sankar

[Former Member]

U can use the key word bye-passing buffer in select stmt.

Performance:

Storing database tables in a local buffer (see SAP buffering) can lead to considerable time savings in a client/server environment, since the access time across the network is considerably higher than that required to access a locally-buffered table.

Notes

A SELECT statement on a table for which SAP buffering has been declared in the ABAP Dictionary usually reads data from the SAP buffer without accessing the database. This does not apply when you use:

- SELECT SINGLE FOR UPDATE or

- SELECT DISTINCT in the SELECT clause,

- BYPASSING BUFFER in the FROM clause,

- ORDER BY f1 ... fn in the ORDER BY clause,

- Aggregate functions in the SELECT clause,

- When you use IS [NOT] NULL in the WHERE condition,

or when the table has generic buffering and the appropriate section of the key is not specified in the WHERE condition.

The SELECT statement does not perform its own authorization checks. You should write your own at program level.

Proper synchronization of simultaneous access by several users to the same set of data cannot be assured by the database lock mechanism. In many cases, you will need to use the SAP locking mechanism.

Changes to data in the database are not made permanent until a database commit (see LUW) occurs. Up to this point, you can undo any changes using a databse rollback (see Programming Transactions). At the lowest isolation level (see lock mechanism ), the "Uncommitted Read", it can sometimes be the case that data selected by a SELECT statement was never written to the database. While a program is selecting data, a second program could be adding data to, changing data in, or deleting data from the database at the same time. If the second program then executes a rollback, the first program has selected a set of data that may only represent a temporary state from the database. If this kind of "phantom data" is unacceptable in the context of your application, you must either use the SAP locking mechanism or change the isolation level of the database system to at least "Committed Read" (see locking mechanism).

In a SELECT - ENDSELECT loop, the CONTINUE statement terminates the current loop pass and starts the next.

If a SELECT - ENDSELECT loop contains a statement that triggers a database commit, the cursor belonging to the loop is lost and a program termination and runtime error occur. Remote Function Calls and changes of screen always lead to a database commit. The following statements are consequently not allowed wihtin a SELECT-ENDSELECT loop: CALL FUNCTION ... STARTING NEW TASK , CALL FUNCTION ... DESTINATION , CALL FUNCTION ... IN BACKGROUND TASK , CALL SCREEN, CALL DIALOG, CALL TRANSACTION, and MESSAGE.

[Former Member]

Buffered Tables (includes both Transparent & Pool Tables)

While buffering database tables in program memory (SELECT into internal table) is generally a good idea for performance, it is not always necessary. Some tables are already buffered in memory. These are mostly configuration tables. If a table is already buffered, then a select statement against it is very fast. To determine if a table is buffered, choose the 'technical settings' soft button from the data dictionary display of a table (SE12). Pool tables should all be buffered.

refer

http://help.sap.com/saphelp_nw04/helpdata/en/47/12ba1cd9ed584a957e5cf6bf243625/content.htm

http://sap.ittoolbox.com/groups/technical-functional/sap-basis/buffering-tables-306296

http://blogs.ittoolbox.com/sap/db2/archives/changing-sap-table-buffering-13556

http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_TableBuffering.asp

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21eadf446011d189700000e8322d00/content.htm

<b>buffering DB tables</b>

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f244446011d189700000e8322d00/content.htm

<b>Which tables should be buffered?</b>

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f26b446011d189700000e8322d00/content.htm

regards,

srinivas

<b>*reward for useful answers*</b>

[Former Member]

Hello,

<b>Improving the Performance through Table Buffering</b>

The records of a buffered table are read directly from the local buffer of the

application server on which the accessing transaction is running when the table

is accessed. This eliminates time-consuming database accesses. The access

improves by a factor of 10 to 100. The increase in speed depends on the structure

of the table and on the exact system configuration. Buffering, therefore, can

greatly increase the system performance.

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.

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

the following buffering types:

&#149; Full buffering: When a record of the table is accessed, all the records of

the table are loaded into the buffer.

&#149; Generic buffering: When a record of the table is accessed, all the records

whose left-justified part of the key is the same are loaded into the buffer.

&#149; Single-record buffering: Only the record that was accessed is loaded

into the buffer.

Using examples, explain which data are written in the puffer when accessing a

data record in a buffered table. First of all explain briefly the three buffering types,

so that the difference between these is clear. The explain under what conditions

what buffering type is implemented. Explain that it only makes sense to load

records into the buffer if you think that these records will be accessed in the near

future. Full buffering: is recommended for small tables, which are frequently

read but rarely written in.

<b>This means that:</b>

&#149; Only those tables that are written very infrequently (read mostly) or for

which such temporary inconsistencies are of no importance may be buffered.

&#149; Tables whose entries change frequently should not be buffered. Otherwise

there would be a constant invalidation and reload, which would have a

negative effect on the performance.

An index helps you to speed up read accesses to a table. An index can be

considered a sorted copy of the table that is reduced to the index fields.

The table buffers reside locally on the application servers.

Buffering can substantially increase the performance when the records of the table

are accessed. Choosing the correct buffering type is important.

The table buffers are adjusted to changes to the table entries at fixed intervals.

The more frequently a table is read and the less frequently the table contents are

changed, the better it is to buffer the table.

Regards,

LIJO JOHN