Looking For best practices to follow while working with database in SAP ABAP. Check out this article.
Here are the few best practices you should follow to improve the performance while working with Database.
Buffering of Data (Useful but wrong Buffering can be harmful)
Defining a table as buffered (SE11) can help in improving the performance but this has to be used with caution.
Following statements cannot be used with a buffered table. In such cases, buffer is bypassed. These are:
- SELECT DISTINCT
- ORDER BY
- GROUP BY
- HAVING
- Nested Queries
- JOIN Queries
Indexing ( its kind of useful if you are not creating it just for name sake , But at the same time time-consuming if its created without proper research ) Creation of Index for improving performance should not be taken without thought. Index speeds up the performance but at the same time adds two overheads namely; memory and insert/append performance.
When INDEX is created, memory is used up for storing the index and index sizes can be quite big on large transaction tables!
When inserting new entry in the table, all the indexes are updated. More index more time. More the amount of data, bigger the indices, larger the time for updating all the indices
∙ Table should not have more than 2 secondary indexes if the data class is APPL1.
∙ Table should not have more than 4 secondary indexes if the data class is APPL0.
∙ A secondary index should not have more than 4 fields.
∙ Table should not have a unique secondary index.
Use secondary index / Create secondary index if accessing a database table with non-key fields depending on the frequency of usage and volume of data accessed.
JOIN Vs. FOR ALL ENTRIES
JOINS are recommended to be used till 5 joins. If the JOIN is being made on fields which are key fields in both the tables, it reduced program overhead and increases performance.
So, if the JOIN is between two tables where the JOINING KEYS are key fields JOIN is recommended over FOR ALL ENTRIES.
Check that the internal table used in FOR ALL ENTRIES is NOT empty as this will retrieve all entries from the table
HAVING clause in a SELECT statement
In a SELECT statement, the HAVING clause allows you to specify a logical condition for the groups in a GROUP-BY clause.
Effective use of the having clause can reduce the set of data transferred from the database to the application server.
When the having clause is used, the aggregates and groups are constructed in the database instead of the application server, thereby reducing the resulting set.
For example, if application requires cumulative sales figures for a month in APJ.
Care needs to be taken while selecting a proper match
SELECT *
Wherever possible, fields should be specified rather than SELECT *.
SELECT * Vs. SELECT SINGLE *
If you are interested in exactly one row of a database table or view, use the SELECT SINGLE statement instead of a SELECT * statement.
SELECT SINGLE requires one communication with the database system whereas SELECT * requires two.
SELECT SINGLE Vs SELECT … UP TO 1 ROWS?
SELECT SINGLE and SELECT UP TO n ROWS return the first matching row/rows for the given condition. It may not be unique, if there are more matching rows for the given condition.
In order to check for the existence of a record then it is better to use SELECT SINGLE than using SELECT … UP TO 1 ROWS since it uses low memory and has better performance.With ORACLE database system, SELECT SINGLE is converted into SELECT … UP TO 1 ROWS, thus they are exactly the same in that case.
The only difference is the ABAP syntax prevents from using ORDER BY with SELECT SINGLE, but it is allowed with SELECT … UP TO 1 ROWS.
Thus, if several records may be returned and we want to get the highest record for example, SELECT SINGLE cannot be used, but SELECT … UP TO 1 ROWS WHERE … ORDER BY … may be used.
Accessing Pool and Cluster Tables
When accessing pool and cluster tables, these should be accessed using the full primary key
SELECT query in a loop
Avoid placing a ‘SELECT’ or ‘SELECT SINGLE’ in a loop to minimize the number of database requests
SELECT.. INTO Vs. SELECT..INTO CORRESPONDING FIELDS OF..Use the latter if:
∙ the fields are needed in a different order the not all fields are needed and INTO does not work because gaps would appear.
SELECT….INTO Vs. SELECT…ENDSELECT….APPEND
It is more efficient to use the former to pull the data from DB to internal table rather than performing APPEND within SELECT…ENDSELECT.
Bad Example:
SELECT * FROM T006 INTO X006.
APPEND X006.
ENDSELECT.
Good Example:
SELECT * FROM T006 INTO TABLE X006.
SELECT *…WHERE <Condition>
The number of records retrieved should be limited using the ‘WHERE’ clause rather than selecting and using the ‘CHECK’ statement.
Fields should be listed in the WHERE clause in the same order as they appear in the table.
Bad Example:
SELECT * FROM SBOOK.
CHECK: SBOOK-CARRID = ‘LH’ AND SBOOK-CONNID = ’0400′.
ENDSELECT.
Good Example:
SELECT * FROM SBOOK WHERE CARRID = ‘LH’ AND CONNID = ’0400′. ENDSELECT.
ORDER BY clause Vs. Internal Table SortingUsing SORT statements in place of ORDER BY clause in SELECT statement is more efficient.
Sorting will happen in Application layer on the internal table which will be much faster as compared to search in DB using ORDER BY clause.
Perform ORDER BY clause should NOT be used with non-key and non-index fields.
Do not use SORT statements within a loop.
Aggregate Functions
Make use of aggregate functions while writing Select Query rather than using loops on internal tables to find Max. Value for example as this is more efficient.
Bad Example:
C4A = ’000′.
SELECT * FROM T100 WHERE SPRSL = ‘D’ AND ARBGB = ’00′.
CHECK: T100-MSGNR > C4A.
C4A = T100-MSGNR.
ENDSELECT.
Good Example:
SELECT MAX( MSGNR ) FROM T100 INTO C4A WHERE SPRSL = ‘D’ AND ARBGB = ’00′.
SELECT DISTINCT Vs. SELECT….SORT….DELETE ADJACENT….
Avoid using SELECT DISTINCT clause. Instead SORT the internal table and then DELETE the adjacent duplicates as this is more efficient.
Identical Selects
Avoid using identical selects (SELECT with same parameters and WHERE clause) multiple times in a program flow. This causes hits to DB which can be reduced with biuffering
Consider buffering of data from SELECT statement and re-using the same information elsewhere instead of querying the database again with same parameters.
Existence Checks
If you are interested if there exists at least one row of a database table or view with a certain condition, use the Select … Up To 1 Rows statement instead of a Select-Endselect-loop with an Exit.
If all primary key fields are supplied in the Where condition you can even use Select Single. Select Single requires one communication with the database system, whereas Select-Endselect needs two.
Here are the few best practices you should follow to improve the performance while working with Database.
Buffering of Data (Useful but wrong Buffering can be harmful)
Defining a table as buffered (SE11) can help in improving the performance but this has to be used with caution.
- Buffering of tables leads to data being read from the buffer rather than from table. Buffer sync with table happens periodically, only if something changes which is happen rarely.
- If this table is a transaction table chances are that the data is changing for particular selection criteria, therefore application tables are usually not suited for table buffering. Using table buffering in such cases is not recommended.
- Customizing data and Master Data should be buffered from DB to Application layer. This will help in reducing the number of hits to the database and enhance performance.
- In case of Standard master data, try using already available standard Function Modules to fetch data with buffering.
Following statements cannot be used with a buffered table. In such cases, buffer is bypassed. These are:
- SELECT DISTINCT
- ORDER BY
- GROUP BY
- HAVING
- Nested Queries
- JOIN Queries
Indexing ( its kind of useful if you are not creating it just for name sake , But at the same time time-consuming if its created without proper research ) Creation of Index for improving performance should not be taken without thought. Index speeds up the performance but at the same time adds two overheads namely; memory and insert/append performance.
When INDEX is created, memory is used up for storing the index and index sizes can be quite big on large transaction tables!
When inserting new entry in the table, all the indexes are updated. More index more time. More the amount of data, bigger the indices, larger the time for updating all the indices
∙ Table should not have more than 2 secondary indexes if the data class is APPL1.
∙ Table should not have more than 4 secondary indexes if the data class is APPL0.
∙ A secondary index should not have more than 4 fields.
∙ Table should not have a unique secondary index.
Use secondary index / Create secondary index if accessing a database table with non-key fields depending on the frequency of usage and volume of data accessed.
JOIN Vs. FOR ALL ENTRIES
JOINS are recommended to be used till 5 joins. If the JOIN is being made on fields which are key fields in both the tables, it reduced program overhead and increases performance.
So, if the JOIN is between two tables where the JOINING KEYS are key fields JOIN is recommended over FOR ALL ENTRIES.
Check that the internal table used in FOR ALL ENTRIES is NOT empty as this will retrieve all entries from the table
HAVING clause in a SELECT statement
In a SELECT statement, the HAVING clause allows you to specify a logical condition for the groups in a GROUP-BY clause.
Effective use of the having clause can reduce the set of data transferred from the database to the application server.
When the having clause is used, the aggregates and groups are constructed in the database instead of the application server, thereby reducing the resulting set.
For example, if application requires cumulative sales figures for a month in APJ.
Care needs to be taken while selecting a proper match
SELECT *
Wherever possible, fields should be specified rather than SELECT *.
SELECT * Vs. SELECT SINGLE *
If you are interested in exactly one row of a database table or view, use the SELECT SINGLE statement instead of a SELECT * statement.
SELECT SINGLE requires one communication with the database system whereas SELECT * requires two.
SELECT SINGLE Vs SELECT … UP TO 1 ROWS?
SELECT SINGLE and SELECT UP TO n ROWS return the first matching row/rows for the given condition. It may not be unique, if there are more matching rows for the given condition.
In order to check for the existence of a record then it is better to use SELECT SINGLE than using SELECT … UP TO 1 ROWS since it uses low memory and has better performance.With ORACLE database system, SELECT SINGLE is converted into SELECT … UP TO 1 ROWS, thus they are exactly the same in that case.
The only difference is the ABAP syntax prevents from using ORDER BY with SELECT SINGLE, but it is allowed with SELECT … UP TO 1 ROWS.
Thus, if several records may be returned and we want to get the highest record for example, SELECT SINGLE cannot be used, but SELECT … UP TO 1 ROWS WHERE … ORDER BY … may be used.
Accessing Pool and Cluster Tables
When accessing pool and cluster tables, these should be accessed using the full primary key
SELECT query in a loop
Avoid placing a ‘SELECT’ or ‘SELECT SINGLE’ in a loop to minimize the number of database requests
SELECT.. INTO Vs. SELECT..INTO CORRESPONDING FIELDS OF..Use the latter if:
∙ the fields are needed in a different order the not all fields are needed and INTO does not work because gaps would appear.
SELECT….INTO Vs. SELECT…ENDSELECT….APPEND
It is more efficient to use the former to pull the data from DB to internal table rather than performing APPEND within SELECT…ENDSELECT.
Bad Example:
SELECT * FROM T006 INTO X006.
APPEND X006.
ENDSELECT.
Good Example:
SELECT * FROM T006 INTO TABLE X006.
SELECT *…WHERE <Condition>
The number of records retrieved should be limited using the ‘WHERE’ clause rather than selecting and using the ‘CHECK’ statement.
Fields should be listed in the WHERE clause in the same order as they appear in the table.
Bad Example:
SELECT * FROM SBOOK.
CHECK: SBOOK-CARRID = ‘LH’ AND SBOOK-CONNID = ’0400′.
ENDSELECT.
Good Example:
SELECT * FROM SBOOK WHERE CARRID = ‘LH’ AND CONNID = ’0400′. ENDSELECT.
ORDER BY clause Vs. Internal Table SortingUsing SORT statements in place of ORDER BY clause in SELECT statement is more efficient.
Sorting will happen in Application layer on the internal table which will be much faster as compared to search in DB using ORDER BY clause.
Perform ORDER BY clause should NOT be used with non-key and non-index fields.
Do not use SORT statements within a loop.
Aggregate Functions
Make use of aggregate functions while writing Select Query rather than using loops on internal tables to find Max. Value for example as this is more efficient.
Bad Example:
C4A = ’000′.
SELECT * FROM T100 WHERE SPRSL = ‘D’ AND ARBGB = ’00′.
CHECK: T100-MSGNR > C4A.
C4A = T100-MSGNR.
ENDSELECT.
Good Example:
SELECT MAX( MSGNR ) FROM T100 INTO C4A WHERE SPRSL = ‘D’ AND ARBGB = ’00′.
SELECT DISTINCT Vs. SELECT….SORT….DELETE ADJACENT….
Avoid using SELECT DISTINCT clause. Instead SORT the internal table and then DELETE the adjacent duplicates as this is more efficient.
Identical Selects
Avoid using identical selects (SELECT with same parameters and WHERE clause) multiple times in a program flow. This causes hits to DB which can be reduced with biuffering
Consider buffering of data from SELECT statement and re-using the same information elsewhere instead of querying the database again with same parameters.
Existence Checks
If you are interested if there exists at least one row of a database table or view with a certain condition, use the Select … Up To 1 Rows statement instead of a Select-Endselect-loop with an Exit.
If all primary key fields are supplied in the Where condition you can even use Select Single. Select Single requires one communication with the database system, whereas Select-Endselect needs two.
No comments:
Post a Comment