buffering

[Former Member]

hai can anyone pls tell me the need of buffering and what r the diff types of buffering k tnk u

[Former Member]

hi,

Whenever an Open SQL statement is used to read a record,

the data buffer is checked first to see whether it is there. If not, the data is read from the database.

If the table's attributes indicate that the data should be buffered,

the record is saved in RAM on the application server in data buffers.

Later, if that record is read again, it is read from the buffer instead of the database.

By buffering data, you increase performance in two important ways:

The programs using the buffered data run faster because they don't have to wait for it to come from the database. This reduces delays waiting for the database and the network that connects it.

The other programs that need to access the database run faster because there is less load on the database and less traffic on the network.

Three types of buffering are possible:

<b>Full buffering

Generic buffering

Single-record buffering</b>

Although you use a check box to indicate the buffering type, only one type can be selected at a time. If you choose more than one, an error message will be displayed.

There are two data buffers on each application server, and the buffering type chosen determines which buffer is used. On an application sever there are:

The generic record buffer

The single-record buffer

The generic record buffer is called TABL (short for "table"). The single-record buffer is TABLP ("P"' stands for "partial").

<b><u>Full Buffering:</u></b>

To activate the full buffering type, tickmark the Full check box.

When an attempt is made to read data from a fully buffered table,

the system looks in the TABL buffer for it.

If it is not found there, all rows are loaded from the database into TABL .

This happens whenever a select statement for it is executed, no matter how many records match the where clause.

Even if no records match, all are loaded into the buffer if the table is not already there or

if it has been invalidated due to buffer synchronization.

Loading of TABL does not occur with select single statements; only select/ endselect does this.

If the table is fully buffered and a select single statement is executed, no records are loaded into TABL.

However, if the table is already loaded into TABL, select single will read records from it.

During buffer synchronization, the entire table is invalidated if any record within it changes. The next time data is read the entire table is reloaded.

Full buffering is appropriate for small tables that seldom change.

<u><b>

Generic Buffering:</b></u>

With generic buffering, a group of records is loaded into TABL instead of the entire table.

To activate this type of buffering, tickmark the Generic check box

and put a number n in the No. of Key Fields so that the n left-most fields of the primary key will group records.

When a record is read and n is 1, all records having the same value

in the first field of the primary key are loaded into the buffer.

When a record is read and n is 2, all records having the same value in the first two fields of the primary key are loaded

into the buffer.

Generic buffering is suitable for tables in which records are usually accessed in sets or groups.

<u><b>Single Record Buffering:</b></u>

With this type of buffering, select single picks one record into the single record buffer TABLP.

With this buffering type, records are only buffered when the select single statement is executed.

Select/endselect does not load or read TABLP.

Single-record buffering is appropriate for very large tables in which a small percentage of the records are read often.

For single-record buffering to work, records must be read using the select single statement.

There are two buffers:<b> TABL</b> (the generic record buffer) and <b>TABLP</b> (the single record buffer).

There are also two varieties of the select statement: select and select single. The select statement loads TABL and the select single statement loads TABLP.

When reading, select only reads TABL; it ignores TABLP. select single reads both buffers. This behavior is summarized in Figure 5.20. Remember that a record can only be in one buffer at a time because a table can only have one buffering type.

I hope it helps.

Regards

Reshma

[Former Member]

Hi,

If the DB table is accessed many times then it can be buffered in application server for the faster access.

It can be fully buffered. - All entries will be stored on application server or partially buffered.

Regards,

Atish

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

[Former Member]

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.

The buffering type defines whether and how the table should be buffered.

There are the following types of buffering:

single-record buffering

generic area buffering

full buffering

In generic area buffering, a number of key fields between 1 and no. of key fields-1 must be specified.

<b>Single-record buffering</b>

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.

<b>Generic buffering</b>

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

<b>Full buffering</b>

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

Gaurav

[Former Member]

Hi,

By buffering data, you increase performance in two important ways:

The programs using the buffered data run faster because they don't have to wait for it to come from the database. This reduces delays waiting for the database and the network that connects it.

The other programs that need to access the database run faster because there is less load on the database and less traffic on the network.

Buffering a table can cause a select to run 10 to 100 times faster or more. At first thought, it might seem like a good idea to buffer every table in the system. However, buffers are stored entirely in RAM, so space is limited by the amount of RAM available. In fact, there is so much more data than there is RAM that tables must be buffered judiciously to prevent overruns. If a buffer overruns, it might swap to disk, which can obliterate any performance gained by buffering.

Tables containing a numeric data type in the primary key cannot be buffered. The DDIC numeric data types are CURR, DEC, FLTP, INT1, INT2, INT4, PREC, and QUAN.

three types of buffering are possible:

Full buffering

Generic buffering

Single-record buffering

Full Buffering

To activate the full buffering type, tickmark the Full check box. When an attempt is made to read data from a fully buffered table, the system looks in the TABL buffer for it. If it is not found there, all rows are loaded from the database into TABL . This happens whenever a select statement for it is executed, no matter how many records match the where clause. Even if no records match, all are loaded into the buffer if the table is not already there or if it has been invalidated due to buffer synchronization.

Generic Buffering

With generic buffering, a group of records is loaded into TABL instead of the entire table. To activate this type of buffering, tickmark the Generic check box and put a number n in the No. of Key Fields so that the n left-most fields of the primary key will group records. When a record is read and n is 1, all records having the same value in the first field of the primary key are loaded into the buffer. When a record is read and n is 2, all records having the same value in the first two fields of the primary key are loaded into the buffer.

Single Record Buffering

Tickmarking the Single Records check box turns on single-record buffering. With this type of buffering, select single picks one record into the single record buffer TABLP, as shown in Figure 5.19. With this buffering type, records are only buffered when the select single statement is executed. Select/endselect does not load or read TABLP.

Hope this helps.

Reward if helpful.

Regards,

Sipra