topic report performance in Application Development and Automation Discussions

report performance

Former Member — Wed, 07 Feb 2007 16:40:49 GMT

its a simple report program but its taking time in test system . Is there any way i can improve its performance further

FORM GET_DATA.

DATA : TABIX LIKE SY-TABIX.

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

MOVE ITAB-VBELNS TO T_OBJECT-OBJECTID.

APPEND T_OBJECT.

ENDLOOP.

SORT T_OBJECT BY OBJECTID.

DELETE ADJACENT DUPLICATES FROM T_OBJECT COMPARING OBJECTID.

Get the CDHDR.

IF NOT T_OBJECT[] IS INITIAL.

SELECT * FROM CDHDR

INTO TABLE T_CDHDR

FOR ALL ENTRIES IN T_OBJECT

WHERE OBJECTCLAS = 'VERKBELEG'

AND OBJECTID = T_OBJECT-OBJECTID

AND UDATE >= S_ERDAT-LOW.

ENDIF.

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.

LOOP AT T_CDHDR.

TABIX = SY-TABIX.

CLEAR: T_CDPOS.

READ TABLE T_CDPOS WITH KEY OBJECTID = T_CDHDR-OBJECTID

CHANGENR = T_CDHDR-CHANGENR BINARY SEARCH.

IF SY-SUBRC NE 0.

DELETE T_CDHDR INDEX TABIX.

ENDIF.

ENDLOOP.

SORT T_CDHDR BY OBJECTID ASCENDING UDATE UTIME DESCENDING.

DELETE ADJACENT DUPLICATES FROM T_CDHDR COMPARING ALL FIELDS.

LOOP AT T_CDHDR.

READ TABLE ITAB WITH KEY VBELNS = T_CDHDR-OBJECTID.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING ITAB TO ITAB2.

ITAB2-ERNAMB = T_CDHDR-USERNAME.

ITAB2-ERDATB = T_CDHDR-UDATE.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMES,

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

ENDSELECT.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMEB,

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

ENDSELECT.

APPEND ITAB2.

ENDIF.

ENDLOOP.

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.

DELETE ITAB2 INDEX SY-TABIX.

ENDIF.

ENDLOOP.

ENDIF.

ENDFORM.

Re: report performance

Former Member — Wed, 07 Feb 2007 16:44:37 GMT

Hi,

Try moving the SQL to outside the loop..

LOOP AT T_CDHDR.

READ TABLE ITAB WITH KEY VBELNS = T_CDHDR-OBJECTID.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING ITAB TO ITAB2.

ITAB2-ERNAMB = T_CDHDR-USERNAME.

ITAB2-ERDATB = T_CDHDR-UDATE.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMES,

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

ENDSELECT.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMEB,

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

ENDSELECT.

APPEND ITAB2.

ENDIF.

Thanks,

Naren

ENDLOOP.

Re: report performance

Former Member — Wed, 07 Feb 2007 16:50:47 GMT

Hi Deepthi,

Try to avoide the use of Select & endselect in loop. If it is very necessary then go for select single.

LOOP AT T_CDHDR.

READ TABLE ITAB WITH KEY VBELNS = T_CDHDR-OBJECTID.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING ITAB TO ITAB2.

ITAB2-ERNAMB = T_CDHDR-USERNAME.

ITAB2-ERDATB = T_CDHDR-UDATE.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMES,

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

ENDSELECT.

SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMEB,

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

ENDSELECT.

APPEND ITAB2.

ENDIF.

ENDLOOP.

Asvhen

Re: report performance

Former Member — Wed, 07 Feb 2007 16:50:48 GMT

Have you run a performance trace (ST05) to find out exactly where the problem is?

Rob

Re: report performance

Former Member — Wed, 07 Feb 2007 16:52:16 GMT

Hi Deepthi,

Avoid SELECTs inside LOOP....ENDLOOP.

In your case get the data from USER_ADDR into an internal table and use READ statement inside LOOP...ENDLOOP.

This may solve your problem.

SELECT BNAME NAME_FIRST NAME_LAST

FROM USER_ADDR

INTO TABLE IT_USER_ADDR.

LOOP.

AT T_CDHDR.

READ TABLE ITAB WITH KEY VBELNS = T_CDHDR-OBJECTID.

IF SY-SUBRC = 0.

MOVE-CORRESPONDING ITAB TO ITAB2.

ITAB2-ERNAMB = T_CDHDR-USERNAME.

ITAB2-ERDATB = T_CDHDR-UDATE.

*SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMES,

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

*ENDSELECT.

READ TABLE IT_USER_ADDR WITH KEY BNAME = ITAB2-ERNAMS.

ITAB2-FIRSTNAMES = IT_USER_ADDR-NAME_FIRST.

ITAB2-LASTNAMES = IT_USER_ADDR-NAME_LAST.

*SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMEB,

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

*ENDSELECT.

READ TABLE IT_USER_ADDR WITH KEY BNAME = ITAB2-ERNAMB.

ITAB2-FIRSTNAMES = IT_USER_ADDR-NAME_FIRST.

ITAB2-LASTNAMES = IT_USER_ADDR-NAME_LAST.

APPEND ITAB2.

ENDIF.

ENDLOOP.

Thanks,

Vinay

Re: report performance

Former Member — Wed, 07 Feb 2007 16:55:22 GMT

Additionally, do this:


SORT itab BY vbelns.
LOOP AT t_cdhdr.
  READ TABLE itab WITH KEY vbelns = t_cdhdr-objectid
    BINARY SEARCH.
  IF sy-subrc = 0.
  ENDIF.
"
"
"
ENDLOOP.

Rob

Re: report performance

Former Member — Wed, 07 Feb 2007 16:58:45 GMT

Be sure to wrap your Select from CDPOS in an IF statement like you did when Selecting from CDHDR:


<b>IF NOT T_CDHDR[] IS INITIAL.</b>
  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.
<b>ENDIF.</b>

Re: report performance

Former Member — Wed, 07 Feb 2007 17:14:53 GMT

hi Deepthi,

try this code:

FORM GET_DATA.

DATA : TABIX LIKE SY-TABIX.

"new itabs
DATA: ITAB_USER_ADDR TYPE TABLE OF USER_ADDR WITH HEADER LINE,
           ITAB_USER_ADDR_2 TYPE TABLE OF USER_ADDR WITH HEADER LINE.

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

MOVE ITAB-VBELNS TO T_OBJECT-OBJECTID.

APPEND T_OBJECT.

ENDLOOP.

SORT T_OBJECT BY OBJECTID.

DELETE ADJACENT DUPLICATES FROM T_OBJECT COMPARING OBJECTID.

Get the CDHDR.

IF NOT T_OBJECT[] IS INITIAL.

SELECT * FROM CDHDR

INTO TABLE T_CDHDR

FOR ALL ENTRIES IN T_OBJECT

WHERE OBJECTCLAS = 'VERKBELEG'

AND OBJECTID = T_OBJECT-OBJECTID

AND UDATE >= S_ERDAT-LOW.

ENDIF.

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.

LOOP AT T_CDHDR.

TABIX = SY-TABIX.

CLEAR: T_CDPOS.

READ TABLE T_CDPOS WITH KEY OBJECTID = T_CDHDR-OBJECTID

CHANGENR = T_CDHDR-CHANGENR BINARY SEARCH.

IF SY-SUBRC NE 0.

DELETE T_CDHDR INDEX TABIX.

ENDIF.

ENDLOOP.

SORT T_CDHDR BY OBJECTID ASCENDING UDATE UTIME DESCENDING.

DELETE ADJACENT DUPLICATES FROM T_CDHDR COMPARING ALL FIELDS.

"Revised code section
LOOP AT T_CDHDR.
   READ TABLE ITAB WITH KEY VBELNS = T_CDHDR-OBJECTID.
   IF SY-SUBRC = 0.
        MOVE-CORRESPONDING ITAB TO ITAB2.
       ITAB2-ERNAMB = T_CDHDR-USERNAME.
       ITAB2-ERDATB = T_CDHDR-UDATE.
       APPEND ITAB2.
    ENDIF.
ENDLOOP.

SELECT NAME_FIRST NAME_LAST INTO (ITAB_USER_ADDR-FIRSTNAMES,
ITAB_USER_ADDR-LASTNAMES) FROM USER_ADDR  FOR ALL ENTRIES IN ITBA2 WHERE BNAME = ITAB2-ERNAMS.


SELECT NAME_FIRST NAME_LAST INTO (ITAB_USER_ADDR_2-FIRSTNAMEB,
ITAB_USER_ADDR_2-LASTNAMEB) FROM USER_ADDR FOR ALL ENTRIES IN ITBA2  WHERE BNAME = ITAB2-ERNAMB.

CLEAR TABIX.
LOOP AT ITAB2.
     TABIX = SY-TABIX.
     WA_ITAB2 = ITAB2.  "declare a work area of the same type of itab2
     READ TABLE ITAB_USER_ADDR WITH KEY BNAME = ITAB2-ERNAMS.
     IF SY-SUBRC = 0.
             ITAB2-FIRSTNAMES = ITAB_USER_ADDR-FIRSTNAMES .
             ITAB2-LASTNAMES = ITAB_USER_ADDR-LASTNAMES .
     ENDIF.
     READ TABLE ITAB_USER_ADDR_2 WITH KEY BNAME = ITAB2-ERNAMB.
     IF SY-SUBRC = 0.
             ITAB2-FIRSTNAMEB = ITAB_USER_ADDR_2-FIRSTNAMEB .
             ITAB2-LASTNAMEB = ITAB_USER_ADDR_2-LASTNAMEB  .
     ENDIF.
     CHECK NOT WA_ITAB2 EQ ITAB2.
     MODIFY ITAB2 INDEX LV-TABIX.   
ENDLOOP.

Hope this helps,

Sajan Joseph.

Re: report performance

Former Member — Wed, 07 Feb 2007 17:17:15 GMT

good

test your program with following things,

1- Extended syntax check

2-Check with tcode SLIN

3-Check with ST05

fix the errors or messages and run the program again.

Thanks

mrutun^

Re: report performance

Former Member — Wed, 07 Feb 2007 18:06:00 GMT

most of the time is being taken for select statements from cdhdr and cdpos tables.

Re: report performance

Former Member — Wed, 07 Feb 2007 18:11:26 GMT

Hi Deepthi,

If most of the time is taken by cdpos & chhdr then you need to avoid the

SELECT * statements. If you are using some particular fields then specify those fields instead of *.

Ashven

Re: report performance

Former Member — Wed, 07 Feb 2007 18:12:00 GMT

Hi,

Why do you want to have this:

LOOP AT ITAB.

CALL FUNCTION 'CONVERSION_EXIT_ALPHA_INPUT'

EXPORTING

INPUT = ITAB-VBELNS

IMPORTING

OUTPUT = ITAB-VBELNS.

MOVE ITAB-VBELNS TO T_OBJECT-OBJECTID.

APPEND T_OBJECT.

ENDLOOP.

From the table itself you will get the VBELN with prefix zero only. You need not input again zeroes.

Regards

Subramanian

Re: report performance

Former Member — Wed, 07 Feb 2007 20:28:52 GMT

Thanks for your replies . I applied all the changes to my program . Its working somewhat fine now.

Re: report performance

Former Member — Wed, 07 Feb 2007 20:30:24 GMT

I am not able to use most of the features of ST05 ,doesnt have authorization.