Performance issue in a report

Former Member — Thu, 22 Jan 2015 16:51:50 GMT

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.

Re: Performance issue in a report

tharu — Thu, 22 Jan 2015 17:13:47 GMT

Dear Willi,

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

Re: Performance issue in a report

Former Member — Thu, 22 Jan 2015 17:15:30 GMT

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

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

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

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

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.

Re: Performance issue in a report

Former Member — Thu, 22 Jan 2015 17:50:57 GMT

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

Re: Performance issue in a report

Former Member — Thu, 22 Jan 2015 18:07:12 GMT

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

Re: Performance issue in a report

Former Member — Thu, 22 Jan 2015 20:45:06 GMT

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?

topic Re: Performance issue in a report in Application Development and Automation Discussions

Performance issue in a report

Re: Performance issue in a report

Re: Performance issue in a report

Re: Performance issue in a report

Re: Performance issue in a report

Re: Performance issue in a report