Performance Improvement - Buffering

[Former Member]

Hi ,

To improve the performance of the ABAP Program we can buffer database table.

my Question is: which table can be buffered , Master databse table or Transactional database table?

Regards,

Archana.

[Simha_]

Hi,

Master data table has to be bufered...

The table that often doesn't changes data are to be buffered...

Cheers,

SImha.

[raviprakash]

Hi Archana,

Its a thumb rule to buffer only the MASTER tables which seldom undergo changes. The TRANSACTIONAL tables keep changing every moment and so if it is buffered, you would always get wrong information.

When you buffer a table, the data resides at the buffer at the Application Layer and thus is updated only after long times from Database.

Thanks and regards,

Ravi.

NOTE: Points are invited .

[christian_wohlfahrt]

Hi Archana!

You can check with transaction ST10, how often tables are read and changed. The ratio should give you an idea, if buffering makes sense. In case a table is already buffered, the number of invalidations are shown. In such a case the next request has to fill the whole buffer -> slow access. Therefore only small percentages are acceptable, best below 1% (for my feeling).

But the ratio between read / change can also differ during the time of day / week / year - it's depending on the business behind the tables.

Regards,

Christian

[Former Member]

transactional data table cannot be bufferedonly master data can be buffered.

reward with points

[Sathish]

The concept of Internal Tables is also related to buffering.

If you are talking of buffering using the transaction SE11, yes do so only for Master Data tables.

[JimSpath]

You also should consider your application server memory and how much is allocated to table buffers. We started with 1GB app servers and 40MB table buffers, and now have 12GB app servers with 100-200MB table buffers.

We have tuned buffering over time by looking at which tables are invalidated, which ones grow and which one are most frequently accessed.

Doing an ST05 trace on your most critical business transactions (without and then with application tracing) can tell you which tables are being read from the table buffers and which are being read from the database buffers.

Read the SAP Performance Optimization Guide (<i>Schneider</i>) chapter 9 ("available on amazon.com").

Jim

[former_member194613]

To improve performance you should buffer ALL data which are read several times in your program.

BUT there are 2 concepts of buffering, the technical table buffers and the buffering in internal tables in your memory.

Technical buffering is useful:

+ for data which changes only rarely

+ and in cases of changes, it should not be absolutely necessary that the data are up-to-date

+ and the accesses to the table must use trhe primary key. Table buffer has no secondary key

+ and last but not least, the tables should not be too large.

It is absolutely recommended for customizing data, and for some master data.

But not for transactional data.

For the rest of the frequently used data you should built buffer modules, there you check first whether the data is already in the internal table, and if not you read the rest from the database. The internal tables lives not longer than your application, so it reads only a small portion of the transactional data and is also up-to-date for these data.

Siegfried

[Former Member]

Hi

Table buffering

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>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>