report performance

[Former Member]

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.

*

[Former Member]

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.

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

APPEND ITAB2.

ENDIF.

Thanks,

Naren

ENDLOOP.

[Former Member]

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.

<b>SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMES,

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

ENDSELECT.</b>

<b>SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMEB,

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

ENDSELECT.</b>

APPEND ITAB2.

ENDIF.

ENDLOOP.

Asvhen

[Former Member]

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

Rob

[Former Member]

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

[Former Member]

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.

<b>SELECT BNAME NAME_FIRST NAME_LAST

FROM USER_ADDR

INTO TABLE IT_USER_ADDR.</b>

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.

<b>READ TABLE IT_USER_ADDR WITH KEY BNAME = ITAB2-ERNAMS.

ITAB2-FIRSTNAMES = IT_USER_ADDR-NAME_FIRST.

ITAB2-LASTNAMES = IT_USER_ADDR-NAME_LAST.</b>

*SELECT NAME_FIRST NAME_LAST INTO (ITAB2-FIRSTNAMEB,

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

*ENDSELECT.

<b>READ TABLE IT_USER_ADDR WITH KEY BNAME = ITAB2-ERNAMB.

ITAB2-FIRSTNAMES = IT_USER_ADDR-NAME_FIRST.

ITAB2-LASTNAMES = IT_USER_ADDR-NAME_LAST.</b>

APPEND ITAB2.

ENDIF.

ENDLOOP.

Thanks,

Vinay

[Former Member]

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>

[Former Member]

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.

[Former Member]

hi

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^

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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