Full Buffering

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.

In this example, the record highlighted in red is read by a program from table SCOUNTER. If the table is fully buffered, all the records of the table are loaded into the buffer.

The data records are sorted in the buffer according to the table key. When the table is accessed with SELECT, only fields up to the last specified key field can be used for the access. The flush left part of the key should therefore be as large as possible in such accesses. For example, if you do not define the first key field, a full table scan takes place in the buffer. In this case direct access to the database can be more efficient if the database has suitable secondary indexes.

When Should you Use Full Buffering?

When considering 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 and are subjected to a large number of read accesses, but are rarely written to.

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

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 tables of the record reside in the buffer, it is possible to decide directly in the buffer whether or not a record exists.