Credits - Lakshminarasimhan N
SELECT
CSM_CASE
CSM_EXID
CSM_CRDA
CSM_TYPE
CSM_CATE
CSM_CLDAFROM
/BIC/AZCACSMDS00 INTO TABLE
LIT_ZCACSMDSFOR ALL ENTRIES IN
RESULT_PACKAGE ----------- Must not be empty WHERE
CSM_CASE = RESULT_PACKAGE-CSM_CASE.
ABAP performance tuning for SAP BW system
Applies to:
SAP BW 7.x system
Details
In SAP BW system, we will be using ABAP in many places and the common used places are start routine, end routine and expert routines. This document points out the ways we can fine tune the ABAP code written in the SAP BW system.
Rule 1 – Never use “select *”. Select * should be avoided and “select ... end select” select must be avoided at any cost.
Rule 2 – Always check if internal table is not empty before using “For all entries”. When you use a select statement with “for all entries”, make sure the internal table is not empty.
Example:
SELECT
CSM_CASE
CSM_EXID
CSM_CRDA
CSM_TYPE
CSM_CATE
CSM_CLDAFROM
/BIC/AZCACSMDS00 INTO TABLE
LIT_ZCACSMDSFOR ALL ENTRIES IN
RESULT_PACKAGE ----------- Must not be empty WHERE
CSM_CASE = RESULT_PACKAGE-CSM_CASE.
Hence we need to check if the internal table is not empty and only if it is not empty then proceed with the select statement.
IF RESULT_PACKAGE[] IS NOT INITIAL.
SELECT
CSM_CASE
CSM_EXID
CSM_CRDA
CSM_TYPE
CSM_CATE
CSM_CLDAFROM
/BIC/AZCACSMDS00 INTO TABLE
LIT_ZCACSMDS
FOR ALL ENTRIES IN
RESULT_PACKAGE WHERE
CSM_CASE = RESULT_PACKAGE-CSM_CASE.
CSM_CASE
CSM_EXID
CSM_CRDA
CSM_TYPE
CSM_CATE
CSM_CLDAFROM
/BIC/AZCACSMDS00 INTO TABLE
LIT_ZCACSMDS
FOR ALL ENTRIES IN
RESULT_PACKAGE WHERE
CSM_CASE = RESULT_PACKAGE-CSM_CASE.
- ENDIF.
Rule 3 – Always use “Code Inspector” and “Extended syntax check”. Double click the transformation and then from menu option you can find “Display generated program”, select it. Then the entire program is displayed, then select the “Code Inspector” and “Extended Program Check” from the below screen shot.
Correct the warning and error messages shown.
Rule 4 – Always use the “types” statement to declare the local structure in the program and the same structure can be used in the select statement.
Example –
From the purchasing DSO if you want to read PO number, PO Item and Actual Quantity Delivered. Then we create a local structure using types statement.
Types : begin of lty_pur,
OI_EBELN type /BI0/OIOI_EBELN,
OI_EBELP type /BI0/OIOI_EBELP,
PDLV_QTY type /BI0/OIPDLV_QTY,
End of lty_pur.
Data : lt_pur type standard table of lty_pur. “ Internal table declared based on the local type
Select OI_EBELN OI_EBELP PDLV_QTY from /BI0/APUR_O0100 into table lt_pur.
Rule 5 – Always try use the “Hashed” internal table and “Sorted” internal table in the routines, sometimes when you are unable to use them and you are using the “Standard” internal table, make sure you “Sort” the table in ascending order based on the keys you use in “READ” statement and then use “Binary search” in the READ statement. This improves the read statement performance. When the standard table is sorted and then used make sure that the read statement, matches the sort order otherwise you will get the correct result.
Example –
Select OI_EBELN OI_EBELP PDLV_QTY from /BI0/APUR_O0100 into table lt_pur.
If sy-subrc = 0.
Sort lt_pur by OI_EBELN OI_EBELP. “ Sorting the table based on the key used in read statement
Loop at result_package assigning <result_fields>.
Read table lt_pur into la_pur with key EBELN = <result_fields>- OI_EBELN EBELP = <result_fields>- OI_EBELPBinary search.
If sy-subrc = 0.
<logic to populate the fields>.
Rule 6 – Never use “into corresponding fields of table”. Follow Rule 5, to declare structure via types statement and use it to create an internal table. In the select statement do not use “into corresponding fields of table”.
Example --
Never use the way given below, follow the example of Rule 4
Data : lt_pur type standard table of /BI0/APUR_O0100.
Select OI_EBELN OI_EBELP PDLV_QTY from /BI0/APUR_O0100 into corresponding fields of table lt_pur.
Rule 7 – In the select statement make sure you add the primary key’s. For the DSO’s with huge volume of data make sure you create index and then use them in the select statement.
Rule 8 – Never use Include program in your transformations.
Rule 9 – Try to minimize the use of 'RSDRI_INFOPROV_READ'. In case you need to use it make sure you need only the necessary characteristics and key figures. Make sure the cube is compressed.
Rule 10 – Make sure to clear the “work area”, “temp. variables” before they are used in the loop.
Rule 11 – Always rely on the field symbols rather than the work areas. This way you can avoid the “modify” statement.
Rule 12 – When the code in the transformation is huge and complicated, make sure the DTP package size is reduced for a faster data load.
Rule 13 – Never use hard-coded “BREAK-POINT” in the transformation.
Rule 15 – Add lot of comments in the transformation along with the Developer name, Functional owner, Technical Change, CR number etc.
Rule 16 – Delete duplicated before you use the “For all entries”.
Example –
You select the “status profile” from CRM DSO.
Select CSM_CASE CSM_EXID CSM_SPRO from /BIC/AZCSM_AGE00 into table lt_csm_pro.
Let us assume that there are 1 million records and all these come to the table lt_csm_pro
Now I need to extract from another table using the “Status profile”
So,
Select 0CSM_TYPE 0CSM_CATE from /BIC/AZCSM_BHF00 into table lt_csm_bhf for all entries in
lt_csm_pro where CSM_SPRO = lt_csm_pro-CSM_SPRO.
The above select statement will take very long time to execute as there are 1 million records.
we know that status profile has duplicates and hence when we remove the duplicates then we
will have only 90 status profiles. So the best approach is to remove the duplicates and then use them in “For all entries”
Copy the table lt_csm_pro to another internal table lt_csm_pro_1.
lt_csm_pro_1[] = lt_csm_pro[].
Sort lt_csm_pro_1 by CSM_SPRO.
Delete adjacent duplicates from lt_csm_pro_1 comparing CSM_SPRO.
After the delete statement lt_csm_pro_1- CSM_SPRO will contain only 90 records. Hence the below statement will work fast.
Select 0CSM_TYPE 0CSM_CATE from /BIC/AZCSM_BHF00 into table lt_csm_bhf for all entries in
lt_csm_pro_1 where CSM_SPRO = lt_csm_pro_1-CSM_SPRO.
Rule 17 – Always use the method new_record__end_routine to add new records to the result_package. Manually we can sort the result_package by record number and then add the records instead it is recommended to use the method new_record__end_routine.
Rule 18 – Use the “global declaration” to declare the internal tables only when you want to maintain records between the start, transformation and end routines.
Rule 19 – Make the use of “Documents” to write detailed steps related to code in the transformation, dependent loads and any other details.
Example –
Rule 20 – Try to use the “DTP filter” and “DTP filter routines” to filter the incoming data from the source InfoProvider.
Rule 21 – Try to use SAP provided features like Master data read, DSO read in the transformations rather than the lookup using ABAP code!!!! :-)
Rule 22 – Before writing code check for the volume of data in PRD system and how frequently the data is increasing, this will allow you to foresee challenges and make you write a better code.
Rule 23 – Make sure you use BADI’s instead of CMOD’s. Make sure you write methods and classes instead of Function modules and subroutines.
Rule 24 – Always use the MONITOR_REC table to capture the exceptional records, instead of updating them into any Z table.
Rule 25 – Use the exceptions cx_rsrout_abort and cx_rsbk_errorcount cautiously.
Rule 26 -- Within the start and end routines, for every small change don't add a new "loop at result_package..endloop". avoid multiple "Loop at result_package..endloop" and use the existing "loop at result_package...endloop". Try to add the entire logic within single "loop at..endloop". This will help in maintaining the code uniformly and clearly.
Rule 27 - Use "constants" which enable you to easily maintain. Also it is even more better to maintain the constant values in Infoobject master data table and use them in the ABAP lookup. Futher the paramter tables can also be used.
Rule 28 - "For all entries" will not fetch duplicate records, so there might be a data loss, but inner join would fetch all of the records and hence "for all entries" must be used cautiously. Make sure to use all Primary keys to fetch records before you use the internal table in "For all entries"
scn.sap.com/thread/2029157
Final Rule - Avoid as much as ABAP code as possible !!! :-) The reason is very simple, when you go to power your BW system with HANA, if you transformations have ABAP code then the transformations will not be executed in the HANA Database.
Posts
