topic Re: performance improvance in Application Development and Automation Discussions

performance improvance

Former Member — Wed, 20 Dec 2006 17:01:16 GMT

Is there any way the performance of this report can be improved.

FORM GET_DATA.

SELECT VBELN ERDAT ERNAM AUART NETWR FROM VBAK INTO TABLE ITAB WHERE

VBELN IN S_VBELN AND ERDAT IN S_ERDAT AND ERNAM IN USERID AND ERNAM NE 'BATCHSCHED' AND AUART IN AUART AND VKORG IN S_VKORG.

LOOP AT ITAB.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = ITAB-VBELNS

IMPORTING

OUTPUT = ITAB-VBELNS.

SELECT * FROM CDHDR

INTO TABLE T_CDHDR

WHERE OBJECTCLAS = 'VERKBELEG'

AND OBJECTID = ITAB-VBELNS.

IF NOT T_CDHDR[] IS INITIAL.

SELECT * FROM CDPOS

INTO TABLE T_CDPOS

FOR ALL ENTRIES IN T_CDHDR

WHERE OBJECTCLAS = T_CDHDR-OBJECTCLAS

AND OBJECTID = T_CDHDR-OBJECTID

AND CHANGENR = T_CDHDR-CHANGENR

AND TABNAME = 'VBAK'

AND FNAME = 'FAKSK'

AND CHNGIND = 'U'

AND VALUE_NEW = SPACE.

IF SY-SUBRC = 0.

READ TABLE T_CDPOS INDEX 1.

IF SY-SUBRC = 0.

READ TABLE T_CDHDR WITH KEY CHANGENR = T_CDPOS-CHANGENR.

IF SY-SUBRC = 0.

MOVE ITAB TO ITAB2.

ITAB2-ERNAMB = T_CDHDR-USERNAME.

ITAB2-ERDATB = T_CDHDR-UDATE.

APPEND ITAB2.

ZINDEX = SY-TABIX.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMES,

ITAB2-LASTNAMES) FROM USER_ADDR WHERE BNAME = ITAB2-ERNAMS.

MODIFY ITAB2 INDEX ZINDEX.

ENDSELECT.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMEB,

ITAB2-LASTNAMEB) FROM USER_ADDR WHERE BNAME = ITAB2-ERNAMB.

MODIFY ITAB2 INDEX ZINDEX.

ENDSELECT.

ENDIF.

ENDLOOP.

SORT ITAB2 BY VBELNS .

DELETE ADJACENT DUPLICATES FROM ITAB2 COMPARING ALL FIELDS.

IF MATCHING = 'X'.

if the check box is checked retreive records where person
who created the order and the person who released it for billing
are same , selected all the records otherwise

LOOP AT ITAB2 .

IF ITAB2-ERNAMS = ITAB2-ERNAMB.

MOVE ITAB2 TO ITAB1.

APPEND ITAB1.

ENDIF.

ENDLOOP.

ELSE.

MOVE ITAB2[] TO ITAB1[].

ENDIF.

ENDFORM.

Re: performance improvance

Former Member — Wed, 20 Dec 2006 17:05:05 GMT

Hello Deepti,

Instead of selecting data from CDPOS you can use the FM

CHANGEDOCUMENT_READ_POSITIONS

CHANGEDOCUMENT_READ_HEADERS - CDHDR.

This will definetly improve the performance.

Vasanth

Re: performance improvance

Former Member — Wed, 20 Dec 2006 17:06:29 GMT

within LOOP ur using CDHDR & CDPOS which will take lot of time...May be try to use CDHDR & CDPOS outside LOOP & use read statements within LOOP.

Re: performance improvance

Former Member — Wed, 20 Dec 2006 17:07:04 GMT

The first thing to do is to make sure s_vbeln is not empty before doing the select on VBAK.

Rob

Re: performance improvance

Former Member — Wed, 20 Dec 2006 17:14:14 GMT

how do we change the select statement if s_vbeln is empty .

Re: performance improvance

Former Member — Wed, 20 Dec 2006 17:19:17 GMT

There is also an index on ERDAT, so you have to make sure that either s_vbeln or s_erdat is not empty; otherwise, it will be slow. If s_erdat has a large range of dates, this will slow it down as well.

Have you done a performance trace (ST05) to find out where the problem lies?

Rob

Re: performance improvance

Former Member — Wed, 20 Dec 2006 18:04:12 GMT

I donno how to use st05 exactly .

Re: performance improvance

Former Member — Wed, 20 Dec 2006 18:44:26 GMT

Create two sessions. In the first one, prepare to run your program.

In the other session, go to transaction ST05 and press the "activate trace" button.

Go back to the first session and run the program. When the program completes, go back to the ST05 session an press "deactivate trace". and then "display trace". The list will display the time for each select statement. Particulary long running ones are highlighted.

I find it useful to set breakpoints in the code just before and after the code that I am interested in. Then in ST05, I turn the trace on after I reach the first breakpoint and turn it off at the second one. It reduces the size of the list.

Rob

Re: performance improvance

Former Member — Wed, 20 Dec 2006 19:10:12 GMT

Hi,

Check the modified code...I moved the SQL to outside the loop..Changes are marked in bold..

FORM GET_DATA.

SELECT VBELN ERDAT ERNAM AUART NETWR FROM VBAK INTO TABLE ITAB WHERE

VBELN IN S_VBELN AND ERDAT IN S_ERDAT AND ERNAM IN USERID AND ERNAM NE 'BATCHSCHED' AND AUART IN AUART AND VKORG IN S_VKORG.

DATA: T_CDHDR LIKE CDHDR OCCURS 0 WITH HEADER LINE.

DATA: T_CDPOS LIKE CDPOS OCCURS 0 WITH HEADER LINE.

DATA: BEGIN OF T_OBJECTID OCCURS 0,

OBJECTID LIKE CDHDR-OBJECTID,

END OF T_OBJECTID.

* Get the objectid which is required for FOR ALL ENTRIES.

LOOP AT ITAB.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = ITAB-VBELNS

IMPORTING

OUTPUT = ITAB-VBELNS.

MOVE ITAB-VBELNS TO T_OBJECTID-OBJECTID.

APPEND T_OBJECTID.

ENDLOOP.

* Sort and remove the duplicates.

SORT T_OBJECTID BY OBJECTID.

DELETE ADJACENT DUPLICATES FROM T_OBJECTID COMPARING OBJECTID.

Get the CDHDR.

IF NOT T_OBJECTID[] IS INITIAL.

SELECT * FROM CDHDR

INTO TABLE T_CDHDR

FOR ALL ENTRIES IN T_OBJECTID

WHERE OBJECTCLAS = 'VERKBELEG'

AND OBJECTID = T_OBJECTID-OBJECTID.

ENDIF.

IF NOT T_CDHDR[] IS INITIAL.

SELECT * FROM CDPOS

INTO TABLE T_CDPOS

FOR ALL ENTRIES IN T_CDHDR

WHERE OBJECTCLAS = T_CDHDR-OBJECTCLAS

AND OBJECTID = T_CDHDR-OBJECTID

AND CHANGENR = T_CDHDR-CHANGENR

AND TABNAME = 'VBAK'

AND FNAME = 'FAKSK'

AND CHNGIND = 'U'

AND VALUE_NEW = SPACE.

ENDIF.

DATA: T_CDHDR_TMP LIKE CDHDR OCCURS 0 WITH HEADER LINE.

DATA: T_CDPOS_TMP LIKE CDPOS OCCURS 0 WITH HEADER LINE.

LOOP AT ITAB.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = ITAB-VBELNS

IMPORTING

OUTPUT = ITAB-VBELNS.

REFRESH: T_CDHDR_TMP,

T_CDPOS_TMP.

LOOP AT T_CDHDR WHERE OBJECTID = ITAB-VBELNS.

APPEND T_CDHDR TO T_CDHDR_TMP.

ENDLOOP.

IF NOT T_CDHDR_TMP[] IS INITIAL.

LOOP AT T_CDHDR_TMP.

LOOP AT T_CDPOS WHERE OBJECTCLAS = T_CDHDR_TMP-OBJECTCLAS

AND OBJECTID = T_CDHDR_TMP-OBJECTID

AND CHANGENR = T_CDHDR_TMP-CHANGENR

AND TABNAME = 'VBAK'

AND FNAME = 'FAKSK'

AND CHNGIND = 'U'

AND VALUE_NEW = SPACE.

MOVE T_CDPOS TO T_CDPOS_TMP.

APPEND T_CDPOS_TMP.

ENDLOOP.

IF SY-SUBRC = 0.

READ TABLE T_CDPOS_TMP INDEX 1.

IF SY-SUBRC = 0.

READ TABLE T_CDHDR_TMP WITH KEY CHANGENR = T_CDPOS-CHANGENR.

IF SY-SUBRC = 0.

MOVE ITAB TO ITAB2.

ITAB2-ERNAMB = T_CDHDR-USERNAME.

ITAB2-ERDATB = T_CDHDR-UDATE.

APPEND ITAB2.

ZINDEX = SY-TABIX.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMES,

ITAB2-LASTNAMES) FROM USER_ADDR WHERE BNAME = ITAB2-ERNAMS.

MODIFY ITAB2 INDEX ZINDEX.

ENDSELECT.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMEB,

ITAB2-LASTNAMEB) FROM USER_ADDR WHERE BNAME = ITAB2-ERNAMB.

MODIFY ITAB2 INDEX ZINDEX.

ENDSELECT.

ENDIF.

ENDLOOP.

SORT ITAB2 BY VBELNS .

DELETE ADJACENT DUPLICATES FROM ITAB2 COMPARING ALL FIELDS.

IF MATCHING = 'X'.

if the check box is checked retreive records where person
who created the order and the person who released it for billing
are same , selected all the records otherwise

LOOP AT ITAB2 .

IF ITAB2-ERNAMS = ITAB2-ERNAMB.

MOVE ITAB2 TO ITAB1.

APPEND ITAB1.

ENDIF.

ENDLOOP.

ELSE.

MOVE ITAB2[] TO ITAB1[].

ENDIF.

ENDFORM.

Thanks,

Naren

Re: performance improvance

Former Member — Wed, 20 Dec 2006 19:53:05 GMT

Deepthi,

Please find the modified code below.

SELECT vbeln

erdat

ernam

auart

netwr

FROM vbak

INTO TABLE itab

WHERE vbeln IN s_vbeln

AND erdat IN e_erdat

AND ernam IN userid

AND ERNAM NE 'BATCHSCHED'

AND auart IN auart

AND vkorg IN s_vkorg.

DELETE itab WHERE ernam = 'BATCHSCHED'.

IF NOT itab[] IS INITIAL.

SELECT * FROM cdhdr

INTO TABLE t_cdhdr

FOR ALL ENTRIES IN itab

WHERE objectclas = 'VERKBELEG'

AND objectid = itab-vbelns.

SORT t_cdhdr BY objectid.

IF NOT t_cdhdr[] IS INITIAL.

SELECT * FROM cdpos

INTO TABLE t_cdpos

FOR ALL ENTRIES IN t_cdhdr

WHERE objectclas = t_cdhdr-objectclas

AND objectid = t_cdhdr-objectid

AND changenr = t_cdhdr-changenr

AND tabname = 'VBAK'

AND fname = 'FAKSK'

AND chngind = 'U'

AND value_new = space.

SORT t_cdpos BY objectclas objectid changenr.

ENDIF.

FIELD-SYMBOLS <fs_itab> LIKE LINE OF itab.

LOOP AT itab ASSIGNING <fs_itab>.

READ TABLE t_cdhdr WITH KEY objectid = <fs_itab>-vbelns

BINARY SEARCH.

IF sy-subrc IS INITIAL.

READ TABLE t_cdpos WITH KEY objectclas = t_cdhdr-objectclas

objectid = t_cdhdr-objectid

changenr = t_cdhdr-changenr

BINARY SEARCH.

IF sy-subrc IS INITIAL.

MOVE <fs_itab> TO itab2.

itab2-ernamb = t_cdhdr-username.

itab2-erdatb = t_cdhdr-udate.

PERFORM get_first_last_name USING itab2-ernamb

CHANGING itab2-firstnameb

itab2-lastnameb.

PERFORM get_first_last_name USING itab2-ernams

CHANGING itab2-firstnames

itab2-lastnames.

APPEND itab2.

CLEAR itab2.

ENDIF.

ENDLOOP

sort itab2 by vbelns .

DELETE ADJACENT DUPLICATES FROM itab2 COMPARING ALL FIELDS.

IF matching = 'X'.

if the check box is checked retreive records where person
who created the order and the person who released it for billing
are same , selected all the records otherwise

LOOP AT itab2 WHERE ernams = ernamb.

MOVE itab2 TO itab1.

APPEND itab1.

ENDLOOP.

ELSE.

itab1[] = itab2[].

ENDIF.

&----

*& Form get_first_last_name

&----

text

----

-->P_ERNAM text
<--P_FIRST_NAME text
<--P_LAST_NAME text

----

FORM get_first_last_name USING p_ernam

CHANGING p_first_name

p_last_name.

DATA: BEGIN OF lt_adrp OCCURS 0,

include structure adrp.

DATA: END OF lt_adrp.

CALL FUNCTION 'REOI_DATA_GET_USERDATA'

EXPORTING

iv_bname = p_ernam

CHANGING

cr_adrp = lt_adrp.

p_first_name = lt_adrp-name_first.

p_last_name = lt_adrp-name_last.

ENDFORM. " get_first_last_name

Please let me know if you have any issues.

Thanks,

Subhash