Generic Extractor based on FM taking a lot of time

[Former Member]

Hi All,

I have written a FM that extracts data based on a certain logic. The data that is extracts is somewhere around 10,000 to 12,000 records. While the code has no errors and executes with correct output, the extraction takes a lot of time and generates a huge log. The job runs for around 2 days. The logic for the extraction is pretty straight forward and the code is short. All select statements are written according to the indexes. But I think there is something wrong with the code as such simple logic shouldn't take this amount of time.

I am basically querying DFKKOP but the problem is elsewhere.

The code is as shown below. It would be great if anyone can point out where the problem is.

-------------------------------------------------------------------------------------------------------------------------------------------

FUNCTION ZX_BIW_SD_SDJ_REFUND.

*"----------------------------------------------------------------------

*"*"Local Interface:

*"  IMPORTING

*"     VALUE(I_REQUNR) TYPE  SRSC_S_IF_SIMPLE-REQUNR

*"     VALUE(I_DSOURCE) TYPE  SRSC_S_IF_SIMPLE-DSOURCE OPTIONAL

*"     VALUE(I_MAXSIZE) TYPE  SRSC_S_IF_SIMPLE-MAXSIZE OPTIONAL

*"     VALUE(I_INITFLAG) TYPE  SRSC_S_IF_SIMPLE-INITFLAG OPTIONAL

*"     VALUE(I_READ_ONLY) TYPE  SRSC_S_IF_SIMPLE-READONLY OPTIONAL

*"     VALUE(I_REMOTE_CALL) TYPE  SBIWA_FLAG DEFAULT SBIWA_C_FLAG_OFF

*"  TABLES

*"      I_T_SELECT TYPE  SRSC_S_IF_SIMPLE-T_SELECT OPTIONAL

*"      I_T_FIELDS TYPE  SRSC_S_IF_SIMPLE-T_FIELDS OPTIONAL

*"      E_T_DATA STRUCTURE  ZEXT_SD_REFUND OPTIONAL

*"  EXCEPTIONS

*"      NO_MORE_DATA

*"      ERROR_PASSED_TO_MESS_HANDLER

*"----------------------------------------------------------------------

   TABLES:ZEXT_SD_REFUND.

* Auxiliary Selection criteria structure

   DATA: L_S_SELECT TYPE SRSC_S_SELECT.

* Maximum number of lines for DB table

   STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,

                       s_strlen TYPE i,

* counter

           S_COUNTER_DATAPAKID LIKE SY-TABIX,

* cursor

           S_CURSOR TYPE CURSOR.

   TYPES:BEGIN OF ty_DFKKOP,

          MANDT TYPE DFKKOP-MANDT,

          VKONT TYPE DFKKOP-VKONT,

          AUGBL TYPE DFKKOP-AUGBL,

          AUGDT TYPE DFKKOP-AUGDT,

          AUGST TYPE DFKKOP-AUGST,

          AUGRD TYPE DFKKOP-AUGRD,

          BUKRS TYPE DFKKOP-BUKRS,

          HVORG TYPE DFKKOP-HVORG,

          TVORG TYPE DFKKOP-TVORG,

          BETRW TYPE DFKKOP-BETRW,

          END OF ty_DFKKOP.

   TYPES:BEGIN OF ty_DFKKOP2,

          MANDT TYPE DFKKOP-MANDT,

          OPBEL TYPE DFKKOP-OPBEL,

          VKONT TYPE DFKKOP-VKONT,

          BUDAT TYPE DFKKOP-BUDAT,

          AUGST TYPE DFKKOP-AUGST,

          BUKRS TYPE DFKKOP-BUKRS,

          HVORG TYPE DFKKOP-HVORG,

          TVORG TYPE DFKKOP-TVORG,

          BETRW TYPE DFKKOP-BETRW,

          DIFF_AMT TYPE DFKKOP-BETRW,

          END OF ty_DFKKOP2.

   TYPES :BEGIN OF ty_FKKVKP,

          MANDT TYPE FKKVKP-MANDT,

          VKONT TYPE FKKVKP-VKONT,

          ZLOCCODES TYPE FKKVKP-ZLOCCODES,

          END OF ty_FKKVKP.

   TYPES :BEGIN OF ty_DFKKKO,

          MANDT TYPE DFKKKO-MANDT,

          OPBEL TYPE DFKKKO-OPBEL,

          ERNAM TYPE DFKKKO-ERNAM,

          END OF ty_DFKKKO.

   DATA: it_DFKKOP TYPE standard table of ty_DFKKOP,

         wa_DFKKOP TYPE ty_DFKKOP,

         it_DFKKOP_EXT TYPE standard table of ty_DFKKOP,

         wa_DFKKOP_EXT TYPE ty_DFKKOP,

         it_DFKKOP_EXT2 TYPE standard table of ty_DFKKOP,

         wa_DFKKOP_EXT2 TYPE ty_DFKKOP,

         it_DFKKOP2 TYPE standard table of ty_DFKKOP2,

         wa_DFKKOP2 TYPE ty_DFKKOP2,

         it_FKKVKP TYPE STANDARD TABLE OF ty_FKKVKP,

         wa_FKKVKP TYPE ty_FKKVKP,

         it_DFKKKO TYPE STANDARD TABLE OF ty_DFKKKO,

         wa_DFKKKO TYPE ty_DFKKKO.

   DATA: it_finallist1 TYPE standard table of ZEXT_SD_REFUND,

         wa_finallist1 TYPE ZEXT_SD_REFUND.

* Select ranges

   RANGES: S_DAT FOR ZEXT_SD_REFUND-ZZAUGDT.

   RANGES: S_OPBEL FOR ZEXT_SD_REFUND-ZZOPBEL.

* Initialization mode (first call by SAPI) or data transfer mode

* (following calls) ?

   IF I_INITFLAG = SBIWA_C_FLAG_ON.

************************************************************************

* Initialization: check input parameters

*                 buffer input parameters

*                 prepare data selection

************************************************************************

* Check DataSource validity

     CASE I_DSOURCE.

*      WHEN 'DATASOURCE NAME'.

       WHEN 'ZDS_SD_ADJREF'.

       WHEN OTHERS.

         IF 1 = 2. MESSAGE E009(R3). ENDIF.

* this is a typical log call. Please write every error message like this

         LOG_WRITE 'E'                  "message type

                   'R3'                 "message class

                   '009'                "message number

                   I_DSOURCE   "message variable 1

                   ' '.                 "message variable 2

         RAISE ERROR_PASSED_TO_MESS_HANDLER.

     ENDCASE.

     APPEND LINES OF I_T_SELECT TO S_S_IF-T_SELECT.

* Fill parameter buffer for data extraction calls

     S_S_IF-REQUNR    = I_REQUNR.

     S_S_IF-DSOURCE = I_DSOURCE.

     S_S_IF-MAXSIZE   = I_MAXSIZE.

* Fill field list table for an optimized select statement

* (in case that there is no 1:1 relation between InfoSource fields

* and database table fields this may be far from beeing trivial)

     APPEND LINES OF I_T_FIELDS TO S_S_IF-T_FIELDS.

   ELSE.                 "Initialization mode or data extraction ?

************************************************************************

* Data transfer: First Call      OPEN CURSOR + FETCH

*                Following Calls FETCH only

************************************************************************

* First data package -> OPEN CURSOR

     IF S_COUNTER_DATAPAKID = 0.

* Select ranges

   RANGES: s_date FOR ZIUBIS_EXTRACTOR5-zdate.

     RANGES: s_date1 FOR ZIUBIS_EXTRACTOR5-zdate.

  LOOP AT S_S_IF-T_SELECT INTO L_S_SELECT WHERE FIELDNM = 'ZZAUGDT'.

         s_date-sign    =  l_s_select-sign.

         s_date-option  =  l_s_select-option.

         s_date-low     =  l_s_select-low.

         s_date-high    =  l_s_select-high.

          s_date1-sign    =  l_s_select-sign.

         s_date1-option  =  l_s_select-option.

         s_date1-low     =  l_s_select-low.

         s_date1-high    =  l_s_select-high.

         APPEND s_date1.

         s_strlen = STRLEN( l_s_select-low ).

         IF s_strlen = 10.

           CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'

             EXPORTING

               date_external            = l_s_select-low

             IMPORTING

               date_internal            = s_date-low

             EXCEPTIONS

               date_external_is_invalid = 1

               OTHERS                   = 2.

           IF sy-subrc <> 0.

             MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

                 WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

           ENDIF.

           IF l_s_select-high <> space.

             CALL FUNCTION 'CONVERT_DATE_TO_INTERNAL'

               EXPORTING

                 date_external            = l_s_select-high

               IMPORTING

                 date_internal            = s_date-high

               EXCEPTIONS

                 date_external_is_invalid = 1

                 OTHERS                   = 2.

             IF sy-subrc <> 0.

               MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno

                  WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

             ENDIF.

           ENDIF.

         ENDIF.

         APPEND s_date.

       ENDLOOP.

       IF s_date IS NOT INITIAL.

         OPEN CURSOR WITH HOLD s_cursor FOR

         SELECT MANDT VKONT AUGBL AUGDT AUGST AUGRD BUKRS HVORG TVORG BETRW

           FROM DFKKOP

           WHERE augdt IN s_date

           and AUGST = '9'

           AND BUKRS = 'WBDC'.

       ENDIF.

     ENDIF.

     FETCH NEXT CURSOR s_cursor

                  APPENDING CORRESPONDING FIELDS OF

                  TABLE it_DFKKOP

                  PACKAGE SIZE s_s_if-maxsize.

     IF sy-subrc <> 0.

       CLOSE CURSOR s_cursor.

       RAISE no_more_data.

     ENDIF.

     S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.

   ENDIF.

   DATA: S_AUGRD TYPE RANGE OF DFKKOP-AUGRD,

         WA_AUGRD LIKE LINE OF S_AUGRD.

   WA_AUGRD-sign = 'I'.

   WA_AUGRD-option = 'EQ'.

   WA_AUGRD-low = '01'.

   append WA_AUGRD TO S_AUGRD.

   WA_AUGRD-sign = 'I'.

   WA_AUGRD-option = 'EQ'.

   WA_AUGRD-low = '02'.

   append WA_AUGRD TO S_AUGRD.

   WA_AUGRD-sign = 'I'.

   WA_AUGRD-option = 'EQ'.

   WA_AUGRD-low = '03'.

   append WA_AUGRD TO S_AUGRD.

   WA_AUGRD-sign = 'I'.

   WA_AUGRD-option = 'EQ'.

   WA_AUGRD-low = '08'.

   append WA_AUGRD TO S_AUGRD.

DELETE IT_DFKKOP WHERE HVORG NE '0020'.

DELETE IT_DFKKOP WHERE TVORG NE '0010'.

   DELETE IT_DFKKOP WHERE AUGRD NOT IN S_AUGRD.

CLEAR : WA_DFKKOP.

IF IT_DFKKOP IS NOT INITIAL.

   IT_DFKKOP_EXT[] = IT_DFKKOP[].     "All docs with Clearing Reason = '03'

   IT_DFKKOP_EXT2[] = IT_DFKKOP[].    "All docs with Clearing Reason NE '03'

   DELETE IT_DFKKOP_EXT WHERE AUGRD NE '03'.

   ENDIF.

IF IT_DFKKOP IS NOT INITIAL.          "For Location Code

   SELECT MANDT VKONT ZLOCCODES

     FROM FKKVKP

     INTO TABLE IT_FKKVKP

     FOR ALL ENTRIES IN IT_DFKKOP[]

     WHERE VKONT = IT_DFKKOP-VKONT.

     SELECT MANDT OPBEL ERNAM           "For ERNAM

       FROM DFKKKO

       INTO TABLE IT_DFKKKO

       FOR ALL ENTRIES IN IT_DFKKOP[]

       WHERE OPBEL = IT_DFKKOP-AUGBL.

ENDIF.

"LOGIC FOR CLEARING REASON 03 ************************************

IF IT_DFKKOP_EXT IS NOT INITIAL.

   SELECT MANDT OPBEL VKONT BUDAT AUGST BUKRS HVORG TVORG BETRW

     FROM DFKKOP

     INTO CORRESPONDING FIELDS OF TABLE IT_DFKKOP2

     FOR ALL ENTRIES IN IT_DFKKOP_EXT[]

     WHERE OPBEL = IT_DFKKOP_EXT-AUGBL

     AND HVORG = '0250'

     AND TVORG = '0010'.

ENDIF.

DELETE IT_DFKKOP2 WHERE BUKRS NE 'WBDC'.

DELETE IT_DFKKOP2 WHERE AUGST NE ''.

DATA : ADJAMT TYPE P DECIMALS 2.

SORT IT_DFKKOP2.

SORT IT_DFKKOP_EXT.

IF IT_DFKKOP2 IS NOT INITIAL.

LOOP AT IT_DFKKOP2 INTO WA_DFKKOP2.

   READ TABLE IT_DFKKOP_EXT INTO WA_DFKKOP_EXT WITH KEY AUGBL = WA_DFKKOP2-OPBEL BINARY SEARCH.

   IF SY-SUBRC = 0.

     ADJAMT = WA_DFKKOP2-BETRW.

     IF ADJAMT < 0.

       ADJAMT = ADJAMT * -1.

       ENDIF.

   IF WA_DFKKOP_EXT-BETRW < 0.

     WA_DFKKOP_EXT-BETRW = WA_DFKKOP_EXT-BETRW * -1.

     ENDIF.

     WA_DFKKOP2-DIFF_AMT = WA_DFKKOP_EXT-BETRW - ADJAMT.

     MODIFY IT_DFKKOP2 FROM WA_DFKKOP2.

     ENDIF.

     ENDLOOP.

ENDIF.

CLEAR : WA_DFKKOP2.

"LOGIC FOR CLEARING REASON 03 ************************************

"Direct amount update for Clearing Reason 01, 08 and 02***********

SORT IT_DFKKOP_EXT2.

SORT IT_FKKVKP.

SORT IT_DFKKKO.

DELETE IT_DFKKOP_EXT2 WHERE AUGRD EQ '03'.

IF IT_DFKKOP_EXT2 IS NOT INITIAL.

LOOP AT IT_DFKKOP_EXT2 INTO WA_DFKKOP_EXT2.

     WA_FINALLIST1-ZZOPBEL = WA_DFKKOP_EXT2-AUGBL.

     WA_FINALLIST1-ZZAUGDT = WA_DFKKOP_EXT2-AUGDT.

     WA_FINALLIST1-ZZCURR = 'INR'.

     IF WA_DFKKOP_EXT2-AUGRD = '08' OR WA_DFKKOP_EXT2-AUGRD = '01'.

       WA_FINALLIST1-ZZBETRW1 = WA_DFKKOP_EXT2-BETRW * -1.

       WA_FINALLIST1-ZZBETRW2 = '0'.

     ELSEIF WA_DFKKOP_EXT2-AUGRD = '02'.

       WA_FINALLIST1-ZZBETRW2 = WA_DFKKOP_EXT2-BETRW * -1.

       WA_FINALLIST1-ZZBETRW1 = '0'.

     ENDIF.

     READ TABLE IT_FKKVKP INTO WA_FKKVKP WITH KEY VKONT = WA_DFKKOP_EXT2-VKONT BINARY SEARCH.

     IF SY-SUBRC = 0.

       WA_FINALLIST1-ZZLOCCODES = WA_FKKVKP-ZLOCCODES.

       ENDIF.

       READ TABLE IT_DFKKKO INTO WA_DFKKKO WITH KEY OPBEL = WA_DFKKOP_EXT2-AUGBL BINARY SEARCH.

       IF SY-SUBRC = 0.

         WA_FINALLIST1-ZZERNAM = WA_DFKKKO-ERNAM.

       ENDIF.

       APPEND WA_FINALLIST1 TO IT_FINALLIST1.

       CLEAR : WA_FINALLIST1.

ENDLOOP.

ENDIF.

"Direct amount update for Clearing Reason 01, 08 and 02***********

IF IT_DFKKOP2 IS NOT INITIAL.

LOOP AT IT_DFKKOP2 INTO WA_DFKKOP2.

     WA_FINALLIST1-ZZOPBEL = WA_DFKKOP2-OPBEL.

     WA_FINALLIST1-ZZAUGDT = WA_DFKKOP2-BUDAT.

     WA_FINALLIST1-ZZCURR = 'INR'.

     WA_FINALLIST1-ZZBETRW1 = WA_DFKKOP2-DIFF_AMT.

     WA_FINALLIST1-ZZBETRW2 = '0'.

     READ TABLE IT_FKKVKP INTO WA_FKKVKP WITH KEY VKONT = WA_DFKKOP2-VKONT BINARY SEARCH.

     IF SY-SUBRC = 0.

       WA_FINALLIST1-ZZLOCCODES = WA_FKKVKP-ZLOCCODES.

       ENDIF.

       READ TABLE IT_DFKKKO INTO WA_DFKKKO WITH KEY OPBEL = WA_DFKKOP2-OPBEL BINARY SEARCH.

       IF SY-SUBRC = 0.

         WA_FINALLIST1-ZZERNAM = WA_DFKKKO-ERNAM.

       ENDIF.

       APPEND WA_FINALLIST1 TO IT_FINALLIST1.

       CLEAR : WA_FINALLIST1.

ENDLOOP.

ENDIF.

   IF it_finallist1[] IS NOT INITIAL.

     e_t_data[] = it_finallist1[].

   ENDIF.

ENDFUNCTION.

[FCI]

Hi Harshit,

I'm not sure the tests your doing to test the emptiness of your internal tables before doing a FOR ALL ENTRIES are correct.

I generally use a

  IF IT_DFKKOP[] IS NOT INITIAL

insteadt of

  IF IT_DFKKOP IS NOT INITIAL

Regards,

Frederic

[karthik_vasudevan]

Hi Harshit

There should be issue with cursor which doesn't stop counting. Though there is no data packages to extract, the code keeps searching for the data.

We had the same issue with one of DS based on FM and we solved it by changing the counter datapackid from 0 to 5 (for my DS, it should be 5)

May be someone else will also give you some suggestions.

Remove the below code

*IF S_COUNTER_DATAPAKID = 0.

     Add the below code

     *Newly added code starts to check

     IF S_COUNTER_DATAPAKID = 2  " try 1 first based on the number of data packages you have

     RAISE NO_MORE_DATA.

     ENDIF.

     *Newly added code ends

*Below is existing step in middle of your code.

S_COUNTER_DATAPAKID = S_COUNTER_DATAPAKID + 1.

This should work fine. Let us know if there is any challenges.

If this works, then consult with your ABAPer and correct the code as you wish foreseeing the future data loads.

Regards

Karthik