Buffering
The SAP System ensures that data transfer between the SAP System and the database system is as efficient as possible. To do this, it uses the following techniques:
- Table buffering. The program accesses data from the buffer of the application server.
- Database request buffering. Individual database entries are not read or passed to the database until required by an OPEN SQL statement.
When you analyze trace records, you should also examine the system's buffering mechanisms.
For tables can be either partially or fully buffered (refer to Buffering Database Tables.), an OPEN SQL statement only accesses the database if the results of the statement are not already in the buffer Consequently, the SQL Trace does not contain a command or command sequence for every OPEN SQL statement. On the other hand, every SQL statement in the trace file has been sent to the database and executed.
To keep the number of runtime-consuming PREPARE calls small, each an application's work processes hold a certain number of already translated SQL statements in a special buffer. By default, a process holds up to 250 statements.
If the system must execute a specific OPEN SQL, the system first checks whether this statement is stored in the "Statement cache". If the statement is in the cache, the system executes it immediately using a REOPEN (SELECT) or a REEXEC (INSERT, UPDATE, DELETE).
If the statement is not buffered, a PREPARE operation prepares it for the subsequent OPEN/EXEC. The system administers the buffer according to the LRU algorithm ("least recently used"). When space is needed for new statements, the statements that are rarely used are deleted. As a result of the LRU algorithm, the statement must prepare frequently used statements usually only once.
An application server buffers the DECLARE, PREPARE, OPEN, and EXEC requests within the cursor cache of one work process. As a result, once the system opens a cursor for a DECLARE operation, it can use this cursor over and over again within the same work process.