DO:
SELECT [fields] FROM [table].
Consider using PACKAGE SIZE in SELECT statement when size of resultant itab is significant in size
- Do not use APPENDING TABLE with PACKAGE SIZE.
- The use of PACKAGE SIZE is not recommended if FOR ALL ENTRIES is being used
Don't: SELECT * FROM [table].
DO:
SELECT [fields] INTO TABLE [internal table]
Don't:
SELECT [fields] INTO CORRESPONDING FIELDS OF TABLE [internal table]
DO:
If full primary key is not available:
SELECT [field] FROM [table] UP TO 1 ROWS [structure].
If full primary key IS available:
SELECT SINGLE [field] from [table] [where]
Don't:
SELECT COUNT(*) FROM [table].
DO:
SELECT [fields] FROM [table] INTO TABLE [internal table].
SORT [internal table] BY [SORT fields]. (Sort is executed on Application server)
Don't:
SELECT [fields] FROM [table] INTO TABLE [internal table] ORDER BY [fields]. (Sort is executed on Database server)
DO:
SELECT [fields] FROM [table] INTO TABLE [internal table].
SORT [internal table].
DELETE ADJACENT DUPLICATES FROM [internal table].
Don't:
SELECT DISTINCT [field] FROM [table] INTO TABLE [internal table]. (Sort is executed on Database server. Also always bypasses the buffer)
- Only use the DISTINCT addition if there are a large number of duplicates and the set of results will be significantly reduced if you remove duplicates.
- Use DISTINCT if the selected fields are part of DB index picked by WHERE clause of the SELECT.)
DO:
OPEN SQL. (Open SQL is SAPs version of SQL. Open SQL is written to isolate the code from a specific RDBMS; thereby, creating database independence.)
Don't:
Native SQL. (Native SQL interface passes all SQL statements to the database without modifying them)
- No table logging on SAP level during Native SQL operations
- Synchronous match codes are not updated by Native SQL operations
- No synchronization of the SAP table buffer
- SQL statements become database dependent. This may cause numerous porting problems between database versions
DO:
JOINs (Executed at database level. Thus, it also bypasses the buffers)
The initial design of the SQL statement with INNER JOINs should start with the smaller table as a general rule.
- The starting table (Primary table in the join) should have specific where clause on the primary key fields or a clean secondary index.
- The starting table (Primary table in the join) should be the one with maximum filter criteria. This reduces the database I/O and disk reads. However a comparison for response time and estimated cost is suggested in this case.
Use ‘For All Entries’ technique as a second alternate to JOINs.
- The driver table must not be empty
- Keep driver table as small and unique as possible
- The selected field list must be a superset of all the key fields of the database tables used in SQL
- Avoid using on small DB tables
- Always use in conjunction with a full DB index
- The first field(s) of the DB index should refer to the fields of the driver table in the SQL WHERE clause. Do not leave gaps between index fields in the WHERE clause.
- Works best when getting a small % of data from the tables. For a large % consider getting all the data and filtering
Don't:
Nested SELECT or SELECTs inside a loop. (Connection to database stays open for each record in the outer select loop)
Logical Databases (Logical Databases use nested selects in the background)
Use LEFT OUTER JOINs with caution since they can impact performance. A LEFT OUTER JOIN produces a resulting set containing all data records from the outer table, regardless of whether matching records exist in the inner table. If there are no matching data records in the inner table, NULL values are returned for the fields in the inner table.
Other:
DO:
IS NULL, IS NOT NULL, LIKE or NOT LIKE
SELECT single
Use alternate index tables for data access
Don't:
Addition CLIENT SPECIFIED in SQL’s
SELECT SINGLE FOR UPDATE
Group BY or Having or
Aggregate Functions (AVG, MAX, MIN, SUM)
DO:
SUBQUERY:
A subquery is a SELECT command within another SELECT, specified in the WHERE condition, that checks whether data from a database table or view meets certain conditions. Subqueries are available as of SAP Release 4.0. In addition, subqueries can be used in UPDATE and DELETE statements as well.
SELECT [fields] INTO TABLE [internal table]
FROM [table]
WHERE [field] IN ( SELECT [field] FROM [table2] ).
(Subqueries are executed at the DB level, thus bypassing buffers) Select single can't be used together with subqueries
Example: Get list of open sales orders (header level).
SELECT VBAK~VBELN
FROM VBAK
INTO ITORDERS
WHERE EXISTS
( SELECT AUFNR FROM J_3ABDSI
WHERE J_3ABDSI~AUFNR = VBAK~VBELN ).
An INNER JOIN here would result in schedule line level results, resulting in greater network i/o. A subsequent DELETE ADJACENT duplicates on the internal table is needed to report on the header level.
Avoid using INNER JOINs between big transactional tables and with a small config table. Instead use subquery like in the example below
Example:
SELECT MVKE~MATNR
FROM MVKE INNER JOIN MARC ON
MARC~MATNR = MVKE~MATNR AND
WHERE
MVKE.VKORG = 'ABCD' AND
MVKE.VTWEG = '00' AND
MARC.WERKS = 'A100' AND
EXISTS ( SELECT * FROM TVKWZ
WHERE TVKWZ.VKORG = MVKE.VKORG AND
TVKWZ.VTWEG = MVKE.VTWEG AND
TVKWZ.WERKS = MARC.WERKS )
DO:
INDICES:
An index will not be used if the following operators are present on the indexed fields in your WHERE clause:
- NOT or NE or []
- LIKE ‘%pattern’
- IS NULL
- IS NOT NULL
- NOT IN
Specify field values without gaps. In other words, if a table has field1, field2, and field3 as the index, your where clause should not have only field1 and field3. Leaving out field2 causes the leading index for the only the first field to be used. If the where clause contained field1 and field2 but not field3, the index would include field1 and field2.
DO:
DATABASE UPDATES:
Do an array of updates in 1 statement
UPDATE [table] FROM TABLE [internal table].
- SY-SUBRC: Contains 0 if all updates were successfully executed
- SY-DBCNT: Contains the number of successfully updated records
The Array update function can't be combined with UPDATE SET
- If a large number of fields are changed in multiple records =] Array update
- If a small number of fields are changed in one record =] Update set
Don't:
LOOP AT [internal table]
UPDATE [DB table] SET [field] = [var]
WHERE [field] = [val].
ENDLOOP.
