Buffering concepts in table

[Former Member]

Hi Floks ,

could u please help him out .where is buffering needed in table .why it required . in z table its required or not how types of buffering is there .

could please explaing or giveme proper links ,send me docs to sunithadf@gmail.com

[Former Member]

We have 3 types of buffering

There are the following types of buffering:

o single-record buffering

o generic area buffering

o full buffering

When should single-record buffering be selected?

o For large tables where there are frequent single-record accesses (using SELECT SINGLE ...). The size of the records being accessed should be between 100-200 KB.

o For comparatively small tables for which the access range is large, it is normally advisable to opt for full buffering. Only one database access is required to load such a table for full buffering,

while single-record buffering calls for a very large number of table accesses.

When should generic buffering be selected?

o A table should be buffered generically if usually only certain areas of the table are required. The individual generic areas are treated like independent tables that are fully buffered. Refer also to the text on complete buffering.

o The generic key area should be selected so that the generic areas are not too small to prevent too may generic areas being produced. If there are only a few records for each generic area, it is more efficient to use full buffering.

o Generic buffering only makes sense if the table is accessed by a specified generic key. If, when an access takes place, a field of the generic key is not supplied with a value, the buffer is ignored

and the records are read directly from the database.

o Language-specific tables are an example of good use of generic buffering (with the language key field as generic key area).

When should you select full buffering?

o For tables up to 30 KB in size. If a table is accessed frequently, but all accesses are read accesses, this value can be exceeded.

o For larger tables where large numbers of records are frequently accessed. However, if the application program is able to formulate an extremely selective WHERE condition using a database index, it may be advisable to dispense with full buffering.

o For tables with frequent accesses to data not contained in the table. Since all records are contained in the buffer, a quick decision can be made as to whether or not the table contains a

record for a specific key.

The buffering type defines which table records are loaded into the buffer of the application server when a table record is accessed. There are the following buffering types:

Full buffering:All the records of the table are loaded into the buffer when one record of the table is accessed.

Full buffering is recommended in the following cases:

Tables up to 30 KB in size. If a table is accessed frequently, but all accesses are read accesses, this value can be exceeded. However, you should always pay attention to the buffer utilization.

Larger tables where large numbers of records are frequently accessed. If these mass accesses can be formulated with a very selective WHERE condition using a database index, it could be better to dispense with buffering.

Tables for which accesses to non-existent records are frequently submitted. Since all the table records reside in the buffer, the system can determine directly in the buffer whether or not a record exists.

Generic buffering: When a record of the table is accessed, all the records having this record in the generic key fields (part of the table key that is left-justified,

identified by specifying a number of key fields) are loaded into the buffer.

A table should be buffered generically if only certain generic areas of the table are normally needed for processing.

Client-specific, fully-buffered tables are automatically generically buffered since normally it is not possible to work in all clients at the same time on an application server. The client field is the generic key.

Language-specific tables are another example where generic buffering is recommended. In general, only records of one language will be needed on an application server. In this case, the generic key includes all the key fields up to and including the language field.

It only makes sense to generically buffer a table if the table is accessed with fully-specified generic key fields. If a field of the generic key is not assigned a value in a SELECT statement, it is read directly from the database, bypassing the buffer.

If you access a generic area that is not in the buffer with a fully-specified generic key, you will access the database to load the area. If the table does not contain any records in the specified area ("No record found"), this area in the buffer is marked as non-existent. It is not necessary to access the database if this area is needed again.

Single-record buffering: Only the records of a table that are really accessed are loaded into the buffer

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.

Check this link :

http://help.sap.com/saphelp_nw04/helpdata/en/f7/e4c5a8a84a11d194eb00a0c929b3c3/content.htm

Regards,

Sai

[paruchuri_nagesh]

hi

The buffering status specifies whether or not a table may be buffered.

This depends on how the table is used, for example on the expected volume of data in the table or on the type of access to a table. (mainly read or mainly write access to the table. In the latter case, for example, one would not select buffering).

You should therefore select

- Buffering not allowed if a table may not be buffered.

- Buffering allowed but not activated if buffering is

principally allowed for a table, but at the moment no buffering

should be active. The

buffering type specified in this case is only

a suggestion.

- Buffering allowed if the table should be buffered. In this

case a buffering type

must be specified.

Single-record buffering

With this kind of buffering, only the records of a table which are actually accessed are loaded into the buffer.

This kind of buffering requires less storage space in the buffer than full buffering. However, greater organization is necessary and considerably more database accesses are necessary for loading.

If an as yet unbuffered record is accessed using SELECT SINGLE, a database access occurs to load the record. If the table does not contain a record for the specified key ('no record found'), this record is noted as nonexistent in the buffer. If a further attempt is made to access this record using SELECT SINGLE, a renewed database access can be avoided.

When should single-record buffering be selected?

For large tables where there are frequent single-record accesses (using SELECT SINGLE ...). The size of the records being accessed should be between 100-200 KB.

For comparatively small tables for which the access range is large, it is normally advisable to opt for full buffering. Only one database access is required to load such a table for full buffering, while single-record buffering calls for a very large number of table accesses.

Generic buffering

In a read access to a record of a generically buffered table, all the records whose left-justified part of the key (generic area) corresponds are loaded into the buffer.

If this type of buffering is selected, the generic area must be defined by specifying a number n of key fields. The first n key fields of the table then define the generic key.

The number of key fields to be entered must lie between 1 and the number of key fields -1. For example, only values between 1 and 5 are permitted for a table with 6 key fields. The client field is included here.

When should generic buffering be selected?

A table should be buffered generically if usually only certain areas of the table are required. The individual generic areas are treated like independent tables that are fully buffered. Refer also to the text on complete buffering.

The generic key area should be selected so that the generic areas are not too small to prevent too may generic areas being produced. If there are only a few records for each generic area, it is more efficient to use full buffering.

Generic buffering only makes sense if the table is accessed by a specified generic key. If, when an access takes place, a field of the generic key is not supplied with a value, the buffer is ignored and the records are read directly from the database.

Language-specific tables are an example of good use of generic buffering (with the language key field as generic key area).

Full buffering

With full buffering, either the complete table or none of the table is in the buffer. If a read access is made to a record, all records of the table are transferred to the buffer.

When should you select full buffering?

For tables up to 30 KB in size. If a table is accessed frequently, but all accesses are read accesses, this value can be exceeded.

For larger tables where large numbers of records are frequently accessed. However, if the application program is able to formulate an extremely selective WHERE condition using a database index, it may be advisable to dispense with full buffering.

For tables with frequent accesses to data not contained in the table. Since all records are contained in the buffer, a quick decision can be made as to whether or not the table contains a record for a specific key.

When considering whether a table should be fully buffered, you should take three aspects 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, frequently read, and rarely updated.

regards

Nagesh.Paruchuri

[Former Member]

Hi

Using buffered tables improves the performance considerably

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>Optimizing the load of the database</b>

<u>Using table buffering</u>

Using buffered tables improves the performance considerably. Note that in some cases a statement can not be used with a buffered table, so when using these statements the buffer will be bypassed. These statements are:

Select DISTINCT

ORDER BY / GROUP BY / HAVING clause

Any WHERE clause that contains a sub query or IS NULL expression

JOIN s

A SELECT... FOR UPDATE

if you wan t to explicitly bypass the buffer, use the BYPASS BUFFER addition to the SELECT clause.

<b>Table buffering</b>

Advantages of buffering

Concept of buffering

Buffering types

Buffer synchronization

<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>Buffering types</b>

With full buffering, the table is either completely or not at all in the buffer. When a record of the table is accessed, all the records of the table are loaded into the buffer.

When you decide whether a table should be fully buffered, you must take the table size, the number of read accesses and the number of write accesses into consideration. The smaller the table is, the more frequently it is read and the less frequently it is written, the better it is to fully buffer the table.

Full buffering is also advisable for tables having frequent accesses to records that do not exist. Since all the records of the table reside in the buffer, it is already clear in the buffer whether or not a record exists.

The data records are stored in the buffer sorted by table key. When you access the data with SELECT, only fields up to the last specified key field can be used for the access. The left-justified part of the key should therefore be as large as possible for such accesses. For example, if the first key field is not defined, the entire table is scanned in the buffer. Under these circumstances, a direct access to the database could be more efficient if there is a suitable secondary index there.

With generic buffering, all the records whose generic key fields agree with this record are loaded into the buffer when one record of the table is accessed. The generic key is a left-justified part of the primary key of the table that must be defined when the buffering type is selected. The generic key should be selected so that the generic areas are not too small, which would result in too many generic areas. If there are only a few records for each generic area, full buffering is usually preferable for the table. If you choose too large a generic key, too much data will be invalidated if there are changes to table entries, which would have a negative effect on the performance.

A table should be generically buffered if only certain generic areas of the table are usually needed for processing.

Client-dependent, fully buffered tables are automatically generically buffered. The client field is the generic key. It is assumed that not all of the clients are being processed at the same time on one application server. Language-dependent tables are a further example of generic buffering. The generic key includes all the key fields up to and including the language field.

The generic areas are managed in the buffer as independent objects. The generic areas are managed analogously to fully buffered tables. You should therefore also read the information about full buffering.

Single-record buffering is recommended particularly for large tables in which only a few records are accessed repeatedly with SELECT SINGLE. All the accesses to the table that do not use SELECT SINGLE bypass the buffer and directly access the database.

If you access a record that was not yet buffered using SELECT SINGLE, there is a database access to load the record. If the table does not contain a record with the specified key, this record is recorded in the buffer as non-existent. This prevents a further database access if you make another access with the same key

You only need one database access to load a table with full buffering, but you need several database accesses with single-record buffering. Full buffering is therefore generally preferable for small tables that are frequently accessed.

<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>Reward if usefull</b>

[varma_narayana]

Hi Sunitha..

Table Buffering can be activated only for the table which are Read frequently but updated rarely such as Customizing data and Some of the Master data.

So based on that you can decide whether ur table can be buffered or not.

Then choose the appropriate buffering type based on size of the table.

eg:

Full buffering is suitable for tables with few records.

<b>reward if Helpful.</b>