Extraction slows down with each iteration.

Former Member · ‎2011 Jan 23

Hello,

I have strange problem with extraction of data to another DB table.

The principle of the report stands on cursor with hold on EGERH table, some computing in LOOP statement for each package of FETCH and DB_COMMIT call on end of each FETCH.

My problem is that on each LOOP is processed slower and slower:

23.01.2011 15:31:07 Equipment interval from 000000000010500001 to 000000000010510582 Time: 15:31:07

23.01.2011 15:42:49 Equipment interval from 000000000010510583 to 000000000010521040 Time: 15:42:49

23.01.2011 15:56:55 Equipment interval from 000000000010521041 to 000000000010531611 Time: 15:56:55

23.01.2011 16:16:36 Equipment interval from 000000000010531612 to 000000000010542156 Time: 16:16:36

23.01.2011 16:40:41 Equipment interval from 000000000010542157 to 000000000010552751 Time: 16:40:41

23.01.2011 17:08:59 Equipment interval from 000000000010552752 to 000000000010563160 Time: 17:08:59

23.01.2011 17:43:32 Equipment interval from 000000000010563161 to 000000000010573613 Time: 17:43:32

23.01.2011 18:22:30 Equipment interval from 000000000010573614 to 000000000010584173 Time: 18:22:30

I dont understand why this slown down happend, because DB_COMMIT is done in each FETCH, every internal table is cleared etc. Every line of the log represents 10.000 records from FETCH (the report must process 25 lines), so total time to process report is unacceptable (because this is 250.000 lines of report of 2.000.000 lines which is processend parallel for 12 variants).

Any hint will be helpful:-).

Thanks a lot.

Frantisek

Edited by: Frantisek Holas on Jan 23, 2011 8:38 PM

ThomasZloch · ‎2011 Jan 23

Are you 100% sure you are clearing all internal tables after each block? Such a slowdown is often caused by increasing memory consumption, after some limit is reached the system is swapping to disk, subject to various system parameters.

I suggest you read the blogs mentioned in the following sticky thread and try to zoom in on the bottleneck e.g. via SE30 trace (or SAT on new releases), maybe by comparing two traces, one from the fast and one from the slow part.

Thomas

Former Member · ‎2011 Jan 23

Hi Thomas,

yes, I am absolutely sure. There are 3 internal tables. Each of them is filled with INTO STATEMENT from select.

Thanks:-)

vimalv · ‎2011 Jan 24

Hi Frantisek,

I guess Thomas is right. You might want to REFRESH the 3 internal tables before the next iteration.

Check your SAT trace to see if it is the Processing or fetching which is taking more time.

If processing is taking more time,

Make sure in the processing, if you are using READ statements, read with the BINARY SEARCH option and

if you are using BINARY SEARCH, make sure to sort your tables by the keys you are reading it with.

If there are any nested Loop statements use the [Parallel Cursor|http://wiki.sdn.sap.com/wiki/display/Snippets/ABAPCodeforParallelCursor-Loop+Processing] and restrict loop flaws.

If SAT shows FETCHes are taking longer, you might want to analyze your SELECT statements and see why it get stuck on the later iterations. Maybe the records differ from one another by more fields as the iteration proceeds?

Hope this helps,

Vimal

Former Member · ‎2011 Jan 25

I would recommend to use the ABAP trace. But your task is much too large to use a non-aggregated ABAP trace.

Then it is harder to use the ABAP trace. Use an aggregated one, and trace the first step. => Trace1

Then either trace several steps or try to stop the trace after every step and switch on the trace only for step 4 or larger.

The compare utility of SAT will guide you to the code which takes more time. There are blogs where I explain how to use trace comparisons.

I can happen that one trace taking is not enough as measurements vary a lot, so times of trace should fit to your overview numbers.

Siegfried

Former Member · ‎2011 Jan 31

Hello, there is my code:

the only way to process the report is through BACKGROUND MODE (dialog mode fill fall in TIME-OUT). I also found IS transaction SM50, that memory allocation of the report is still growing up. That should be the problem. But I don´t know how to fix the problem.

Any hint will be apperiated.

Thanks a lot.

Frantisek

Code:

DELETE FROM zcsbg_bw_dm_data WHERE equnr IN s_equnr.
  COMMIT WORK.

  TRY.
      CLOSE CURSOR dbcur1.
    CATCH cx_sy_open_sql_db.
  ENDTRY.

  OPEN CURSOR WITH HOLD dbcur1 FOR
    SELECT *
           FROM egerh
           WHERE kombinat = 'Z'
            AND equnr IN s_equnr   " 12.08.2010
            AND bis = '99991231'
            AND logiknr NE '0'
           ORDER BY PRIMARY KEY.

  DO.
    IF p_max IS NOT INITIAL.
      lv_count = lv_count + p_size.
      IF lv_count > p_max.
        EXIT.
      ENDIF.
    ENDIF.

    CLEAR lt_egerh.
    FETCH NEXT CURSOR dbcur1
      INTO TABLE lt_egerh PACKAGE SIZE p_size.
    IF sy-subrc <> 0.
      EXIT.
    ENDIF.

    CLEAR lt_eastl.
    SELECT * FROM eastl INTO TABLE lt_eastl
             FOR ALL ENTRIES IN lt_egerh
           WHERE logiknr = lt_egerh-logiknr
             AND bis = '99991231'.

    GET TIME.
    ls_msg-msgty     = 'I'.
    ls_msg-msgid     = '01'.
    ls_msg-msgno     = '319'.
    READ TABLE lt_egerh INTO wa_egerh INDEX 1.
    CONCATENATE 'Equipment interval from' wa_egerh-equnr INTO ls_msg-msgv1 SEPARATED BY space.
    DESCRIBE TABLE lt_egerh LINES lv_lines.
    READ TABLE lt_egerh INTO wa_egerh INDEX lv_lines.
    CONCATENATE 'to' wa_egerh-equnr INTO ls_msg-msgv2 SEPARATED BY space.
    WRITE sy-uzeit TO ls_msg-msgv3.
    CONCATENATE 'Time:' ls_msg-msgv3 INTO ls_msg-msgv3 SEPARATED BY space.
    ls_msg-msgv4     = ' '.
    ls_msg-probclass = '4'.

    IF sy-batch EQ abap_true.
      CONCATENATE ls_msg-msgv1 ls_msg-msgv2 ls_msg-msgv3 INTO lv_string SEPARATED BY space.
      MESSAGE lv_string TYPE 'I'.
    ENDIF.

    CALL FUNCTION 'BAL_LOG_MSG_ADD'
      EXPORTING
        i_log_handle = lf_handle
        i_s_msg      = ls_msg
      EXCEPTIONS
        OTHERS       = 1.

    CLEAR lt_bwdm_data.
    LOOP AT lt_egerh INTO wa_egerh.
      READ TABLE lt_eastl INTO wa_eastl WITH KEY logiknr = wa_egerh-logiknr.
      IF sy-subrc NE 0.
        CONTINUE.
      ELSE.
        CLEAR: wa_bwdm_data.
        wa_bwdm_data-equnr = wa_egerh-equnr.
        wa_bwdm_data-logiknr = wa_egerh-logiknr.
        wa_bwdm_data-devloc = wa_egerh-devloc.

*<--- extension of extractor 22.07.2010
        SELECT SINGLE sernr matnr baujj
                 INTO (wa_bwdm_data-sernr,
                       wa_bwdm_data-matnr,
                       wa_bwdm_data-baujj)
                 FROM equi
                 WHERE equnr = wa_egerh-equnr.

        SELECT SINGLE bauklas ptbnum funklas messart
                 INTO (wa_bwdm_data-bauklas,
                       wa_bwdm_data-ptbnum,
                       wa_bwdm_data-funklas,
                       wa_bwdm_data-messart)
                 FROM etyp
                 WHERE matnr = wa_bwdm_data-matnr.

        SELECT SINGLE bgljahr INTO wa_bwdm_data-bgljahr
                              FROM egers
                              WHERE equnr = wa_egerh-equnr.

        CALL FUNCTION 'Z_DVMBG_METERINFO_GET'
          EXPORTING
            iv_equnr      = wa_egerh-equnr
            iv_keydate    = sy-datum
          IMPORTING
            ev_miconstant = wa_bwdm_data-miconstant
            ev_turns      = wa_bwdm_data-turns
            ev_c_range    = wa_bwdm_data-c_range
            ev_v_range    = wa_bwdm_data-v_range
            ev_zwgruppe   = wa_bwdm_data-zwgruppe
            ev_aclass     = wa_bwdm_data-aclass
            ev_owner      = wa_bwdm_data-owner
            ev_herst      = wa_bwdm_data-herst
            ev_model      = wa_bwdm_data-model.
*---> 22.07.2010

        LOOP AT lt_eastl INTO wa_eastl WHERE logiknr = wa_egerh-logiknr.
          CLEAR: lv_service, lv_aklasse, lv_tariftyp, lv_anlart.
          SELECT SINGLE service anlart INTO (lv_service, lv_anlart) FROM eanl
                               WHERE anlage = wa_eastl-anlage.
          SELECT SINGLE aklasse tariftyp
                   INTO (lv_aklasse, lv_tariftyp)
                   FROM eanlh
                  WHERE anlage = wa_eastl-anlage
                    AND bis = '99991231'.

          IF lv_service = '100'.
            wa_bwdm_data-anlage_distr   = wa_eastl-anlage.
            wa_bwdm_data-distr_service  = lv_service.
            wa_bwdm_data-distr_anlart  = lv_anlart.
            wa_bwdm_data-distr_aklasse  = lv_aklasse.
            wa_bwdm_data-distr_tariftyp = lv_tariftyp.
          ENDIF.
          IF lv_service = '200'.
            wa_bwdm_data-anlage_suppl = wa_eastl-anlage.
            wa_bwdm_data-suppl_service  = lv_service.
            wa_bwdm_data-suppl_anlart  = lv_anlart.
            wa_bwdm_data-suppl_aklasse = lv_aklasse.
            wa_bwdm_data-suppl_tariftyp = lv_tariftyp.
          ENDIF.

        ENDLOOP.


        SELECT SINGLE vertrag invoicing_party vkonto kofiz
                 INTO (wa_bwdm_data-vertrag_distr, wa_bwdm_data-inv_party_distr,
                       wa_bwdm_data-vkont, wa_bwdm_data-kofiz_sd)
                 FROM ever
                WHERE anlage = wa_bwdm_data-anlage_distr
                  AND auszdat = '99991231'.

        "replaced 3x single select for performance reasons
        CLEAR lt_ettifn.
        SELECT *
          INTO TABLE lt_ettifn
          FROM ettifn
          WHERE anlage = wa_bwdm_data-anlage_distr AND
                ab <= sy-datum AND
                bis >= sy-datum AND
                operand IN ('NO_ACCESS', 'NO_TRANS', 'NO_GR').
        IF sy-subrc IS INITIAL.
          READ TABLE lt_ettifn ASSIGNING <lfs_ettifn> WITH KEY operand = 'NO_ACCESS'.
          IF sy-subrc IS INITIAL.
            wa_bwdm_data-no_access = <lfs_ettifn>-tarifart.
          ENDIF.
          READ TABLE lt_ettifn ASSIGNING <lfs_ettifn> WITH KEY operand = 'NO_TRANS'.
          IF sy-subrc IS INITIAL.
            wa_bwdm_data-no_trans = <lfs_ettifn>-tarifart.
          ENDIF.
          READ TABLE lt_ettifn ASSIGNING <lfs_ettifn> WITH KEY operand = 'NO_GR'.
          IF sy-subrc IS INITIAL.
            wa_bwdm_data-no_gr = <lfs_ettifn>-tarifart.
          ENDIF.
        ENDIF.

        SELECT SINGLE gpart INTO wa_bwdm_data-gpart
                             FROM fkkvkp
                            WHERE vkont = wa_bwdm_data-vkont.


        SELECT SINGLE vertrag invoicing_party vkonto
                 INTO (wa_bwdm_data-vertrag_suppl, wa_bwdm_data-inv_party_suppl,
                       wa_bwdm_data-vkont)
                 FROM ever
                WHERE anlage = wa_bwdm_data-anlage_suppl
                  AND auszdat = '99991231'.


        SELECT SINGLE tplma INTO wa_bwdm_data-haus FROM iflot
                           WHERE tplnr = wa_egerh-devloc.

        SELECT SINGLE regiogroup INTO wa_bwdm_data-regiogroup FROM ehauisu
                           WHERE haus = wa_bwdm_data-haus.

        wa_bwdm_data-zzdatum = sy-datum.

*        CALL FUNCTION 'Z_DVMBG_METERINFO_GET'
*          EXPORTING
*            IV_EQUNR   = wa_egerh-equnr
*            IV_KEYDATE = SY-DATUM
*          IMPORTING
*            EV_PHASES  = wa_bwdm_data-zzphaseno.

        SELECT SINGLE funklas bauklas INTO (lv_funklas, lv_bauklas)
                                      FROM etyp
                                     WHERE matnr = wa_bwdm_data-matnr.
        CASE lv_funklas.
          WHEN '001' OR '002' OR '003'.
            IF lv_bauklas+3(1) = '1' OR lv_bauklas+3(1) = '2'.
              wa_bwdm_data-zzphaseno = '1'.
            ELSEIF lv_bauklas+3(1) = '3' OR lv_bauklas+3(1) = '4'.
              wa_bwdm_data-zzphaseno = '3'.
            ENDIF.
          WHEN '004' OR '005'.
            IF lv_bauklas+3(1) = '1'.
              wa_bwdm_data-zzphaseno = '1'.
            ELSEIF lv_bauklas+3(1) = '2'.
              wa_bwdm_data-zzphaseno = '3'.
            ENDIF.
        ENDCASE.
        APPEND wa_bwdm_data TO lt_bwdm_data.
      ENDIF.
    ENDLOOP.

    CHECK lt_bwdm_data[] IS NOT INITIAL.
    INSERT zcsbg_bw_dm_data FROM TABLE lt_bwdm_data ACCEPTING DUPLICATE KEYS. "accepting duplicate keys added. before errors occured
    IF sy-subrc IS NOT INITIAL.
      DESCRIBE TABLE lt_bwdm_data LINES lv_lines.
      lv_lines = lv_lines - sy-dbcnt.
      ls_msg-msgty     = 'W'.
      ls_msg-msgid     = '01'.
      ls_msg-msgno     = '319'.
      ls_msg-msgv1     = 'Error at INSERT into the table!'.
      WRITE lv_lines TO ls_msg-msgv2.
      CONCATENATE ls_msg-msgv2 'entries not inserted' INTO ls_msg-msgv2 SEPARATED BY space.
      ls_msg-msgv3     = ' '.
      ls_msg-msgv4     = ' '.
      ls_msg-probclass = '1'.

      IF sy-batch EQ abap_true.
        CONCATENATE ls_msg-msgv1 ls_msg-msgv2 INTO lv_string SEPARATED BY space.
        MESSAGE lv_string TYPE 'W'.
      ENDIF.

      CALL FUNCTION 'BAL_LOG_MSG_ADD'
        EXPORTING
          i_log_handle = lf_handle
          i_s_msg      = ls_msg
        EXCEPTIONS
          OTHERS       = 1.
      EXIT.
    ENDIF.

    CALL FUNCTION 'DB_COMMIT'.
  ENDDO.

  CLOSE CURSOR: dbcur1.
  DATA lt_e_new_lognumbers TYPE bal_t_lgnm.
  APPEND lf_handle TO lt_handle.

  CALL FUNCTION 'BAL_DB_SAVE'
    EXPORTING
      i_save_all       = 'X'
      i_t_log_handle   = lt_handle
    IMPORTING
      e_new_lognumbers = lt_e_new_lognumbers
    EXCEPTIONS
      OTHERS           = 1.

* ----------------------------------------------------------------------
* - End of Selection
* ----------------------------------------------------------------------
END-OF-SELECTION.
  IF sy-batch IS INITIAL.
    PERFORM display_log.
  ENDIF.

* ----------------------------------------------------------------------
* - Top of Page
* ----------------------------------------------------------------------
TOP-OF-PAGE.

**
******* FORMS **********************************************************
**
* ----------------------------------------------------------------------
* -     FORM INIT
* ----------------------------------------------------------------------
FORM init.
ENDFORM.                    "INIT

*&---------------------------------------------------------------------*
*&      Form  display_log
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM display_log.
  DATA: l_s_display_profile TYPE bal_s_prof.

* get a prepared profile

  CALL FUNCTION 'BAL_DSP_PROFILE_STANDARD_GET'
    IMPORTING
      e_s_display_profile = l_s_display_profile
    EXCEPTIONS
      OTHERS              = 1.

  IF sy-subrc <> 0.
    MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
       WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
  ENDIF.


  l_s_display_profile-title = 'DM Master data UPLOAD'.
  l_s_display_profile-use_grid = 'X'.
  l_s_display_profile-disvariant-report = sy-repid.
  l_s_display_profile-show_all = 'X'.
  l_s_display_profile-cwidth_opt = 'X'.
  CALL FUNCTION 'BAL_DSP_LOG_DISPLAY'
  EXPORTING
   i_s_display_profile          = l_s_display_profile
*       I_T_LOG_HANDLE               =
*       I_T_MSG_HANDLE               =
*       I_S_LOG_FILTER               =
*       I_S_MSG_FILTER               =
*       I_T_LOG_CONTEXT_FILTER       =
*       I_T_MSG_CONTEXT_FILTER       =
*       I_AMODAL                     = ' '
*       I_SRT_BY_TIMSTMP             = ' '
*     IMPORTING
*       E_S_EXIT_COMMAND             =
 EXCEPTIONS
   profile_inconsistent         = 1
   internal_error               = 2
   no_data_available            = 3
   no_authority                 = 4
   OTHERS                       = 5
          .
  IF sy-subrc <> 0.
* MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
*         WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
  ENDIF.
ENDFORM.                    "display_log

Edited by: Frantisek Holas on Jan 31, 2011 9:35 AM

Former Member · ‎2011 Jan 31

Hi Frantisek,

this code isn't easy to read!

I see those possible performance killers:

SELECT ... FOR ALL ENTRIES: If the table where you refer to is permanently increasing, this may be the reason.

CALL FUNCTION 'Z_DVMBG_METERINFO_GET': If you call a fm which doesn't clear its internal tables this may also be the reason.

Regards,

Klaus

Former Member · ‎2011 Jan 31

Hello,

thanks for your reply. Meanwhile i found that z-function had subroutines that used buffering, which causing the problem.

Frantisek

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Extraction slows down with each iteration.