documentation on performance tuning

INTERNAL TABLES

The following are the guidelines for working with the Internal Tables:

Read Table
Read statement fetches the record from the internal table using implicit key or explicit key. When no key or index is specified for the search criteria the key in the WA of the internal table is used implicitly for searching. SAP recommends explicit search criteria. Here the key or index is explicitly specified which is used for searching the records from internal table. This is faster than an implicit search.

When reading a single record in an internal table, the READ TABLE WITH KEY is not a direct READ. Therefore, SORT the table and use READ TABLE WITH KEY BINARY SEARCH.
Do not use the following statement:-
Select matnr from mara
Into table i_mara
Where matnr in s_matnr.

Select matnr werks from marc
Into table i_marc
For all entries in i_mara
Where matnr eq i_mara-matnr.

Loop at I_mara.
-----------
Read table i_marc with key matnr = I_mara-matnr.
-----------
Endloop.

Instead use the following statement:-
Select matnr from mara
Into table i_mara
Where matnr in s_matnr.

Select matnr werks from marc
Into table i_marc
For all entries in i_mara
Where matnr eq i_mara-matnr.

Sort I_marc by matnr.
Loop at I_mara.
-----------
Read table i_marc with key
matnr = I_mara-matnr
binary search.
-----------
Endloop.


Always read the required fields using TRANSPORTING f1 f2 …
Do not use the following statement:-
data: i_vbak like vbak occurs 0 with header line.
data: i_vbap like vbap occurs 0 with header line.

Sort I_vbap by vbeln.
Loop at i_vbak.
----------
read table i_vbap with key
vbeln = i_vbak-vbeln binary search.
If sy-subrc = 0 and i_vbap-posnr = ‘00010’.
----------
----------
endif.
----------
Endloop.

Instead use the following statement:-
data: i_vbak like vbak occurs 0 with header line.
data: i_vbap like vbap occurs 0 with header line.

Sort i_vbap by vbeln.
Loop at i_vbak.
----------
read table i_vbap transporting posnr with key
vbeln = i_vbak-vbeln binary search.
If sy-subrc = 0 and i_vbap-posnr = ‘00010’.
----------
----------
endif.
----------
Endloop.


Use with key when reading from an internal table.
Do not use the following statement:-
Loop at i_mara.
--------------
--------------
i_marc-matnr = i_mara-matnr.
read table i_marc.
--------------
--------------
Endloop.

Instead use the following statement:-
Loop at i_mara.
--------------
--------------
read table i_marc with key
matnr = i_mara-matnr.
--------------
--------------
Endloop.


Sort Statement

SORT [Ascending/Descending] [As text] [Stable]
By Ascending/descending [As text]
…
By Ascending/descending [As text]
[As text] is used to sort the table alphabetically.

A linear search on internal table with large record size or large record numbers is time consuming.

It is always a good practice to search a record by binary search after sorting. The difference is felt especially in the production environment where the live data is usually huge.

When an internal table is sorted without specifying the keys the default is used. Hence specify the fields to be sorted, which is more efficient than SORT ITAB.

Sorting an Internal Table, though done in the memory, is a unnecessary load on the processor. Try to fill the table with sorted values. If the SORTED BY clause cannot be used in the select statement, then Sorting the Internal Table is inevitable.

APPEND to SORTED BY
Use SORTED clause when:
There are less than 100 rows.
Rows are already in sorted order.

If an internal table must be sorted and the above cannot be satisfied, fill the table in unsorted order and then sort specifying key fields.

To remove duplicates, if required, collect the rows from the sorted table into an auxiliary table.

As of release 4.0 there are new types of internal tables like SORTED and HASHED which can be effectively used to further reduce the search time and processing time.

Specify keys when sorting an Internal table SORT INT_TAB BY COL1, COL2
Do not use the following statement:-
Data : begin of i_mara occurs 0,
ersda like mara-ersda,
ernam like mara-ernam,
laeda like mara-laeda,
aenam like mara-aenam,
vpsta like mara-vpsta,
End of i_mara.

-----------
-----------
Sort i_mara.
Loop at i_mara.
-----------
Endloop.

Instead use the following statement:-
Data : begin of i_mara occurs 0,
ersda like mara-ersda,
ernam like mara-ernam,
laeda like mara-laeda,
aenam like mara-aenam,
vpsta like mara-vpsta,
End of i_mara.

-----------
-----------
Sort i_mara by matnr aenam.
Loop at i_mara.
-----------
Endloop.



INITIAL SIZE

This size does not belong to the data type of the internal table, and does not affect the type check. You can use the above addition to reserve memory space for table lines when you declare the table object.

When this initial area is full, the system makes twice as much extra space available up to a limit of 8KB. Further memory areas of 12KB each are then allocated.

You can usually leave it to the system to work out the initial memory requirement. The first time you fill the table, little memory is used. The space occupied, depending on the line width, is 16 <= <= 100.


Append Statement
Use APPEND LINES OF [FROM ] [TO ] TO
When appending fixed lines from an internal table to a new internal table.

Collect Statement
When working with internal tables, remember that the COLLECT statement can be very CPU intensive. When COLLECTing on an internal table with greater than 50-60 entries, use the following alternative code.

READ TABLE ITAB WITH KEY NEW-KEY BINARY SEARCH.
CASE SY-SUBRC.
WHEN 0. NEW-AMT = NEW-AMT + ITAB-AMT.
MOVE ...
MODIFY ITAB INDEX SY-INDEX.
WHEN 4. MOVE ...
INSERT ITAB INDEX SY-INDEX.
WHEN 8. MOVE ...
APPEND ITAB.
ENDCASE.

For internal tables with less than 50 entries the COLLECT is more efficient.

Do not perform any of the following functions when internal table is populated using Collect statement: -
Append, Insert, Modify, Delete, Sort, Refresh, Import, and Select into table/appending table. They invalidate the collect statement.

DELETE
Where appropriate using the following syntax, task of deleting a set of lines can be transferred to the kernel.
DELETE itable [FROM ...] [TO ...] WHERE ...

If possible, WHERE should be used together with FROM... and/or TO... to enhance performance.

The performance gain when using
DELETE itable WHERE....
Instead of
LOOP AT table WHERE ...
DELETE itable.
ENDLOOP.
Increases with the number of entries the internal table contains and the number of lines to be deleted.

When deleting adjacent duplicates use COMPARING fields as far as possible.
E.g. DELETE ADJACENT DUPLICATE ENTRIES from
COMPARING F1 F2…

MOVE
When records a and b have the exact same structure, it is more efficient to MOVE a TO b than to MOVE-CORRESPONDING a TO b.

MOVE BSEG TO *BSEG.
Is better than
MOVE-CORRESPONDING BSEG TO *BSEG.

Explicit Work area
Avoid unnecessary assignments to the header line when using internal tables with a header line.

For example,
"APPEND wa TO itab."
is approximately twice as fast as
"itab = wa. APPEND itab.”

The same applies to COLLECT and INSERT.
Whenever possible, use statements that have an explicit work area.
APPEND workarea TO itable.
INSERT workarea INTO itable.
COLLECT workarea INTO itable.
MODIFY itable FROM workarea.
READ TABLE itable INTO workarea.
LOOP AT itable INTO workarea.

Exit

Use EXIT statement to jump out of the loop, when the complete processing is done.

Where


Use WHERE clause when processing internal tables with the loop statement.


Nested Loops

Avoid nested looping on the internal tables. They eat up processor time.
If nested loops are unavoidable then,
Avoid the following:
SORT: I_vbak by vbeln, I_vbap by vbeln.
Loop at I_vbak
Loop at I_vbap where vbeln = I_vbak-vbeln.
…
Endloop.
Endloop.

Instead use:
SORT: I_vbak by vbeln, I_vbap by vbeln.
Loop at I_vbak
Read table I_vbap with key vbeln = I_vbak-vbeln
binary search transporting no fields.
Loop at I_vbap from sy-tabix.
If I_vbap-vbeln <> I_vbak-vbeln.
Exit.
EndIf.
Endloop.
Endloop.

Check

Do not use the CHECK construct within Loop … Endloop. The end condition for check statement varies with the type of loop structure. For example within loop … endloop it moves to the next loop pass, whereas in form … endform it terminates the subroutine. Thus the outcome may not be as expected. It is always safe to use If … Endif.
Do not use the following statement:-
Loop at i_mara.
Check i_mara-matnr = ‘121212’
-------------
-------------
Endloop.

Instead use the following statement:-
Loop at i_mara.
If i_mara-matnr = ‘121212’
-------------
-------------
Endif.
Endloop.


Control Break Statements

SAP recommends not to use control break events in loop statements which have a Where Clause. The outcome of the results cannot be predicted correctly. However it is a general practice to use these control break events even in loop statements which have a Where Clause. It is always a good practice to avoid control break events in such cases. If the logic is complicated without events then populate a new internal table, which has the required records, and then use events with it.
Do not use AT events in Loop … Endloop having From or To or Where condition.
Do not use the following statement:-
Loop at i_vbak where
vbeln > ‘9000123’ and vbeln < ‘9000132’.

At new vbeln.
-------------
-------------
EndAt.
--------------
--------------
Endloop.

Instead use the following statement:-
Append lines of I_vbak to I_vbak_new
where vbeln > ‘9000123’ and vbeln < ‘9000132’.

Loop at i_vbak_new.
At new vbeln.
-------------
-------------
EndAt.
--------------
--------------
Endloop.


Other ABAP Statements

The following should be avoided in the ABAP/4 statements to improve efficiency:
If Condition
When coding IF tests, nest the testing conditions so that the outer conditions are those which are most frequently true. This will ensure minimal code execution. Similarly, for logical expressions with ‘AND’, place the most likely false first and for the OR, place the most likely true first.

Case Statement

When testing fields "equal to" something, one can use either the nested IF or the CASE statement. The CASE is better for two reasons. It is easier to read and after about five nested IFs the performance of the CASE is more efficient.

This feature is also available in SAP Tips & Tricks. As can be seen the time measured for the same logical units of code using if… elseif … endif is almost twice that of case … endcase.

It is always advisable to use case … endcase as far as possible.
Note: CASE statement used in place of IF where field checked for > 2 values.

Describe Statement

DESCRIBE TABLE [LINES ] [OCCURS ] [KIND ]

To find out how many entries are in an internal table use DESCRIBE.

DESCRIBE TABLE ITAB LINES CNTLNS.
Is more efficient than
LOOP AT ITAB.
CNTLNS = CNTLNS + 1.
ENDLOOP.

Type Conversions
The processor takes additional time to convert from one data type to another.

Type ‘P’ fields
Unless rounding errors are not avoidable, do not use ‘packed’ data variables.

Field Length
To find out the length of a field use the string length function.

FLDLEN = STRLEN (FLD).
Is more efficient than
IF FLD CP ‘* #’.
ENDIF.
FLDLEN = SY-FDPOS.

Arithmetic Operators
Use symbols for arithmetic operators instead of characters for better performance.
Example : use <> instead of ne .

Use of tables
Internal tables vs. field groups:
Using internal tables is more efficient than field groups and should be used when possible.
If the volume of data is very much, field groups are more efficient compared to internal tables in terms of memory management.

ASSIGN
Use of field symbols is discouraged unless necessity dictates. Field symbols, when used, should be documented in program comments when defined and whenever used. Always have a type declaration for field symbols.

AT PFnn
Use the ‘ AT USER COMMAND’ instead of ‘AT PFnn’. This ensures proper response to the user command and is more legible.

CHECK
Use check statements whenever possible instead of nested IF’s.

User Interface (GUI)
GUI statuses should be used for interactive report programs and online programs. Use menu bar linking whenever possible to build consistent GUI statuses for screens within a module pool.

CHECK, EXIT, REJECT, STOP & CONTINUE
Use these statements to suspend processing and/or skip remaining unnecessary processing for improved performance.


Subroutines
Whenever values need to be passed in a subroutine have type declarations assigned to the formal parameters. If no specific type declaration is possible then use TYPE ANY. This improves the performance. It is also recommended by SAP and can be noticed during extended program check (EPC) for the program.

E.g. Do not use the following statement:-
perform type_test using p_var1 p_var2 p_var3.
------------

form type_test using p_var1 p_var2 p_var3.
-------------
endform.

Instead use the following statement:-
perform type_test using p_var1 p_var2 p_var3.
-------------

form type_test using p_var1 type c
p_var2 type any
p_var3 like mara-matnr.
-------------
endform.

When modularizing your program, use FORMS rather than FUNCTIONS whenever practical. A statement of PERFORM FORM <..> requires significantly less resources and time than does a CALL FUNCTION <..>.

Field-Symbols
This is similar to type declarations for subroutines. Except that type declarations need to be maintained for field-symbols.

E.g. Do not use the following statement:-
field-symbols: , , .

Instead use the following statement:-
field-symbols: type c,
like mara-matnr,
like marc-werks.


Miscellaneous Methods to Enhance Performance
1. Always declare variables to avoid SAP make type conversions
2. Use while loop rather than DO/ENDDO
The Do … Enddo loop does not have a terminating condition. This has to be handled explicitly within the loop construct. This has some affect on the performance. On the other hand While … Endwhile loop has a condition to satisfy before entering the loop. Hence will improve the performance and is also safe to use.

Do not use the following statement:-
Do.
If count > 20.
Exit.
Endif.
--------------
--------------
Count = count + 1.
Enddo.

Instead use the following statement:-
While ( count < 20 ).
-------------
-------------
Endwhile.

3. Remove Break-point from the code.
It is observed that break points are hard coded in the program during testing. Some are soft break points some are hard coded and also user specific. These are left in the program during transports and cause production problems later.


DATABASE SELECTION

Techniques for efficient Database selection
The following basic guidelines need to be considered while retrieving data from the database:
Avoid unnecessary database accesses
Keep the number of data records small
Reduce information transfer to application server
Reduce the load on the database engine
Use database locks and SAP enqueue.

Basic thing to be kept in mind is:
Get the data in one single go as far as possible, thereby reducing the network traffics.


Reduce Information transfer to Application server
Specify the individual column (field) names of the data you want to retrieve in the Select/ Get statement.
E.g. Use: SELECT single MATNR ERSDA
INTO (V_MATNR, V_ERSDA)
FROM MARA
WHERE MATNR EQ ‘000101234567890123’.

Instead of: SELECT single * FROM MARA
WHERE MATNR EQ ‘000101234567890123’.

In this example, only 2 fields are transported back to the work area for each matching record. This greatly reduces the amount of data transfer (network traffic) – especially for tables, which have a large number of fields/large record size.

Similarly, Use: Get fields f1 f2 … fn.
Instead of: Get .

Use Aggregate functions provided in the Select statement.
By using these, the DBMS makes the computations instead of transferring all the data to the application.
E.g.1 Use: SELECT COUNT (*)
FROM MARC
WHERE MATNR EQ ‘000101234567890123’.

SY-DBCNT will hold the number of matching records found.

Instead of: SELECT * FROM MARC
WHERE MATNR EQ ‘000101234567890123’.
ADD 1 TO V_COUNT.
ENDSELECT.

E.g.2 Use: SELECT SUM (TRAME)
INTO V_TOTAL_IN_TRANS
FROM MARC
WHERE MATNR EQ ‘000101234567890123’.

Instead of: SELECT * FROM MARC
WHERE MATNR EQ ‘000101234567890123’.
V_TOTAL_IN_TRANS = V_TOTAL_IN_TRANS + MARC-TRAME.
ENDSELECT.

Use update statement sparingly.
Only update the columns, which are changed, and do not overwrite the entire line.


Keep the number of data records small
Always use the WHERE clause in the corresponding SQL statement.
An Application should read only those lines of the table that are necessary for the processing. Therefore formulate filter condition not through CHECK statements, rather through part of WHERE statements
E.g. Always use option 1 instead of option 2.

Option 1: SELECT MATNR
INTO TABLE IT_MARA
FROM MARA
WHERE MATNR LIKE ‘23%’.

Option 2: SELECT MATNR
INTO MARA-MATNR
FROM MARA.
CHECK MARA-MATNR+0(2) NE ‘23’.
MOVE MARA-MATNR TO IT_MARA-MATNR.
APPEND IT_MARA.
CLEAR IT_MARA.
ENDSELECT.

Use the indexes of the relevant database tables to make your WHERE clause more efficient, by checking all index fields for equality (EQ, =) and using the AND operator.
E.g.1 Use: Select * From dbtab
Where field1 = value1 and
field2 = value2.
...
EndSelect.
Note: The fields field1, field2 etc are the primary key fields of the table. Primary Index is used for searching.

E.g.2 Use: An index to the table EXTAB containing the fields FIELD1, FIELD2,
FIELD3 and FIELD4 in this sequence are to be defined. The table is to be accessed using the SELECT statement:

SELECT * FROM EXTAB
WHERE FIELD1 = X1 AND
FIELD2 = X2 AND
FIELD4= X4.

Since FIELD3 is not specified more precisely, sorting of the index functions only up to FIELD2. If the database system accesses the data using this index, it will quickly be able to access all records for which FIELD1 = X1 and FIELD2 = X2 are valid. It will then have to select all the records for which FIELD4 = X4 from this set.

If FIELD3 can be provided, the index can be used to full extent.

SELECT FIELD3 FROM BSPTAB
INTO TABLE ITAB
WHERE FIELD1 = VALUE1.

SELECT * FROM EXTAB
FOR ALL ENTRIES IN ITAB
WHERE FIELD1 = X1 AND
FIELD2 = X2 AND
FIELD3 = ITAB-FIELD3 AND
FIELD4= X4.
...
ENDSELECT.

Define a database view via the ABAP/4 Dictionary, which identifies the tables and fields that you are interested in. Then SELECT from the view rather than the database table(s).
E.g. SELECT *
FROM Z_M_MARC
WHERE MATNR EQ ‘000101234567890123’.

This is preferable if multiple programs require the same data from the same table(s). If the set of data in these programs requires changes, then the developer need only change the definition of the database view, rather than modify every SELECT statement – i.e. greater reusability and maintainability.

Avoid using complex WHERE clauses since the system has to break them down into several individual statements for the database system

If all you are interested in is retrieving all possible values for a given table field (i.e. you do not want duplicates), then SELECT DISTINCT will provide that capability and will also restrict the amount of data passed back to the application to only the set of unique values.
Instead of using DISTINCT, the application might remove the duplicates by itself, e.g. using DELETE ADJACENT DUPLICATES. This should only be used if a small number of duplicates are expected.
E.g. SELECT DISTINCT WERKS INTO V_PLANT FROM MARC.

ENDSELECT.

In this example, a value will be returned for each unique value of the field WERKS (Plant). If there are 3 unique plants in table MARC, will be performed 3 times only, with each value of WERKS being placed into the work area V_PLANT. Each plant may, in reality, be present on multiple records, but this method returns only unique values.

Be careful while restricting data on NULL values.
You will not find a record in a database table, if field has a Null Value and you are using Following WHERE-clauses:
WHERE fieldn = 0.
WHERE NOT fieldn = 0.
WHERE fieldn < 5.
WHERE fieldn > 5.
WHERE fieldn = SPACE.
WHERE fieldn <> SPACE.

If you want to read records with Null Values, you have to use IS NULL with your WHERE-clause:
WHERE fieldn = 0 OR fieldn IS NULL.
WHERE fieldn < 5 OR fieldn IS NULL.
WHERE fieldn = SPACE OR fieldn IS NULL.

Where possible, avoid accessing the same data more than once (for example, by using SELECT before an UPDATE or DELETE statement).

Never use Nested select loops. Instead consider options: Views, Joins or select statement with for ALL ENTRIES OPTION.
E.g. Use: Select * From dbtab1
into table Itab
where field1 = value1.

Select * from dbtab2
for ALL Entries In Itab
where field2=value2.
...
EndSelect.

Instead of: Select * From dbtab1 where field1=value1.
Select * from dbtab2 where field2=value2.
...
EndSelect.
EndSelect.



Some Guidelines while creating Indexes

The speed of access provided by this index is heavily dependent on how closely the dataset selected via the index corresponds to the dataset to be finally selected.

Consequently, the sequence of the fields in the index is decisive in determining the speed with which data records are accessed.

Fields, which frequently contains constants in many selections, should be located at the beginning. An index assists selection only up until the first unspecified field. Fields should be included in an index only if they significantly restrict the set of data selected.
Eg: The following selection is often made from an address file ADRTAB:
SELECT * FROM ADRTAB WHERE TITLE = 'Prof.' AND
NAME = X AND
FIRSTNAME = Y.
In an index containing the fields NAME, FIRSTNAME and TITLE, the field TITLE would rarely narrow down further the records specified via name and first name. It would not be useful, therefore, to include this field in such an index. An index containing only the field TITLE might be useful, for example, if it is often necessary to select all the professors.

The primary key of a database table is automatically its primary index. If selections are frequently made involving attributes that are not contained in the primary index, you can also create secondary indexes for a database table in the ABAP Dictionary. However, certain considerations need to be made while creating secondary indexes.

Please note that any additional indexes you create may place an additional load on the system, since they have to be adjusted each time a change is made to the table contents. Every additional index therefore slows down the insertion of records in the table. Tables in which entries are frequently written should generally therefore have only a few indexes.

Even if a suitable index exists for a selection, it may not always be used. The index selected depends on the database system optimizer in use. You should usually therefore check whether an index is in use.

The indexes for a table should therefore be as distinct as possible – i.e., they should have as few fields in common as possible. If two indexes for a table have many fields in common, this can complicate the choice of the most selective index by the optimizer.

As of release 4.5 it is now possible to specify the index which should be used in the select clause. The addition %_hint is used to achieve this. This improves the performance as the index is known before hand and need not be determined by the system. Also it helps the system in cases where none of the indexes exactly matches with the fields in the where clause of the select statement.

If possible, avoid using the NOT operator in the WHERE clause, because it is not supported by database indexes, Invert the logical expression instead. Nevertheless, specify if it can avoid useless transfers between database and application.


Reduce the load on the database engine
If all the primary keys of a table can be provided in the where clause it is a good practice to use SELECT SINGLE.. rather than using SELECT UP TO 1 ROWS etc.

E.g. Do not use the following statement:-
Select matnr up to 1 rows from mara
into mara-matnr
where matnr = itab-matnr.

Instead use the following statement:-
Select single matnr from mara
into mara-matnr
where matnr = itab-matnr.

Cluster and pool tables store data from various data objects and so is the most convenient point for accessing data. But this has adverse affect on the performance. It is always a good practice to avoid reading directly from cluster and pool tables.
Identify all the transparent tables, which store the required data. Though the data cannot be retrieved from one table it is much faster than retrieving from cluster or pool tables.

E.g. Do not use the following statement:-
Select belnr buzei audat
From bseg
Into ( bseg-belnr,
bseg-buzei,
bseg-audat )
Where belnr in s_cust.
Endselect.

Instead use the following statement:-
Select belnr buzei audat
From bsak
Into ( bsak-belnr,
bsak-buzei,
bsak-audat )
Where belnr in s_cust.
Endselect.

Use the 'into' syntax and push data directly into internal tables rather than using the row by row 'append' approach. Again this is an example of thinking in terms of 'sets' rather than 'rows'. Old-fashioned databases only knew about rows. Modern relational databases work better when dealing with sets of data. Try to work always in sets.
The order of the fields in the where clause of the select must be in the same order as the order in the Index. (Oracle limitation but routinely should be used against all databases).

The addition INTO CORRESPONDING FIELDS in the INTO clause of the SELECT Statement is only effective for large amounts of data, because the time required to compare the field names is otherwise too great.

There are two ways to get ordered data. The data can be selected using the addition ORDER BY from the database. This is advisable for large amounts of data (>10 MB) because the database system is the only component in R/3 with large resources. Ensure that the ORDER BY can use an index. When a small amount of data is to be sorted, it can be done with ABAP statement SORT.

Do not use a SELECT statement to confirm existence before UPDATE or DELETE is used. Directly use UPDATE or DELETE and then check the result with SY-SUBRC.
Always use collective Updates. Do not use single line updates.
E.g. Use: Update dbtab
Set field = field + delta
Where field1=value1.

Instead of: Select * from
Where field1=value1.
dbtab-field = dbtab-field + delta
update dbtab.
EndSelect.

As far as possible, avoid using Insert, Append, Modify, Update or Delete within the loop..endloop. Use array-updating functionality and Insert, Append, Modify, Update or Delete at one go.
E.g. Use: INSERT dbtable FROM TABLE itable
ACCEPTING DUPLICATE KEYS.
IF NOT SY-SUBRC IS INITIAL.
... Error handling
ENDIF.

Instead of: LOOP AT itable.
INSERT INTO dbtable VALUES itable.
ENDLOOP.

When multiple records are to be inserted into a table, then use the first form as shown above rather than loop into the internal table and inserting/updating single records.

Be Careful with range table. The size of statement might exceed the limit depending on the size of the range table!
E.g.: Data: Ranges ran_field1 for dbtable1-field1.
Select * from dbtab1 into table itab1.
ran_field1-sign = ‘I’.
ran_field1-option = ‘EQ’.

Loop at itable1.
Move itable1-field1 To ran_field1-low.
Append ran_field1.
EndLoop.
Select * from dbtable2 where field2 in ran_field1.
...
EndSelect.

Avoid unnecessary database accesses
Don’t use identical select statements.
E.g.1 To get Material Descriptions
Use: LOOP AT IT_VBAP.
READ TABLE IT_MAKT KEY MATNR = IT_VBAP-MATNR
TRANSPORTING MAKTX.
IF SY-SUBRC NE 0.
SELECT MATNR MAKTX FROM MAKT
APPENDING TABLE IT_MAKT-MAKTX
WHERE MATNR EQ IT_VBAP-MATNR.
ENDIF.
MOVE: IT_MAKT TO IT_VBAP-MAKTX.
ENDLOOP.

Instead of: LOOP AT IT_VBAP.
SELECT SINGLE MAKTX INTO IT_VBAP-MAKTX
FROM MAKT
WHERE MATNR EQ IT_VBAP-MATNR.
ENDLOOP.

E.g.2 Use: Select vbeln vkorg from vbak
Into table i_vbak
Where vbeln in s_vbeln.

Select hkunnr from kunh
Into table i_kunh
Where vkorg in (‘IJI1’,’IJI2’,’IJI3’,’IJI4’).

Loop at i_vbak.
Case i_vvbak-vkorg.
When ‘IJI1’.
Read table i_kunh where vkorg = i_vbak-vkorg.
-----------
-----------
When ‘IJI2’.
Read table i_kunh where vkorg = i_vbak-vkorg.
-----------
-----------
When ‘IJI3’.
Read table i_kunh where vkorg = i_vbak-vkorg.
-----------
-----------
When ‘IJI4’.
Read table i_kunh where vkorg = i_vbak-vkorg.
-----------
-----------
Endcase.
Endloop.

Instead of: Select vbeln vkorg from vbak
Into table i_vbak
Where vbeln in s_vbeln.

Loop at i_vbak.
Case i_vbak-vkorg.
When ‘IJI1’.
Select hkunnr from kunh
Into table i_kunh
Where vkorg = ‘IJI1’.
-------------
-------------
When ‘IJI2’.
Select hkunnr from kunh
Into table i_kunh
Where vkorg = ‘IJI2’.
-------------
-------------
When ‘IJI3’.
Select hkunnr from kunh
Into table i_kunh
Where vkorg = ‘IJI3’.
-------------
-------------
When ‘IJI4’.
Select hkunnr from kunh
Into table i_kunh
Where vkorg = ‘IJI4’.
-------------
-------------
Endcase.
Endloop.


Using Database Buffering:

Saving database tables in local buffers can save a considerable amount of time. Wherever possible, use buffered data, and only use the BYPASSING BUFFER addition where absolutely necessary e.g. Table updated at a regular frequently or a table used only once.

Note that the following additions automatically bypass the buffer:
SELECT ... BYPASSING BUFFER
Any SELECT from a view (except a projection view)
SELECT FOR UPDATE...
Any aggregate function (COUNT, MIN, MAX, SUM, AVG)
Example: SELECT MIN (field1) FROM dbtable1 WHERE ...
SELECT DISTINCT...
WHERE-clause contains ... IS (NOT) NULL
ORDER BY (other than PRIMARY KEY)
Any Native SQL statement.

Inner Joins:

An INNER join is a join where you expect that there will always be a relationship between all of the tables in the set, and if any of the relationships are missing, then you do not want to get the row.
E.g. Consider 3 imaginary tables.
Customer
MANDT (Primary Key)
CUST_CODE. (Primary Key)

Invoice
MANDT (Primary key)
INV_NUM (Primary key)
CUST_CODE (Foreign key to Customer)

Invoice_line
MANDT (PK)
INV_NUM (PK)
INV_LINE_NUM (PK)

You could construct an INNER join over these tables as follows

Customer Invoice Invoice_line
MANDT MANDT MANDT
inv_num === inv_num
cust_code === cust_code

Say you then have to report off this information.

Your report would contain ONLY those customers, which had Invoices, which had at least one line. Customers without invoices would not be included. Customers that had invoices but without a line would not be included.

This is the principle of the INNER join. All the data has to be there and be related.
Database views support ONLY the inner join concept.

Outer Joins:

In the majority of cases inner joins will actually be the requirement, but in the minority of cases you will want to use an outer join. Outer joins are useful where you may need all records from a certain table, that meet the selection criteria, and all data from another table(s), IF it exists. But if the latter data does not exist, you still want to get the first table data.

E.g. Using our imaginary tables again.
Suppose the requirement is not for an invoice report, but for a customer report, showing outstanding invoices. You want to see ALL customers, so you can't use an inner join
(Incidentally therefore you can't use a database view, database views only support INNER joins) because if you did, you would not see customers that had no invoices. So you would construct an outer join relationship as under:

Customer Invoice Invoice_line
MANDT MANDT MANDT
inv_num === inv_num
cust_code ----> cust_code

In this situation you would get all customers regardless, and their
invoices. You would only see the invoices, which had at least one line as the join
between invoice and invoice_line is still an inner join. If you wanted to be absolutely sure you could code a double outer join as under:

Customer Invoice Invoice_line
MANDT MANDT MANDT
inv_num ----> inv_num
cust_code ----> cust_code

In this situation you would see all the invoices, even if they had no lines.
In reality in the above situation you would seldom code such a well defined relationship as invoice - invoice_line using an outer join. If an invoice doesn't have a line then it is usually corrupt data.

Using outer joins places a heavy load on the database engine (although no heavier that if you coded a nested select), so be very parsimonious about the use of outer joins.

The performance of the join depends on the database optimizer used especially if there are more than two tables used for joins.

Try to give maximum number of conditions in the ON clause. This is because the ON conditions are evaluated first and the virtual table created as a result is the one on which the WHERE clause applies.

Use subqueries if possible. However, be careful while choosing the type of subquery to be written.
Example: Selects the flights for which at least one booking exists:

TABLES SFLIGHT.
SELECT * FROM SFLIGHT AS F
WHERE EXIST
( SELECT * FROM SBOOK
WHERE CARRID = F~CARRID
AND CONNID = F~CONNID
AND FLDATE = F~FLDATE ) .
WRITE:/ SFLIGHT-CARRID,
SFLIGHT-CONNID,
SFLIGHT-FLDATE.
ENDSELECT.

This is most assuredly not a Good Thing. What this is actually doing is forcing the database to do a separate selection from SBOOK for each row in sflight. For a small number of records this would probably work OK - but once again - on larger datasets it would hang. For every record in SFLIGHT a separate database operation is occurring in sbook. You might as well have coded the selections inside a loop.

This query is better written as follows;

Select distinct f~*
into table it_sflight
From sflight as F
inner join sbook as s on
S~CARRID = F~CARRID AND
S~CONNID = F~CONNID AND
S~FLDATE = F~FLDATE.

Following the above guidelines does not guarantee the most optimal selection from the database. Ultimately, it depends on the nature of the data and other associated factors.


SQL Tuning Checklist
Consolidated selections
No row by row processing
No check statements
No selections within loops
Selections are 'into' internal tables - no appends
SQL trace check completed
All programs checked to make sure that they are using the full index and in the
Correct order.
Minimum or zero number of identical selects.
Use of appropriate Indexes


Scenario

Pick details from Sales Header table for the chosen parameters, pick the subsequent matching records from line items and write the line item details in the report. While writing the line items pick up the material description from material master.

This is how normally its written:

Data : I_vbak like vbak occurs 0,
Data : I_vbap like vbap occurs 0.
Data : d_lines like sy-subrc.

Start-of-selection.

Refresh : I_vbak , I_vbap.
Select * from VBAK into table I_vbak
Where erdat eq p_erdat And
vkorg = p_vkorg And
vtweg = p_vtweg And
spart = p_spart.

Describe table I_vbak lines d_lines.

Check d_lines <> 0.

Select * from VBAP into table I_vbap
For all entries in table I_vbak
Where vbeln = I_vbak-vbeln.

Describe table I_vbap lines d_lines.

Check d_lines <> 0.

End-of-selection.

Loop at I_vbap.
Clear makt.
Select single * from makt
where spras = sy-langu and
matnr = I_vbap-matnr

Write :/ I_vbnap-vbeln,
I_vbap-psonr,
I_vbap-matnr,
Makt-matnr,
I_vbap-kwmeng.
Endloop.

Better Way :

Data : Begin of I_vbak occurs 0,
Vbeln like vbak-vbeln,
End of I_vbak.

Data : Begin I_vbap occurs 0,
Vbeln like vbap-vbeln,
Posnr like vbap-posnr,
Matnr like vbap-matnr,
Kwmeng like vbap-kwmeng,
End of I_vbap.

Data : Begin of I_makt occurs 0,
Matnr like makt-matnr,
Maktx like makt-maktx,
End of I_makt.

Data : d_lines like sy-subrc.

Start-of-selection.

Refresh : I_vbak , I_vbap , I_makt.
Select vbeln from VBAK into table I_vbak
Where erdat = p_erdat And
vkorg = p_vkorg And
vtweg = p_vtweg And
spart = p_spart.

Clear d_lines.
Describe table I_vbak lines d_lines.

Check d_lines <> 0.

Select vbeln posnr matnr kwmeng from VBAP into table I_vbap
For all entries in table I_vbak
Where vbeln = I_vbak-vbeln.

Clear d_lines.
Describe table I_vbap lines d_lines.

Check d_lines <> 0.

End-of-selection.

Sort I_vbap by vbeln matnr.

Select matnr maktx from makt into I_makt
For all entries in I_vbap
Where spras = sy-langu And
matnr = I_vbap-matnr.

Sort I_makt by matnr.

Loop at I_vbap.

Clear I_makt.
Read I_makt with key matnr = I_vbap-matnr binary search.

Write :/ I_vbnap-vbeln,
I_vbap-psonr,
I_vbap-matnr,
I_Makt-matnr,
I_vbap-kwmeng.

Endloop.



Above is a real life scenario that has been optimized.