Do's & Don't in ABAP programming using SELECT statement
Objective:
The idea behind this document came when I was reading very excellent blog about OpenSQL A complete guide to OpenSQL statements which helps developers in writing query using OpenSQL.
However that blog does not talks much about the common mistakes which mostly developers do in writing query results in rework for the defects/performance when object is delivered.
So, I collected some common errors which can be avoided during the development so that an efficient code is delivered with high performance.
Let me know if there is more we can add here!
Usage of SELECT statement:
a) SELECT with Primary key/Secondary Index
Ensure that data is selected with Primary key /Secondary index support with the fields in the select statement’s WHERE clause in the same order as fields in the index.
| Wrong Approach | Right Approach |
|---|---|
SELECT * FROM mara INTO TABLE itab WHERE mtart EQ 'ROH' AND matnr EQ '000001102'
|
SELECT * FROM mara INTO TABLE itab WHERE matnr EQ '000001102' AND mtart EQ 'ROH'
|
b) Avoid SELECT with Negative Clause
The database system only supports queries that describe the result in positive terms, for example, EQ or LIKE. It does not support negative expressions like NE or NOT LIKE.
| Wrong Approach | Right Approach |
|---|---|
SELECT * FROM mara INTO TABLE itab WHERE mtart EQ 'ROH' AND ersda NE sy-datum
|
SELECT * FROM mara INTO TABLE itab WHERE mtart EQ 'ROH' AND ( ersda LT sy-datum OR ersda GT sy-datum )
|
NOTE: If possible, avoid using the NOT operator in the WHERE clause, because it is not supported by database indexes; invert the logical expression instead.
c) SELECT with WHERE Clause fields – Reading single record
When using select for a single record access, always keep in mind, is selection based on Primary key or Non Primary key field?
i) When based on Non-Primary Key
| Wrong Approach | Right Approach |
|---|---|
SELECT SINGLE matnr FROM mara INTO lv_matnr WHERE vbeln = '0001500080'.
|
SELECT matnr UP TO 1 ROWS FROM mara INTO lv_matnr WHERE vbeln = '0001500080'.
ENDSELECT.
|
ii) When based on Primary Key
| Wrong Approach | Right Approach |
|---|---|
SELECT audat vbtyp UP TO 1 ROWS FROM vbak INTO (lv_audat, lv_vbtyp)
WHERE vbeln = '0001500080'
|
SELECT SINGLE audat vbtyp FROM vbak INTO (lv_audat, lv_vbtyp) WHERE vbeln = '0001500080'
|
d) Select with JOIN Clause
Select with JOINS instead of nested select statements to read data from logically related tables. If the number of records to be fetched in a JOIN clause is more than five ensure that the join fields are the key fields. Always limit tables in a join to three tables
| Wrong Approach | Right Approach |
|---|---|
SELECT vbeln auart FROM vbak INTO ls_vbak
WHERE vbeln BETWEEN '0000000001' AND '0000002000'.
SELECT posnr matnr kwmeng FROM vbap INTO ls_vbap
WHERE vbeln = ls_vbak-vbeln AND matkl = '000000001'.
SELECT etenr FROM vbep INTO ls_vbep
WHERE vbeln = ls_vbak-vbeln AND posnr = ls_vbap-posnr.
* ..... logic here ........*
ENDSELECT.
ENDSELECT.
ENDSELECT.
|
SELECT v~vbeln v~auart p~posnr p~matnr p~kwmeng e~etenr
INTO TABLE itab
FROM vbak AS v INNER JOIN vbap
AS p ON v~vbeln = p~vbeln
INNER JOIN vbep AS e ON p~vbeln = e~vbeln
AND p~posnr = e~posnr
WHERE v~vbeln BETWEEN '0000000001'
AND '0000002000'.
|
NOTE: When using views or the join construct the database can also better optimize the disk accesses
e) SELECT * Vs. SELECT fields
Always select fields only instead of select *, when number of fields < 1/3rd of total fields in table or no. of fields getting selected is less than or equal to 16.
| Wrong Approach | Right Approach |
|---|---|
SELECT * FROM spfli INTO ls_spfli WHERE carrid ='AA'.
* ..... logic here ........*
* ..... logic here ........*
|
SELECT carrid connid cityfrom INTO (lv_carrid, lv_connid, lv_cityfrom) FROM spfli WHERE carrid = 'AA'.
* ..... logic here ........*
* ..... logic here ........*
|
f) SELECT using Aggregate functions
Select using an aggregate function (SUM, MIN, MAX, COUNT) instead of computing the aggregates yourself.
For Example: To list the number of accounting document headers (rows in BKPF) with document type ‘EM’ for FY1999, for each of the company codes
| Wrong Approach | Right Approach |
|---|---|
SELECT mandt bukrs FROM bkpf WHERE blart = 'EM'
AND gjahr = '1999'.
* ..... logic here to count .....*
|
SELECT mandt bukrs COUNT(*) FROM bkpf WHERE blart = 'EM' AND gjahr = '1999' GROUP BY mandt bukrs.
|
NOTE: When you do the calculations in your program, you need to read all data rows from the d/base into the program (over the network) causing a considerable load on the network.
g) SELECT INTO Table Version
Always prefer to use SELECT INTO TABLE Clause rather than using SELECT with Append statement.
| Wrong Approach | Right Approach |
|---|---|
SELECT * FROM mara INTO ls_mara WHERE matnr BETWEEN '000000000060000000' AND '000000000060000335'.
APPEND ls_mara TO itab.
ENDSELECT.
|
SELECT * FROM mara INTO TABLE itab WHERE matnr BETWEEN '000000000060000000' AND '000000000060000335'.
|
h) Avoid Complex WHERE Clause(1)
Complex WHERE clauses should be avoided always because it confuses database optimizer. One should avoid -OR- construct in WHERE clause instead use IN
| Wrong Approach | Right Approach |
|---|---|
SELECT * FROM ekpo INTO TABLE lt_ekpo WHERE ebeln IN '001' AND aedat IN s_date AND
( bukrs = '3000' OR bukrs = '3500' OR bukrs = '5500').
* ---- logic here ---- *
|
SELECT * FROM ekpo INTO TABLE t_ekpo WHERE ebeln IN s_ebeln AND aedat IN s_date AND bukrs IN ('3000', '3500', '5000').
* ---- logic here ---- *
|
i) Avoid Complex WHERE Clause(2)
Do NOT use the '>= AND <= construct' but use BETWEEN construct instead. It will result in improved performance.
| Wrong Approach | Right Approach |
|---|---|
SELECT * FROM mara WHERE matnr IN s_matnr AND ersda >= sy-datum AND
ersda <= '20141231'.
* ---- logic here ---- *
|
SELECT * FROM mara WHERE matnr IN s_matnr AND ersda BETWEEN sy-datum AND '20141231'.
* ---- logic here ---- *
|
j) Select statements on Cluster Tables
Do not use select statement on cluster tables as it creates performance issues. Alternatively, we should access Secondary Index tables.
For example, BSEG access
| Wrong Approach | Right Approach |
|---|---|
SELECT bukrs belnr ghahr INTO TABLE itab FROM besg WHERE kunnr IN s_kunnr.
|
* secondary index tables access
SELECT bukrs belnr ghahr INTO TABLE itab FROM bsid WHERE kunnr IN s_kunnr.
|
NOTE: SAP has provided many secondary index tables for operation on FI-GL tables. Also, it is always a better option to buffer the data of a pool table in the application once and process it in the program rather then going to database again and again.
TIPS:
- In FI-GL the secondary index tables BSIS, BSAS, BSIK, BSAK, BSID and BSAD often are a better starting point to retrieve accounting document information than using the traditional method (directly going from BKPF (accounting document header table) to the BSEG table)
- FI-GL programs which are normally run for a specific vendor account number it is better to start accessing BSAK and BSIK first, before accessing BKPF and BSEG, instead of starting with the BKPF table. As the BKPF table does not contain vendor account number information, a lot of accounting documents are read which are not needed as they do not match the vendor account specified by the user, in case BKPF is used as starting point. In case BSAK and BSIK are used, only the accounting documents matching the specified vendor account number will be read, reducing the runtime from several hours to minutes.
