Skip to end of metadata
Go to start of metadata

CURSOR v/s SELECT...ENDSELECT and SELECT... INTO <internal table>
 
Cursor: This is a control structure for successive traversal through data. The rows in the result set will be processed sequentially by the application. Cursor acts as an iterator over a collection of rows in the result set. 

Fetching data from the Database
 
Data is fetched from the database depending on the data specified in the SELECT statement along with its additions. There are two core operations to get the data from the database. 
1)      OPEN / RE-OPEN.

2)      FETCH. 

OPEN / RE-OPEN: This is the process to start or to flag-off the process of getting data from the database. This is like a green signal of the traffic lights that denotes that it is the time to get the data from the source location. 

FETCH: This locates the database data that satisfies the conditions and then transfers it into the Application Server. The data is transferred in one or more FETCHes; it is called an Array Fetch. An Array Fetch offers better performance than transferring data in the form of single records in Client/Server architecture.  
Maximum number of records that can be FETCHed in an operation is determined by the SAP DB interface. The parameter that indicates this is dbs/io_buf_size. The number of records that can be fetched depends on the clause in the SELECT statement. 
            No .of Records = <dbs/io_buf_size >/ length of one record (in bytes). 
The default value that is set by the SAP for this purpose is 33,792 bytes. 

Detailed working of the Data FETCH process
 
Apart from the core operations that have been described above, there are other stages in getting data from the database.  
1)      Declare.

2)      Prepare.

3)      Open. 

Declare: In this stage, a cursor is declared and an ID assigned to the cursor. This ID is used for communication between the SAP work process and Database to keep track of the data that is being fetched in chunks. 

Prepare: This is the most crucial phase of the database access. In this, the DB process determines the access strategy for the statement. This is a time taking process, in order to improve on the efficiency for this process; each of the work process of the Application Server retains certain already parsed SQL statements in a special buffer - SAP Cursor Cache. The SAP Cursor Cache follows a four step process to do the operations. The processes are Declare, Prepare, Open and Execute. 

Open: This is a flagging-off stage when all the records that satisfy the WHERE clause are actually got into the Application Server from the Database. The database does not receive the values until an open operation is performed.  

Actual working of the SELECT statements: 

 A SELECT statement makes use of the CURSORS implicitly to fetch the data from the database.  

Using CURSOR: The CURSOR follows a three stage process of getting data from the database. The process is OPEN CURSOR...FETCH CURSOR...CLOSE CURSOR. The detailed usage of this process is described below.

 Analyzing the Con's of CURSOR
 
1)      There is a limit on the number of CURSORS that are used a program.

2)      Only a limited number of cursors can be open at a time.

3)      Any CURSOR that has been opened should be closed.

4)      When we are using 'INTO CORRESPONDING FIELDS OF' in the FETCH statement of the CURSOR, it will display only the data for the latest SELECT statement, in this, the data is not appended but over-written. 

 Need for SELECT statements over CURSOR
 
è    As per the stated disadvantages of using CURSOR, the most essential of all of them is the limitation on the number of CURSORS that can be open at one instance of time.

è    In a scenario, when the CURSORS that are OPENed for usage but not CLOSEd will cause performance problems and resource blocking issues. When a CURSOR is Opened for fetching data and not closed, it leads increase in Space Complexity.

è    In order to avoid this, the SELECT statements are used. The SELECT statements will implicitly use the CURSOR methodology, but make sure that once the operations are complete, they are closed.

è    This solves the problem of Space Complexity.

è    Space Complexity and Time Complexity of an algorithm are inversely proportionate; thereby the performance of the SELECT is slower than that of CURSOR.

Analyzing the Pro's of CURSOR over SELECTs
 
è    In a normal SELECT statement, the data is directly read into the target area specified by the INTO clause. When we use a SELECT...ENDSELECT statement, records are read row by row and individually access the RDBMS each and every time.

è    Using a CURSOR decouples the process of INTO clause and the SELECT statement. After getting the complete data, the data is put into the target area.

è    Using the CURSOR, we can formulate the SELECT statement so that the selection consists of more than one line.

è    CURSOR creates a pointer to the DB record being created, with this; data is not actually copied into ABAP Application Server. CURSOR is mainly used to extract data in segments or chunks from the DB table. Conversely for the SELECT statement and its variants, data is directly copied into a data object in the ABAP memory.

è    Cursor Caching: This reduces the over head of calls to the RDBMS by using the same cursor for all the queries that retrieve the matching tuples of inner relation in a NESTED SELECT.

è    Generally the CURSOR is used in cases where we need to get data in chunks; similar to the one we get BW-R/3. In BW-R/3 interface which works technically by issuing several RFC calls (until the whole result set is transferred). Certainly you can not use SELECT ENDSELECT here as it will open the same select every time the FM called. So, in that case you can declare OPEN CURSOR with HOLD and transfer every time next portion of query result set as the current cursor position will be saved between FM calls.

è    The difference between these ways of extracting data will become prominent when large amount of data is being extracted and a single record could mean a lot of data. 

Technical Analysis and Statistics for the CURSOR and SELECT Statements


This is the code extract that demonstrates the time taken to fetch records from the table VBAK.

GET RUN TIME FIELD F1.
SELECT VBELN ERNAM VBTYP AUART INTO CORRESPONDING FIELDS OF ITAB
FROM VBAK UP TO 100 ROWS.
APPEND ITAB.
ENDSELECT.
GET RUN TIME FIELD F2.

f3 = f2 - f1.
WRITE:/'Runtime test1 F3',F3.

GET RUN TIME FIELD F1. "Fetches runtime for executing the data.

"This is to Open the Cursor for the SELECT statement.
OPEN CURSOR c_cursor FOR

SELECT VBELN ERNAM VBTYP AUART
FROM VBAK UP TO 100 ROWS.

"Move the data from the Cursor into the target area.
FETCH NEXT CURSOR c_cursor INTO
TABLE itab.

"Close the Cursor to complete the operation.
CLOSE CURSOR c_cursor.

GET RUN TIME FIELD F2. "Fetches runtime info from previous RUNTIME command.

f3 = f2 - f1.
WRITE:/'Runtime test2 F3',F3. " Time taken to execute statements between the two RUNTIME statements.

Data from the test run can be found in the following screen shots. ( 4 Test runs taken )

Runtime test1 F3=> Runtime performance for SELECT...ENDSELECT
Runtime test2 F3=> Runtime performance for CURSOR

Test Run 1: Selecting 10 rows from VBAK table.        1095         548
Test Run 2: Selecting 100 rows from VBAK table.      1629        1721
Test Run 3: Selecting 1000 rows from VBAK table.    18198      12160
Test Run 4: Selecting 10000 rows from VBAK table.  252172    185468

Performance Analysis for CURSOR Vs SELECT Statement

This is the code extract that demonstrates the time taken to fetch records from the table VBAK. (Using a SELECT statement into an internal table with Header Line)

GET RUN TIME FIELD F1.
GET RUN TIME FIELD F1.
SELECT VBELN ERNAM VBTYP AUART INTO TABLE ITAB
FROM VBAK UP TO 100 ROWS.
GET RUN TIME FIELD F2.

f3 = f2 - f1.
WRITE:/'Runtime test1 F3',F3.

GET RUN TIME FIELD F1. "Fetches runtime for executing the data.

"This is to Open the Cursor for the SELECT statement.
OPEN CURSOR c_cursor FOR

SELECT VBELN ERNAM VBTYP AUART
FROM VBAK UP TO 100 ROWS.

"Move the data from the Cursor into the target area.
FETCH NEXT CURSOR c_cursor INTO
TABLE itab.

"Close the Cursor to complete the operation.
CLOSE CURSOR c_cursor.

GET RUN TIME FIELD F2. "Fetches runtime info from previous RUNTIME command.

f3 = f2 - f1.
WRITE:/'Runtime test2 F3',F3. " Time taken to execute statements between the two RUNTIME statements.

Data from the test run can be found in the following screen shots. (4 Test runs taken)

Runtime test1 F3=> Runtime performance for SELECT...INTO...
Runtime test2 F3=> Runtime performance for CURSOR

Test Run 5: Selecting 10 rows from VBAK table. 964,                    582
Test Run 6: Selecting 100 rows from VBAK table. 1686,               1533
Test Run 7: Selecting 1000 rows from VBAK table. 33,382,         11,350
Test Run 8: Selecting 10000 rows from VBAK table. 220,920,     113,165

Please find the supporting Screen Shots in the attachment.

  1. In program ,Declaration of f1,f2,f3 and cusror name ( c_cursor ) missing.please update it.