Performance issue in a report

[Former Member]

Hi Experts,

This is a basic question but very important for me today.

I have a internal table on which I am looping this internal table contains key fields for two different tables.

as this internal table is from CDPOS I need to seperate the key fields during processing.

What I have done:

I looped on the internal table , seperated the key fields and the used for all entries to get the data from two different tables and then loop again on internal table to proceed further.

Is it better to use select single in this condition inside the loop, this way I can avoid a complete loop two big select queries.

Waiting for your response.

[Former Member]

Hi Experts,

This is a basic question but very important for me today.

I have a internal table on which I am looping this internal table contains key fields for two different tables.

as this internal table is from CDPOS I need to seperate the key fields during processing.

What I have done:

I looped on the internal table , seperated the key fields and the used for all entries to get the data from two different tables and then loop again on internal table to proceed further.

Is it better to use select single in this condition inside the loop, this way I can avoid a complete loop two big select queries.

Waiting for your response.

[tharu]

Dear Willi,

better if you can post your code here..Will help you out.

[Former Member]

* Loop on CDPOS to get records from AFVUand AFVC

       LOOP AT gt_cdpos_op INTO lw_cdpos_op.

         lw_get_data-aufpl = lw_cdpos_op-tabkey+3(10).

         lw_get_data-aplzl = lw_cdpos_op-tabkey+13(8).

         APPEND lw_get_data TO gt_get_data.

         lw_get_aufk-aufnr = lw_cdpos_op-objectid+5(12).

         APPEND lw_get_aufk TO lt_get_aufk.

       CLEAR :lw_cdpos_op,lw_get_aufk,lw_get_data.

       ENDLOOP.

* Get data from AFVU

      IF gt_get_data IS NOT INITIAL.

       REFRESH gt_afvu.

       SELECT * FROM afvu

         INTO TABLE gt_afvu

         FOR ALL ENTRIES IN gt_get_data

         WHERE aufpl = gt_get_data-aufpl

           AND aplzl = gt_get_data-aplzl.

       IF sy-subrc NE 0.

         REFRESH gt_afvu.

       ENDIF.

* Get data from AFVC

       REFRESH gt_afvc.

       SELECT * FROM afvc

         INTO TABLE gt_afvc

         FOR ALL ENTRIES IN gt_get_data

         WHERE aufpl = gt_get_data-aufpl

           AND aplzl = gt_get_data-aplzl.

       IF sy-subrc NE 0.

         REFRESH gt_afvc.

       ENDIF.

      ENDIF.

* Get data AUFK

      IF lt_get_aufk IS NOT INITIAL.

       REFRESH lt_aufk.

       SELECT aufnr auart FROM aufk

         INTO TABLE lt_aufk

         FOR ALL ENTRIES IN lt_get_aufk

         WHERE aufnr = lt_get_aufk-aufnr.

       IF sy-subrc NE 0.

         SORT lt_get_aufk BY aufnr.

       ENDIF.

      ENDIF.

* Get name of users

      IF gt_cdhdr_op IS NOT INITIAL.

        REFRESH lt_user_name.

        SELECT * FROM v_usr_name INTO TABLE lt_user_name FOR ALL ENTRIES IN gt_cdhdr_op

                                       WHERE bname = gt_cdhdr_op-username.

         IF sy-subrc EQ 0.

           SORT lt_user_name BY bname.

         ENDIF.

      ENDIF.

* Loop at GT_AFVU to get the first record

     LOOP AT gt_afvu INTO lw_afvu.

       CLEAR lw_op_status.

       CONCATENATE lw_afvu-mandt lw_afvu-aufpl lw_afvu-aplzl INTO lv_tabkey.

       MOVE-CORRESPONDING lw_afvu TO lw_op_status.

* Get data from AFVU and AFVC and fill the output structure

       CLEAR lw_afvc.

       READ TABLE gt_afvc INTO lw_afvc WITH KEY aufpl = lw_afvu-aufpl

                                                aplzl = lw_afvu-aplzl.

       IF sy-subrc EQ 0.

         MOVE-CORRESPONDING lw_afvc TO lw_op_status.

       ENDIF.

       CLEAR lw_cdpos_first.

       READ TABLE gt_cdpos_op INTO lw_cdpos_first WITH KEY tabkey = lv_tabkey.

* Get time stamp from CDHDR

        IF sy-subrc EQ 0.

        CLEAR lw_cdhdr_op.

        READ TABLE gt_cdhdr_op INTO lw_cdhdr_op WITH KEY  objectid = lw_cdpos_first-objectid

                                                         changenr = lw_cdpos_first-changenr.

          IF sy-subrc EQ 0.

           lw_op_status-aufnr = lw_cdpos_first-objectid+5(12).

          CLEAR lw_aufk.

          READ TABLE lt_aufk INTO lw_aufk WITH KEY aufnr = lw_op_status-aufnr BINARY SEARCH.

           lw_op_status-autyp = lw_cdpos_first-objectid+3(2).

           lw_op_status-aufpl = lw_afvu-aufpl.

           lw_op_status-aplzl = lw_afvu-aplzl.

           lw_op_status-auart = lw_aufk-auart.

           lw_op_status-udate = lw_cdhdr_op-udate.

           lw_op_status-utime = lw_cdhdr_op-utime.

           lw_op_status-username = lw_cdhdr_op-username.

          CLEAR lw_user_name.

          READ TABLE lt_user_name INTO lw_user_name WITH KEY bname = lw_op_status-username BINARY SEARCH.

           IF sy-subrc EQ 0.

            lw_op_status-name_text = lw_user_name-name_text.

           ENDIF.

           APPEND lw_op_status TO gt_op_status.

          ENDIF.

         ENDIF.

       LOOP AT gt_cdpos_op INTO lw_cdpos_op WHERE tabkey EQ lv_tabkey.

         IF lw_cdpos_op-fname EQ 'KEY'.

           CONTINUE.

         ENDIF.

         lv_next = sy-tabix + 1.

           lw_op_status-aufnr = lw_cdpos_op-objectid+5(12).

          CLEAR lw_aufk.

          READ TABLE lt_aufk INTO lw_aufk WITH KEY aufnr = lw_op_status-aufnr BINARY SEARCH.

           lw_op_status-auart = lw_aufk-auart.

           lw_op_status-autyp = lw_cdpos_op-objectid+3(2).

           lw_op_status-aufpl = lw_afvu-aufpl.

           lw_op_status-aplzl = lw_afvu-aplzl.

               UNASSIGN <fs_any>.

               CONCATENATE 'LW_OP_STATUS-' lw_cdpos_op-fname INTO lv_value.

               ASSIGN (lv_value) TO <fs_any>.

              IF sy-subrc EQ 0.

              <fs_any> =  lw_cdpos_op-value_old.

              ENDIF.

         AT END OF changenr.                       ##LOOP_AT_OK

         READ TABLE gt_cdpos_op INTO lw_cdpos_next INDEX lv_next.

         READ TABLE gt_cdhdr_op INTO lw_cdhdr_op WITH KEY  objectid = lw_cdpos_next-objectid

                                                           changenr = lw_cdpos_next-changenr.

          IF sy-subrc EQ 0.

           lw_op_status-udate = lw_cdhdr_op-udate.

           lw_op_status-utime = lw_cdhdr_op-utime.

           lw_op_status-username = lw_cdhdr_op-username.

          CLEAR lw_user_name.

          READ TABLE lt_user_name INTO lw_user_name WITH KEY bname = lw_op_status-username BINARY SEARCH.

           IF sy-subrc EQ 0.

            lw_op_status-name_text = lw_user_name-name_text.

           ENDIF.

           APPEND lw_op_status TO gt_op_status.

          ENDIF.

         ENDAT.

       ENDLOOP.

     ENDLOOP.

[Former Member]

You have multiple nested LOOPs plus a number of READs without the BINARY SEARCH addition. You can use the parallel cursor method instead of the nested LOOPs and sort the tables that you are READing so that you can use a binary search.

You can also try sorted or hashed tables instead of the parallel cursor.

F1 and Google will help you in this.

Rob

[Former Member]

Hi ROb,

Parallel cursor would not be helpful as this both tables are not having the same key.

reads without binary search is having some reason but I can work on that.

But the question is still unanswered.. can I avoid the outer loop for creating the internal table then two select queries and write select single inside the loop.????

Thanks

[Former Member]

I checked your code and it looks like you have coded the right way. If you are facing performance issue, can you please provide me the entire code including data declaration?