Skip to Content
-1

Performance of End Routine in SAP BW

Hi All,

I have written end routine to populate the DSO where the billing number already exists doesnt gets updated by looking up on another DSO but the routine is taking 1 - 2hour to complete for updating 100-150 records. Here is the code. Please let me know your thoughts in improving the performance of the code.

Code:

***Declaration***

TYPES: BEGIN OF TY_CON,

UCCONTRACT TYPE /BI0/OIUCCONTRACT,
UCINSTALLA TYPE /BI0/OIUCINSTALLA,

END OF TY_CON.

TYPES: BEGIN OF TY_LOOKUP,
UC_SIM_ID TYPE /BI0/OIUC_SIM_ID,
/BIC/ZCHBL_NUM TYPE /BIC/OIZCHBL_NUM,
END OF TY_LOOKUP.

DATA: LT_CON TYPE STANDARD TABLE OF TY_CON,
LT_LOOKUP TYPE STANDARD TABLE OF TY_LOOKUP,
LT_RESULT TYPE STANDARD TABLE OF _TY_S_TG_1,
LT_RP TYPE STANDARD TABLE OF _TY_S_TG_1,
LV_RESULT TYPE _TY_S_TG_1.

****end of declaration****


FIELD-SYMBOLS: <FS_CON> TYPE TY_CON.

LT_RP[] = RESULT_PACKAGE[].

SORT LT_RP BY UCCONTRACT.
DELETE ADJACENT DUPLICATES FROM LT_RP COMPARING UCCONTRACT.


IF LT_RP[] IS NOT INITIAL.


SELECT UCCONTRACT UCINSTALLA FROM /BI0/PUCCONTRACT
INTO TABLE LT_CON FOR ALL ENTRIES IN LT_RP WHERE
UCCONTRACT = LT_RP-UCCONTRACT.

IF SY-SUBRC IS INITIAL.
SORT LT_CON BY UCCONTRACT.
DELETE LT_CON WHERE UCINSTALLA IS INITIAL.
ENDIF.
ENDIF.

CLEAR LT_RP[].
LT_RP[] = RESULT_PACKAGE[].

SORT LT_RP BY UC_SIM_ID /BIC/ZCHBL_NUM.
DELETE ADJACENT DUPLICATES FROM LT_RP COMPARING UC_SIM_ID /BIC/ZCHBL_NUM.


IF LT_RP[] IS NOT INITIAL.

SELECT UC_SIM_ID /BIC/ZCHBL_NUM FROM /BIC/ABDSSDS9600
INTO TABLE LT_LOOKUP FOR ALL ENTRIES IN LT_RP
WHERE UC_SIM_ID = LT_RP-UC_SIM_ID AND
/BIC/ZCHBL_NUM = LT_RP-/BIC/ZCHBL_NUM.

IF SY-SUBRC IS INITIAL.

SORT LT_LOOKUP.

ENDIF.
ENDIF.

CLEAR LT_RP[].

LOOP AT RESULT_PACKAGE INTO LV_RESULT.

READ TABLE LT_CON ASSIGNING <FS_CON>
WITH KEY UCCONTRACT = LV_RESULT-UCCONTRACT
BINARY SEARCH.

IF SY-SUBRC IS INITIAL.

LV_RESULT-UCINSTALLA = <FS_CON>-UCINSTALLA.

ENDIF.

READ TABLE LT_LOOKUP WITH KEY
UC_SIM_ID = LV_RESULT-UC_SIM_ID
/BIC/ZCHBL_NUM = LV_RESULT-/BIC/ZCHBL_NUM
BINARY SEARCH TRANSPORTING NO FIELDS.

IF SY-SUBRC IS NOT INITIAL.

APPEND LV_RESULT TO LT_RESULT.

ENDIF.

ENDLOOP.

CLEAR RESULT_PACKAGE[].
RESULT_PACKAGE[] = LT_RESULT[].

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    May 14 at 05:58 AM

    Define LT_CON and LT_LOOKUP as HASHED tables. Using standard tables with sort and binary search is pre-2000 techniques, and not the most efficient method.

    Add comment
    10|10000 characters needed characters exceeded

  • May 13 at 01:58 PM

    Hi,

    You must identify where si a bottleneck.

    Please run the transformation in debug and try to identify:

    1. Is the problem before end routine (a lot of time takes to actually get into end routine)

    2. Is the problem inside end routine? If so then in which part of the code? What is the volume of result package? What is the volume and select time of internal tables? etc.

    3. Is the problem after end routine?

    BR,

    Add comment
    10|10000 characters needed characters exceeded

  • May 13 at 07:29 PM

    Abhimanyu,

    is this /BIC/ZCHBL_NUM = LT_RP-/BIC/ZCHBL_NUM is key field in DSO where you did lookup?

    if not then create secondary index on DSO BDSSDS96

    Thanks

    Add comment
    10|10000 characters needed characters exceeded