|
Database requests are interconnected and always occur in the same logical sequence.
The DECLARE function defines and numbers the cursor. DECLARE precedes the PREPARE function.
Use PREPARE to prepare a specific SQL statement, such as:
select * from sflight where carrid eq 'LH'.
and define the access method before the system can transfer the request to the database. During this preparation, the system is concerned only with the structure of the SQL statement and not with the values it contains.
The OPEN function takes the prepared SELECT statement and completes it with the correct values. In the above example, OPEN would issue the field carrid the value LH.
FETCH passes the entries from the database to the database interface of the SAP System All of the database operations required to execute an SQL statement are linked by the same cursor ID.
If the SQL statement makes changes in the database (INSERT, UPDATE, DELETE), PREPARE is followed by EXEC, which executes the statement
If the system can refer back to an SQL statement that has already been prepared, there is no PREPARE operation, and the statement is executed using REOPEN or REEXEC as appropriate
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.
Table Buffering
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.
Buffering of Database Requests
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.
Analyzing a Sample SQL Data File
When you create an SQL trace file for an application, you can see exactly how the system handles database operations. In a sample application, a report reads, and later changes, records on the ABAP Dictionary table SFLIGHT using ABAP Open SQL statements. Since the table SFLIGHT is not buffered, the system first needs to access the database to retrieve the records. In the sections below, the data file from the sample application is analyzed.
Read Access
The first screen of the SQL trace file displays each measured database request the application made. The trace file records when the request occurred and its duration. The ABAP Dictionary table involved in the request is also listed.
A trace file for a read access of the table SFLIGHT might look like this:
|
