5 Replies Latest reply: Aug 29, 2007 1:27 PM by Olivier Cora RSS

Function Module Extraction from KONV Table taking lot of time for extractio

SWAPNIL THOLIYA
Currently Being Moderated

Hi

 

I have a requirement wherein i need to get records from KONV Table (Conditions (Transaction Data) ). i need the data corresponding to Application (KAPPL) = 'F'.

 

For this i had written one function module but it is taking lot of time (@ 2.5 hrs) for fetching records as there are large number of records in KONV Table.

 

I am pasting the Function Module code for reference.

 

<b>kindly guide me as to how the extraction performance can be improved.</b>

 

<b>Function Module Code:</b>

 

FUNCTION ZBW_SHPMNT_COND.

*"----


""Local interface:

*"  IMPORTING

*"     VALUE(I_REQUNR) TYPE  SBIWA_S_INTERFACE-REQUNR

*"     VALUE(I_ISOURCE) TYPE  SBIWA_S_INTERFACE-ISOURCE OPTIONAL

*"     VALUE(I_MAXSIZE) TYPE  SBIWA_S_INTERFACE-MAXSIZE OPTIONAL

*"     VALUE(I_INITFLAG) TYPE  SBIWA_S_INTERFACE-INITFLAG OPTIONAL

*"     VALUE(I_UPDMODE) TYPE  SBIWA_S_INTERFACE-UPDMODE OPTIONAL

*"     VALUE(I_DATAPAKID) TYPE  SBIWA_S_INTERFACE-DATAPAKID OPTIONAL

*"     VALUE(I_PRIVATE_MODE) OPTIONAL

*"     VALUE(I_CALLMODE) LIKE  ROARCHD200-CALLMODE OPTIONAL

*"  TABLES

*"      I_T_SELECT TYPE  SBIWA_T_SELECT OPTIONAL

*"      I_T_FIELDS TYPE  SBIWA_T_FIELDS OPTIONAL

*"      E_T_DATA STRUCTURE  ZBW_SHPMNT_COND OPTIONAL

*"      E_T_SOURCE_STRUCTURE_NAME OPTIONAL

*"  EXCEPTIONS

*"      NO_MORE_DATA

*"      ERROR_PASSED_TO_MESS_HANDLER

*"----


 

  • The input parameter I_DATAPAKID is not supported yet !

  TABLES: KONV.

 

  • Auxiliary Selection criteria structure

  DATA: l_s_select TYPE sbiwa_s_select.

 

  • Maximum number of lines for DB table

  STATICS: l_maxsize TYPE sbiwa_s_interface-maxsize.

 

  • Maximum number of lines for DB table

  STATICS: S_S_IF TYPE SRSC_S_IF_SIMPLE,

 

  • counter

          S_COUNTER_DATAPAKID LIKE SY-TABIX,

 

  • cursor

          S_CURSOR TYPE CURSOR.

 

  • Select ranges

  RANGES: L_R_KNUMV  FOR KONV-KNUMV,

          L_R_KSCHL  FOR KONV-KSCHL,

          L_R_KDATU  FOR KONV-KDATU.

 

  • Declaring internal tables

  • DATA : I_KONV LIKE KONV OCCURS 0 WITH HEADER LINE.

  DATA : Begin of I_KONV occurs 0,

         MANDT LIKE konv-mandt,

         KNUMV LIKE konv-knumv,

         KPOSN LIKE konv-kposn,

         STUNR LIKE konv-stunr,

         ZAEHK LIKE konv-zaehk,

         KAPPL LIKE konv-kappl,

         KSCHL LIKE konv-kschl,

         KDATU LIKE konv-kdatu,

         KBETR LIKE konv-kbetr,

         WAERS LIKE konv-waers,

         END OF I_KONV.

 

 

  • 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

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

 

  • The input parameter I_DATAPAKID is not supported yet !

 

  • Invalid second initialization call -> error exit

    IF NOT g_flag_interface_initialized IS INITIAL.

 

      IF

        1 = 2.

        MESSAGE e008(r3).

      ENDIF.

      log_write 'E'                    "message type

                'R3'                   "message class

                '008'                  "message number

                ' '                    "message variable 1

                ' '.                   "message variable 2

      RAISE error_passed_to_mess_handler.

    ENDIF.

 

  • Check InfoSource validity

    CASE i_isource.

      WHEN 'X'.

  •      WHEN 'Y'.

  •      WHEN 'Z'.

      WHEN OTHERS.

  •        IF 1 = 2. MESSAGE e009(r3). ENDIF.

  •        log_write 'E'                  "message type

  •                  'R3'                 "message class

  •                  '009'                "message number

  •                  i_isource            "message variable 1

  •                  ' '.                 "message variable 2

  •        RAISE error_passed_to_mess_handler.

    ENDCASE.

 

  • Check for supported update mode

    CASE i_updmode.

  • For full upload

      WHEN 'F'.

 

      WHEN 'D'.

      WHEN OTHERS.

  •        IF 1 = 2. MESSAGE e011(r3). ENDIF.

  •        log_write 'E'                  "message type

  •                  'R3'                 "message class

  •                  '011'                "message number

  •                  i_updmode            "message variable 1

  •                  ' '.                 "message variable 2

  •        RAISE error_passed_to_mess_handler.

    ENDCASE.

 

    APPEND LINES OF i_t_select TO g_t_select.

 

  • Fill parameter buffer for data extraction calls

    g_s_interface-requnr    = i_requnr.

    g_s_interface-isource   = i_isource.

    g_s_interface-maxsize   = i_maxsize.

    g_s_interface-initflag  = i_initflag.

    g_s_interface-updmode   = i_updmode.

    g_s_interface-datapakid = i_datapakid.

    g_flag_interface_initialized = sbiwa_c_flag_on.

 

  • 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 g_t_fields.

 

  • Interpretation of date selection for generic extraktion

  •    CALL FUNCTION 'RSA3_DATE_RANGE_CONVERT'

  •      TABLES

  •        i_t_select = g_t_select.

 

  ELSE.                 "Initialization mode or data extraction ?

 

  •    CASE g_s_interface-updmode.

  •      WHEN 'F' OR 'C' OR 'I'.

  • First data package -> OPEN CURSOR

    IF g_counter_datapakid = 0.

  •    L_MAXSIZE = G_S_INTERFACE-MAXSIZE.

 

      LOOP AT g_t_select INTO l_s_select WHERE fieldnm = 'KNUMV'.

        MOVE-CORRESPONDING l_s_select TO l_r_knumv.

        APPEND l_r_knumv.

      ENDLOOP.

 

      LOOP AT g_t_select INTO l_s_select WHERE fieldnm = 'KSCHL'.

        MOVE-CORRESPONDING l_s_select TO l_r_kschl.

        APPEND l_r_kschl.

      ENDLOOP.

 

      Loop AT g_t_select INTO l_s_select WHERE fieldnm = 'KDATU'.

        MOVE-CORRESPONDING l_s_select TO l_r_kdatu.

        APPEND l_r_kdatu.

      ENDLOOP.

 

 

*In case of full upload

 

  • 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.

*

 

 

      OPEN CURSOR G_CURSOR FOR

        SELECT MANDT

               KNUMV

               KPOSN

               STUNR

               ZAEHK

               KAPPL

               KSCHL

               KDATU

               KBETR

               WAERS

        FROM   KONV

        WHERE KNUMV IN l_r_knumv

        AND   KSCHL IN l_r_kschl

        AND   KDATU IN l_r_kdatu

        AND   KAPPL EQ 'F'.

 

    ENDIF.

 

    Refresh I_KONV.

 

    FETCH NEXT CURSOR G_CURSOR

               APPENDING CORRESPONDING FIELDS OF TABLE I_KONV

               PACKAGE SIZE S_S_IF-MAXSIZE.

 

    IF SY-SUBRC <> 0.

      CLOSE CURSOR G_CURSOR.

      RAISE NO_MORE_DATA.

 

    ENDIF.

 

    LOOP AT I_KONV.

  •      IF I_KONV-KAPPL EQ 'F'.

      CLEAR :E_T_DATA.

      E_T_DATA-MANDT = I_KONV-MANDT.

      E_T_DATA-KNUMV = I_KONV-KNUMV.

      E_T_DATA-KPOSN = I_KONV-KPOSN.

      E_T_DATA-STUNR = I_KONV-STUNR.

      E_T_DATA-ZAEHK = I_KONV-ZAEHK.

      E_T_DATA-KAPPL = I_KONV-KAPPL.

      E_T_DATA-KSCHL = I_KONV-KSCHL.

      E_T_DATA-KDATU = I_KONV-KDATU.

      E_T_DATA-KBETR = I_KONV-KBETR.

      E_T_DATA-WAERS = I_KONV-WAERS.

 

      APPEND E_T_DATA.

  •    ENDIF.

    ENDLOOP.

 

    g_counter_datapakid = g_counter_datapakid + 1.

 

 

  ENDIF.

ENDFUNCTION.

 

 

 

Thanks in Advance

Regards

Swapnil.

Actions