problem : ALV report taking a lot of time to execute

[Former Member]

hii

Am facing problem in my ALV report which is taking a lot of Time to execute

Below is my Code :

START-OF-SELECTION.

• Retrieving data from table

PERFORM sub_fill_itab.

************************************************************************

• END-OF-SELECTION EVENT *

************************************************************************

END-OF-SELECTION.

PERFORM sub_fill_final.

• Prepare fieldcatalog

PERFORM sub_prepare_fieldcat.

• prepare layout

PERFORM sub_layout.

• Display data in the output

PERFORM sub_display_data.

&----

---

*& Form sub_fill_itab

&----

---

• text

----

---

FORM sub_fill_itab .

SELECT bukrs

gjahr

budat

belnr

INTO TABLE it_bkpf

FROM bkpf

WHERE budat IN s_budat AND bukrs EQ p_bukrs

AND gjahr IN s_gjahr .

IF sy-subrc EQ 0.

• SORT it_bkpf BY bukrs gjahr belnr.

ELSE.

MESSAGE i007(zwb).

LEAVE LIST-PROCESSING.

ENDIF.

SELECT bukrs

belnr

xref3

gjahr

buzid

hkont

matnr

menge

dmbtr

lifnr

ebeln

shkzg

buzei

INTO TABLE it_bseg

FROM bseg

FOR ALL ENTRIES IN it_bkpf

WHERE bukrs = it_bkpf-bukrs AND gjahr = it_bkpf-gjahr

AND belnr = it_bkpf-belnr AND hkont IN s_hkont AND

ebeln IN s_ebeln.

IF sy-subrc EQ 0.

SORT it_bseg BY bukrs xref3 ebeln hkont gjahr.

ENDIF.

SELECT bukrs

xref3

gjahr

lifnr

belnr

ebeln

INTO TABLE it_bseg1

FROM bseg

FOR ALL ENTRIES IN it_bkpf

WHERE bukrs = it_bkpf-bukrs AND gjahr = it_bkpf-gjahr

AND belnr = it_bkpf-belnr . " AND hkont IN s_hkont AND ebeln IN s_ebeln.

IF sy-subrc EQ 0.

DELETE it_bseg1 WHERE lifnr = ''.

ENDIF.

SELECT lifnr

name1

INTO TABLE it_lfa1

FROM lfa1

FOR ALL ENTRIES IN it_bseg1

WHERE lifnr = it_bseg1-lifnr.

IF sy-subrc EQ 0.

DELETE ADJACENT DUPLICATES FROM it_lfa1 .

ENDIF.

&----

---

*& Form sub_fill_final

&----

---

• text

----

---

FORM sub_fill_final .

LOOP AT it_bseg INTO wa_bseg.

*v_year1 = wa_bseg-xref3+0(4).

MOVE: wa_bseg-bukrs TO wa_bseg2-bukrs,

wa_bseg-belnr TO wa_bseg2-belnr,

• wa_bseg-xref3

wa_bseg-gjahr TO wa_bseg2-gjahr,

• wa_bseg-buzid

wa_bseg-hkont TO wa_bseg2-hkont,

wa_bseg-matnr TO wa_bseg2-matnr,

• wa_bseg-menge

wa_bseg-dmbtr TO wa_bseg2-dmbtr,

• wa_bseg-lifnr TO wa_bseg2-lifnr,

wa_bseg-ebeln TO wa_bseg2-ebeln,

wa_bseg-shkzg TO wa_bseg2-shkzg.

READ TABLE it_bseg1 INTO wa_bseg1 WITH KEY bukrs = wa_bseg-bukrs

gjahr = wa_bseg-gjahr

belnr = wa_bseg-belnr.

IF sy-subrc EQ 0.

MOVE: wa_bseg1-lifnr TO wa_bseg2-lifnr.

• wa_bseg1-ebeln TO wa_bseg2-ebeln.

ENDIF.

APPEND wa_bseg2 TO it_bseg2.

CLEAR: wa_bseg2,

wa_bseg1,

wa_bseg.

ENDLOOP.

REFRESH: it_bseg,

it_bseg1.

IF sy-subrc EQ 0.

DELETE it_bseg2 WHERE lifnr NOT IN s_lifnr.

ENDIF.

LOOP AT it_bseg2 INTO wa_bseg2.

IF wa_bseg2-shkzg EQ 'H'.

v_val = - wa_bseg2-dmbtr.

ELSE.

v_val = wa_bseg2-dmbtr.

ENDIF.

MOVE: wa_bseg2-bukrs TO wa_final-bukrs,

wa_bseg2-belnr TO wa_final-belnr,

• wa_bseg-xref3

wa_bseg2-gjahr TO wa_final-gjahr,

• wa_bseg-buzid

wa_bseg2-hkont TO wa_final-hkont,

wa_bseg2-matnr TO wa_final-matnr,

• wa_bseg-menge

v_val TO wa_final-dmbtr,

wa_bseg2-lifnr TO wa_final-lifnr,

wa_bseg2-ebeln TO wa_final-ebeln,

wa_bseg2-shkzg TO wa_final-shkzg.

READ TABLE it_bkpf INTO wa_bkpf WITH KEY bukrs = wa_bseg2-bukrs

gjahr = wa_bseg2-gjahr

belnr = wa_bseg2-belnr.

IF sy-subrc EQ 0.

MOVE: wa_bkpf-budat TO wa_final-budat.

ENDIF.

READ TABLE it_lfa1 INTO wa_lfa1 WITH KEY lifnr = wa_bseg2-lifnr.

IF sy-subrc EQ 0.

MOVE: wa_lfa1-name1 TO wa_final-name1.

ENDIF.

APPEND wa_final TO it_final.

CLEAR: wa_bseg2,

wa_bkpf,

wa_final,

wa_lfa1.

ENDLOOP.

REFRESH: it_bseg2,

it_bkpf,

it_lfa1.

FREE: it_bseg2,

it_bkpf,

it_lfa1.

IF sy-subrc EQ 0.

SORT it_final BY ebeln.

ENDIF.

ENDFORM. " sub_fill_final

*******************************************************************

The Sub-Routine "sub_fill_final" taking a lot of Time as there are lakhs of Records getting populated in Internal table "it_bseg" .

Plz guide me up in Improvimg the codeing part so that it will take less time to execute .

Thanks in Advance

Jack

[ThomasZloch]

1) include BSTAT = space in the WHERE condition of the BKPF select (allows use of secondary index BKPF~2)

2) add BINARY SEARCH to all READ statements and sort the tables according to the access fields beforehand

Thomas

[Former Member]

Hi

when used FOR ALL ENTRIES condition before that u ve to chek the table is not initial. after that use the for all entries condition

if it_bkpf is not initial.

select f1 f2 f3 into table

from bseg into corresponding fields of table it_bseg

where......

<cond>

endif.

all the READ statement should be added by BINARY SEARCH.

modify statements should be replaced by

modify...transporting.

rewards if useful.....

regards

sachhi

[Former Member]

Hi,

Try this.

1.Before using the table in the for all entries, check whether it is initial or not.

2. Use binary search in the read stmts.

Sharin.

[Former Member]

hii

i hav changed the logic like below .. used binary search logic..

But its taking 3 times more time as compared to my previous coding

FORM sub_fill_itab .

SELECT bukrs

gjahr

budat

belnr

INTO TABLE it_bkpf

FROM bkpf

WHERE budat IN s_budat AND bukrs EQ p_bukrs

AND gjahr IN s_gjahr .

IF sy-subrc EQ 0.

SORT it_bkpf BY bukrs gjahr belnr.

ELSE.

MESSAGE i007(zwb).

LEAVE LIST-PROCESSING.

ENDIF.

SELECT bukrs

belnr

xref3

gjahr

buzid

hkont

matnr

menge

dmbtr

lifnr

ebeln

shkzg

buzei

INTO TABLE it_bseg

FROM bseg

FOR ALL ENTRIES IN it_bkpf

WHERE bukrs = it_bkpf-bukrs AND gjahr = it_bkpf-gjahr

AND belnr = it_bkpf-belnr AND hkont IN s_hkont AND

ebeln IN s_ebeln.

IF sy-subrc EQ 0.

• DELETE it_bseg WHERE hkont NOT IN s_hkont.

SORT it_bseg BY bukrs xref3 ebeln hkont gjahr.

ENDIF.

*

SELECT bukrs

xref3

gjahr

lifnr

belnr

ebeln

INTO TABLE it_bseg1

FROM bseg

FOR ALL ENTRIES IN it_bkpf

WHERE bukrs = it_bkpf-bukrs AND gjahr = it_bkpf-gjahr

AND belnr = it_bkpf-belnr . " AND hkont IN s_hkont AND ebeln IN s_ebeln.

IF sy-subrc EQ 0.

SORT it_bseg1 BY lifnr belnr ebeln .

DELETE it_bseg1 WHERE lifnr = ''.

ENDIF.

SELECT lifnr

name1

INTO TABLE it_lfa1

FROM lfa1

FOR ALL ENTRIES IN it_bseg1

WHERE lifnr = it_bseg1-lifnr.

IF sy-subrc EQ 0.

SORT it_lfa1 BY lifnr .

DELETE ADJACENT DUPLICATES FROM it_lfa1 .

ENDIF.

ENDFORM.

FORM sub_fill_final .

LOOP AT it_bseg INTO wa_bseg.

*v_year1 = wa_bseg-xref3+0(4).

MOVE: wa_bseg-bukrs TO wa_bseg2-bukrs,

wa_bseg-belnr TO wa_bseg2-belnr,

• wa_bseg-xref3

wa_bseg-gjahr TO wa_bseg2-gjahr,

• wa_bseg-buzid

wa_bseg-hkont TO wa_bseg2-hkont,

wa_bseg-matnr TO wa_bseg2-matnr,

• wa_bseg-menge

wa_bseg-dmbtr TO wa_bseg2-dmbtr,

• wa_bseg-lifnr TO wa_bseg2-lifnr,

wa_bseg-ebeln TO wa_bseg2-ebeln,

wa_bseg-shkzg TO wa_bseg2-shkzg.

SORT it_bseg1 BY belnr ebeln lifnr .

READ TABLE it_bseg1 INTO wa_bseg1 WITH KEY bukrs = wa_bseg-bukrs

gjahr = wa_bseg-gjahr

belnr = wa_bseg-belnr

BINARY SEARCH .

IF sy-subrc EQ 0.

MOVE: wa_bseg1-lifnr TO wa_bseg2-lifnr.

• wa_bseg1-ebeln TO wa_bseg2-ebeln.

ENDIF.

APPEND wa_bseg2 TO it_bseg2.

CLEAR: wa_bseg2,

wa_bseg1,

wa_bseg.

ENDLOOP.

REFRESH: it_bseg,

it_bseg1.

IF sy-subrc EQ 0.

DELETE it_bseg2 WHERE lifnr NOT IN s_lifnr.

ENDIF.

LOOP AT it_bseg2 INTO wa_bseg2.

IF wa_bseg2-shkzg EQ 'H'.

v_val = - wa_bseg2-dmbtr.

ELSE.

v_val = wa_bseg2-dmbtr.

ENDIF.

MOVE: wa_bseg2-bukrs TO wa_final-bukrs,

wa_bseg2-belnr TO wa_final-belnr,

• wa_bseg-xref3

wa_bseg2-gjahr TO wa_final-gjahr,

• wa_bseg-buzid

wa_bseg2-hkont TO wa_final-hkont,

wa_bseg2-matnr TO wa_final-matnr,

• wa_bseg-menge

v_val TO wa_final-dmbtr,

wa_bseg2-lifnr TO wa_final-lifnr,

wa_bseg2-ebeln TO wa_final-ebeln,

wa_bseg2-shkzg TO wa_final-shkzg.

SORT it_bkpf by belnr budat gjahr .

READ TABLE it_bkpf INTO wa_bkpf WITH KEY bukrs = wa_bseg2-bukrs

gjahr = wa_bseg2-gjahr

belnr = wa_bseg2-belnr

BINARY SEARCH .

IF sy-subrc EQ 0.

MOVE: wa_bkpf-budat TO wa_final-budat.

ENDIF.

sort it_lfa1 by lifnr .

READ TABLE it_lfa1 INTO wa_lfa1 WITH KEY lifnr = wa_bseg2-lifnr BINARY SEARCH.

IF sy-subrc EQ 0.

MOVE: wa_lfa1-name1 TO wa_final-name1.

ENDIF.

APPEND wa_final TO it_final.

CLEAR: wa_bseg2,

wa_bkpf,

wa_final,

wa_lfa1.

ENDLOOP.

REFRESH: it_bseg2,

it_bkpf,

it_lfa1.

FREE: it_bseg2,

it_bkpf,

it_lfa1.

IF sy-subrc EQ 0.

SORT it_final BY ebeln.

ENDIF.

ENDFORM.

kindly help me out ...

Thanks in Advance

JACK

[Former Member]

>

> i hav changed the logic like below .. used binary search logic..
> 
> But its taking 3 times more time as compared to my previous coding

That's because you didn't take Thomas's advice. Add BSTAT to the WHERE in the first SELECT.

Also in your FORM, you are sorting your internal tables before each READ. Just do the sorts once before the loop.

Rob

[poornima_chepur2]

Instead of selecting directly from BSEG table, select from the secondary index tables (BSIF, BSIK, BSAK, etc). That will be much faster.

[Former Member]

>

> Instead of selecting directly from BSEG table, select from the secondary index tables (BSIF, BSIK, BSAK, etc). That will be much faster.

There's nothing wrong with SELECTing from BSEG, so long as you use the index and these SELECTs do.

Rob

[P561888]

Hi Jack,

Try to create the one database view with max. fields what are using bcoz select take the more time if u have the more number of records in the table. so try to create one View so that it will reduce the time..

Regards,

Bharani

[Former Member]

>

> Try to create the one database view with max. fields what are using bcoz select take the more time if u have the more number of records in the table. so try to create one View so that it will reduce the time..

Bharani - what tables do you think he should create the view on?

Rob