TAW10 - PERFORMANCE WHEN ACCESSING THE TABLES

STRUCTURE OF AN INDEX

An index is a copy of the database table containing only the key fields. As data in an index is sorted, it can be accessed and retrieved instantly. Indexes also contain a pointer from the index entry to the corresponding table entry.
When index is used for data retrieval, all fields constituting the index will participate in the WHERE clause. In the example shown in the slide, the SELECT * statement is used to fetch data from the SCARR table where the value of CARRID is LH. Therefore, binary search is used to locate the records in data table. LH is in the first position in the WHERE clause as it has the maximum number of selections, 14.

ACCESSES WITH INDEXES

The function of the database optimizer is to determine which index should be used by the database for accessing data records. When you use the SELECT * statement with carname as Lufthansa, the database optimizer selects the SCARR NAM index table to fetch data from the SCARR table.
There are two types of indexes: Primary and Secondary. The index type of a particular index can be determined by its three-place index ID. If the index ID is zero, it denotes a primary index.
Customers can also create their own indexes on SAP tables. The index IDs for such indexes always begin with either Y or Z.

DATA ACCESS USING THE BUFFER

You can use table buffers to increase performance when reading records from a table. The buffers reside locally on the application servers. During the initial access, data is read from the database. Thereafter, data remains loaded into the buffer and is accessed directly from it. This speeds up the whole process.


FULL BUFFERING

Full buffering means that a table is either completely buffered or not buffered at all. In full buffering, when a single record is accessed, all the records of that table are also loaded into the buffer. There are three points to be kept in mind while using full buffering. These are
  the size of the table to be buffered;

  the number of read accesses; and

  the number of write accesses.

It is commonly observed that the smaller the table is; the more frequently it is read and the less frequently it is written. This makes it ideal for full buffering. It is also recommended for tables that have frequent accesses to records that do not exist.

GENERIC BUFFERING

With generic buffering, when you access a particular record, all other records whose generic key fields match this record are loaded into the buffer. The generic key for a table is the left-justified part of a primary key that is specified when the buffering type is selected.


SINGLE-RECORD BUFFERING

Single-record buffering is preferred for
  records that are accessed with SELECT SINGLE command; and

  tables in which the individual entries are repeatedly accessed.

In single-record buffering, only those records that are actually accessed are loaded into the buffer. This can save a lot of storage space in the buffer compared to generic and full buffering. However, in this case, the overhead for buffer administration is higher than that for generic or full buffering.


TABLE BUFFERING

The buffers reside locally on the application servers. During initial access, data is read from the database. Thereafter, data remains loaded into the buffer and are accessed directly from it, making the data access easier and faster. However, another program can change this data in the database. Therefore, data must be synchronized.


BUFFER SYNCHRONIZATION 1

As the buffers reside locally on the application servers, they must be synchronized after any data has been modified in a buffered table. This synchronization occurs at periodic intervals laid down in the system profile.
Let us understand this process with an example.
As shown here, two application servers need to access Table SCARR in the database. Server 1 first reads the records from Table SCARR on the database. This table is then loaded into a local buffer on server 1. This local buffer is subsequently used by server 1 to access the data.



BUFFER SYNCHORIZATION 2

Here, Server 2 accesses the records from Table SCARR in the database and loads it into its local buffer.

BUFFER SYNCHRONIZATION 3

If a user on Server 1 deletes certain records from the SCARR table and updates the database, then Server 1 writes an entry in the synchronization table and updates its local buffer.


BUFFER SYNCHRONIZATION 4

A user on Server 2 can still access the deleted records because the records reside in his local buffer, even though they no longer exist in the database table. If the same access had been made through an application program to Server 1, it would have replied stating that the records no longer exist.


BUFFER SYNCHRONIZATION 5

The synchronization of the servers takes place at fixed time intervals. During synchronization, both servers check the synchronization table to look for any modifications.
Server 2 now realizes that the SCARR table has been modified by server 1 and immediately invalidates the table in its local buffer. Server 1, on the other hand, does not need to invalidate the table in its buffer, since it had made the modification initially. Therefore, it continues to use the table in its buffer to access the SCARR table.

BUFFER SYNCHRONIZATION 6

After the table in its buffer is invalidated during the synchronization process, Server 2 accesses the SCARR table again. It does this through the database. The SCARR table from the database is now reloaded into the local buffer of Server 2.


DECISION TREE FOR BUFFERING

This slide illustrates a decision-making tree for buffering tables. It illustrates the decision-making process involved in buffering tables. It is seen that buffering significantly increases performance. This is because table buffers reside locally on an application server and are refreshed at regular intervals. However, it is important to select the right type of buffering. Another crucial point is that the more frequently a table is read; and the less frequently its contents are changed, the better it is to buffer the table.