Re: Report taking long time to execute, performanc...

Former Member · ‎2007 Dec 20

Hi All,

My report is talking long time to execute, can some one please help me with it.

MOVE LV_TOTAL TO IOUT-KWERT. is taking long time to move the value.

And also I want cust name to get when there is a new customer, does at new and endat work for this.

Thanks,

Veni.

REPORT ZSDR_SALES_VALUE NO STANDARD PAGE HEADING.

*
DESCRIPTION: This report gives the gross and net sales values based *
on date from the selection screen. *

TABLES: vbrk, vbrp, konv, kna1.

INCLUDE zalvi_grid_display.

DATA: g_repid LIKE sy-repid.

DATA: g_erdat(35) type c.

----

Input Tables

----

DATA: BEGIN OF ivbrk OCCURS 0,

vbeln LIKE vbrk-vbeln,

knumv LIKE vbrk-knumv,

netwr LIKE vbrk-netwr,

erdat like vbrk-erdat,

kunag LIKE vbrk-kunag,

END OF ivbrk.

DATA: BEGIN OF ikonv OCCURS 0,

knumv LIKE konv-knumv,

kwert LIKE konv-kwert,

END OF ikonv.

*DATA: BEGIN OF ikna1 OCCURS 0,

kunnr LIKE kna1-kunnr,
name1 LIKE kna1-name1,
END OF ikna1.

----

Output Table

----

DATA: BEGIN OF iout OCCURS 0,

kunag LIKE vbrk-kunag,

name1 LIKE kna1-name1,

netwr LIKE vbrk-netwr,

kwert LIKE konv-kwert,

END OF iout.

----

SELECTION SCREEN

----

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_erdat FOR vbrk-erdat OBLIGATORY.

Parameters: p_kunag like vbrk-kunag.

SELECTION-SCREEN END OF BLOCK b1.

----

START-OF-SELECTION

----

START-OF-SELECTION.

PERFORM get_data.

PERFORM process_data.

PERFORM sub_alv_routines.

PERFORM comment_build USING t_list_top_of_page[].

g_repid = sy-repid.

PERFORM alv_build_fieldcat USING 'IOUT' g_repid.

PERFORM sub_modify_field_cat.

PERFORM sub_call_alv_grid TABLES iout.

&----

*& Form getdata

&----

text

----

--> p1 text
<-- p2 text

----

FORM get_data.

SELECT vbeln knumv netwr erdat kunag

FROM vbrk

INTO TABLE ivbrk

WHERE erdat IN s_erdat

and kunag = p_kunag

and fkart = 'F2'.

IF sy-subrc = 0.

SELECT knumv kwert

FROM konv

INTO TABLE ikonv

FOR ALL ENTRIES IN ivbrk

WHERE knumv = ivbrk-knumv

AND kschl = 'PR00'.

ENDIF.

ENDFORM. " get_data

&----

*& Form process_data

&----

text

----

--> p1 text
<-- p2 text

----

FORM process_data.

DATA: lv_name1 LIKE kna1-name1,

LV_kwert like konv-kwert,

lv_total like konv-kwert.

sort ivbrk by kunag.

sort ikonv by knumv.

LOOP AT ivbrk.

MOVE ivbrk-netwr TO iout-netwr.

MOVE ivbrk-kunag TO iout-kunag.

READ TABLE ikonv WITH KEY knumv = ivbrk-knumv.

IF sy-subrc = 0.

loop at ikonv.

LV_kwert = ikonv-kwert.

lv_total = LV_kwert + lv_total.

clear lv_kwert.

endloop.

ENDIF.

Move lv_total to iout-kwert.

at new kunag.

SELECT SINGLE name1

FROM kna1 INTO lv_name1 WHERE kunnr = ivbrk-kunag.

IF sy-subrc = 0.

MOVE lv_name1 TO iout-name1.

ENDIF.

endat.

append iout.

CLEAR iout.

CLEAR ivbrk.

clear ikonv.

CLEAR lv_name1.

clear lv_total.

ENDLOOP.

ENDFORM. " process_data

former_member156446 · ‎2007 Dec 20

sort ikonv by knumv.

READ TABLE ikonv WITH KEY knumv = ivbrk-knumv binary search.

IOUT-KWERT = LV_TOTAL.

clear lv_total.

award points if useful.

Edited by: jackandjay on Dec 20, 2007 3:29 PM

Former Member · ‎2007 Dec 20

Hi Jack,

There is some problem with the loop on IKONV. For Ex:

IVBRK

VBELN KNUMV NETWR KUNAG

71563233 3332144 1000.00 1004

71563234 3332145 2000.00 1004

-

3000.00

IKONV

KNUMV KPOSN KWERT

3332144 10 1500.00

3332145 10 2500.00

-

4000.00

For each IVBRK-KNUMV entire IKONV is executing so for knumv 3332144 two values are adding up from KONV and for 3332145 again two values are adding up, so the total KWERT is comming as 8000.00 instead of 4000.00.

The loop on KONV has to be executed only if we have more no of KPOSN's for same KNUMV like

KNUMV KPOSN KWERT

3332144 10 1500.00

3332144 20 2500.00

3332144 30 3000.00

3332144 40 2000.00

Please help me.

Thanks,

Veni.

Edited by: veni reddy on Dec 20, 2007 10:29 PM

former_member156446 · ‎2007 Dec 20

1. 3332144 10 1500.00

> 500

> 800

> 200

2. 3332145 10 2500.00

> 500

> 1000

u wanted the sum based on knumv is it.. so that what happening in read ur getting 3332144 (knumv) for that 500 (Kwert) + 800 + 200 = 1500 this is what is coming in lv_total. and that is being moved to iout.

Former Member · ‎2007 Dec 20

Hi Rob,

I added Binery search also, but not working .

Hi Jack,

For 3332144 it is adding up 5008002005001000+1000 and for

3332145 also it is adding up 5008002005001000+1000 and entire total 8000 is comming for that customer instead of 1500 + 2500 = 4000.

Please guide me.

sort ikonv by knumv.

READ TABLE ikonv WITH KEY knumv = ivbrk-knumv binary search.

IF sy-subrc = 0.

loop at ikonv.

LV_kwert = ikonv-kwert.

lv_total = LV_kwert + lv_total.

clear lv_kwert.

endloop.

ENDIF.

Move lv_total to iout-kwert.

Thanks,

Veni.

Edited by: veni reddy on Dec 20, 2007 10:47 PM

former_member194669 · ‎2007 Dec 20


LOOP AT ivbrk.

MOVE ivbrk-netwr TO iout-netwr.
MOVE ivbrk-kunag TO iout-kunag.

READ TABLE ikonv WITH KEY knumv = ivbrk-knumv.
IF sy-subrc = 0.
loop at ikonv where knumv = ivbrk-knumv. "<<<<<Check this

LV_kwert = ikonv-kwert.
lv_total = LV_kwert + lv_total.
clear lv_kwert.

endloop.
ENDIF.

Move lv_total to iout-kwert.

at new kunag.
SELECT SINGLE name1
FROM kna1 INTO lv_name1 WHERE kunnr = ivbrk-kunag.
IF sy-subrc = 0.
MOVE lv_name1 TO iout-name1.
ENDIF.
endat.

append iout.
CLEAR iout.
CLEAR ivbrk.
clear ikonv.
CLEAR lv_name1.
clear lv_total.

ENDLOOP.

a®

Former Member · ‎2007 Dec 20

Well, it is helping, but there are other problems that you need to deal with as well.

One at a time.

Rob

Former Member · ‎2007 Dec 20

Hi ars,

Thank you the loop is working properly now, but with AT NEW also cust name stmt is executing many times. How can I make this stmt to execute only once for each customer.

at new kunag.

SELECT SINGLE name1

FROM kna1 INTO lv_name1 WHERE kunnr = ivbrk-kunag.

IF sy-subrc = 0.

MOVE lv_name1 TO iout-name1.

ENDIF.

endat.

For Ex: Customer num 11010 has 7 invoices so 7 VBRK-KNUMV and this custname is executing 7 times.

Please help me with it.

Thanks,

Veni.

Former Member · ‎2007 Dec 21

Hi All,

I was checking data for one year and there were 233,000 records and for one customer there are 153,000 records and for another customer there are 65,000 records in VBRK, the KNA1-NAME1 also executes this many times. Can I get NAME1 only once for each customer.

Please help me to improve the performance of my program. I ran the report for one year and it is taking for ever to execute.

Thanks,

Veni.

REPORT ZSDR_SALES_VALUE NO STANDARD PAGE HEADING.

DESCRIPTION: This report gives the gross and net sales values based *
on date from the selection screen. *

TABLES: vbrk, vbrp, konv, kna1.

INCLUDE zalvi_grid_display.

DATA: g_repid LIKE sy-repid.

DATA: g_erdat(35) type c,

lv_name1 LIKE kna1-name1,

lv_kwert like konv-kwert,

lv_total like konv-kwert.

----

Input Tables

----

DATA: BEGIN OF ivbrk OCCURS 0,

vbeln LIKE vbrk-vbeln,

knumv LIKE vbrk-knumv,

netwr LIKE vbrk-netwr,

erdat like vbrk-erdat,

kunag LIKE vbrk-kunag,

END OF ivbrk.

DATA: BEGIN OF ikonv OCCURS 0,

knumv LIKE konv-knumv,

kposn LIKE konv-kposn,

kwert LIKE konv-kwert,

END OF ikonv.

DATA: BEGIN OF ikna1 OCCURS 0,

kunnr LIKE kna1-kunnr,

name1 LIKE kna1-name1,

END OF ikna1.

----

Output Table

----

DATA: BEGIN OF iout OCCURS 0,

kunag LIKE vbrk-kunag,

name1 LIKE kna1-name1,

kwert LIKE konv-kwert,

netwr LIKE vbrk-netwr,

END OF iout.

----

SELECTION SCREEN

----

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_erdat FOR vbrk-erdat OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b1.

----

START-OF-SELECTION

----

START-OF-SELECTION.

PERFORM get_data.

PERFORM process_data.

PERFORM sub_alv_routines.

PERFORM comment_build USING t_list_top_of_page[].

g_repid = sy-repid.

PERFORM alv_build_fieldcat USING 'IOUT' g_repid.

PERFORM sub_modify_field_cat.

PERFORM sub_call_alv_grid TABLES iout.

&----

*& Form getdata

&----

text

----

--> p1 text
<-- p2 text

----

FORM get_data.

SELECT vbeln knumv netwr erdat kunag

FROM vbrk

INTO TABLE ivbrk

WHERE erdat IN s_erdat

and fkart = 'F2'.

IF sy-subrc = 0.

SELECT knumv kposn kwert

FROM konv

INTO TABLE ikonv

FOR ALL ENTRIES IN ivbrk

WHERE knumv = ivbrk-knumv

AND stunr = '011' //Added this line to improve performance

AND zaehk = '01' //Added this line to improve performance

AND kschl = 'PR00'.

ENDIF.

IF sy-subrc = 0.

SELECT kunnr name1

FROM kna1

INTO table ikna1

FOR ALL ENTRIES IN ivbrk

WHERE kunnr = ivbrk-kunag.

endif.

ENDFORM. " get_data

&----

*& Form process_data

&----

text

----

--> p1 text
<-- p2 text

----

FORM process_data.

sort ivbrk by kunag.

LOOP AT ivbrk.

MOVE ivbrk-netwr TO iout-netwr.

MOVE ivbrk-kunag TO iout-kunag.

READ TABLE ikonv WITH KEY knumv = ivbrk-knumv binary search.

IF sy-subrc = 0.

loop at ikonv where knumv = ivbrk-knumv.

LV_kwert = ikonv-kwert.

lv_total = LV_kwert + lv_total.

clear lv_kwert.

endloop.

ENDIF.

Move lv_total to iout-kwert.

SELECT SINGLE name1
FROM kna1 INTO lv_name1 WHERE kunnr = ivbrk-kunag.

READ TABLE ikna1 WITH KEY kunnr = ivbrk-kunag.

IF sy-subrc = 0.

MOVE ikna1-name1 TO iout-name1.

ENDIF.

collect iout.

CLEAR iout.

CLEAR ivbrk.

clear ikonv.

clear ikna1.

clear lv_total.

CLEAR lv_name1.

ENDLOOP.

ENDFORM. " process_data

Former Member · ‎2007 Dec 21

Any help please...

Thanks,

Veni.

former_member156446 · ‎2007 Dec 21

how long is it taking... just an approx time ?

Former Member · ‎2007 Dec 21

Hi Jack,

For transactions of one month it is taking around 3 min's for one year transactions I waited for 20 min and stopped the transaction.

Can I get cust name out of the loop and also any changes to improve the performance?

Thanks,

Veni.

former_member156446 · ‎2007 Dec 21

ur selection screen has only date as input.. so it looks reasonable to take that long.. if u can put some more primary key field it would be good, if u can...

2. try switching the loop and read table... i.e. read table ivbrk and loop ikonv... look like in selection of ivbrk u have very less in where clause so heavy amount of data is in table ivbrk..

LOOP AT ivbrk.

MOVE ivbrk-netwr TO iout-netwr.

MOVE ivbrk-kunag TO iout-kunag.

READ TABLE ikonv WITH KEY knumv = ivbrk-knumv binary search.

IF sy-subrc = 0.

loop at ikonv where knumv = ivbrk-knumv.

>(Keep ur main prog as it is and copy to another prg and test these >changes..)

award points if useful.

Edited by: jackandjay on Dec 21, 2007 2:00 PM

Former Member · ‎2007 Dec 20

For starters, do this:

  READ TABLE ikonv WITH KEY knumv = ivbrk-knumv
    binary search.             "<====

Rob

Former Member · ‎2007 Dec 22

Hi Veni,

Take out the routine to get customer name, out of the loop.

set up temp_kunag

sort the iout loop by kunag.

clear: lv_name1, temp_kunag.

loop at iout.

if temp_kunag <> iout-kunag

SELECT SINGLE name1

FROM kna1 INTO lv_name1 WHERE kunnr = ivbrk-kunag.

if sy-subrc = 0. "nothing to do here really

endif.

MOVE lv_name1 TO iout-name1.

modify iout.

temp_kunag = iout-kunag

endloop.

This will assign the name first time through and as long as the following loops are the same kunag it does not read from the database. Once Kunag is different it will read from the database and assign the value to temp until it changes again.

Your code might have worked except that your IVBRK loop needed to be sorted by kunag. Then your atnew endat could work. A separate loop would not take too much longer though.

Hope this helps.

Filler

Former Member · ‎2007 Dec 23

Hi Jack, Filler and Rahul,

I tried the At New and Endat after sorting, but it is still not working. Can you please help me.

Thanks,

Veni.

FORM process_data.

sort ivbrk by kunag knumv.

sort ikonv by knumv.

LOOP AT ivbrk.

MOVE ivbrk-netwr TO iout-netwr.

MOVE ivbrk-kunag TO iout-kunag.

at new kunag.

READ TABLE ikna1 WITH KEY kunnr = ivbrk-kunag.

IF sy-subrc = 0.

MOVE ikna1-name1 TO iout-name1.

ENDIF.

endat.

READ TABLE ikonv WITH KEY knumv = ivbrk-knumv binary search.

IF sy-subrc = 0.

loop at ikonv where knumv = ivbrk-knumv.

LV_kwert = ikonv-kwert.

lv_total = LV_kwert + lv_total.

clear lv_kwert.

endloop.

ENDIF.

Move lv_total to iout-kwert.

collect iout.

CLEAR iout.

CLEAR ivbrk.

clear ikonv.

clear ikna1.

clear lv_total.

CLEAR lv_name1.

ENDLOOP.

ENDFORM. " process_data

former_member156446 · ‎2007 Dec 24

hi neelu / veni

the last alternative is ask ur FC, may be if you can put some more selection options on the selection screen.

Former Member · ‎2007 Dec 31

Hi Guru's,

ON CHANGE OF worked out to get customer name only when there is a change in the cust num. But the report is still taking long time in PROCESS_DATA. Is there any way I can reduce the time. I tried joining VBRk and KONV but it did not work, do you think joining VBRK and KNA1 will help me.

Are there any other things that I can modify to get good performance.

Thanks,

Veni.

REPORT ZSDR_SALES_VALUE NO STANDARD PAGE HEADING.

TABLES: vbrk, vbrp, konv, kna1.

INCLUDE zalvi_grid_display.

DATA: g_repid LIKE sy-repid.

DATA: g_erdat(35) type c,

lv_name1 LIKE kna1-name1,

lv_kwert like konv-kwert,

lv_total like konv-kwert.

----

Input Tables

----

DATA: BEGIN OF ivbrk OCCURS 0,

vbeln LIKE vbrk-vbeln,

knumv LIKE vbrk-knumv,

netwr LIKE vbrk-netwr,

erdat like vbrk-erdat,

kunag LIKE vbrk-kunag,

END OF ivbrk.

DATA: BEGIN OF ikonv OCCURS 0,

knumv LIKE konv-knumv,

kposn LIKE konv-kposn,

kwert LIKE konv-kwert,

END OF ikonv.

DATA: BEGIN OF ikna1 OCCURS 0,

kunnr LIKE kna1-kunnr,

name1 LIKE kna1-name1,

END OF ikna1.

----

Output Table

----

DATA: BEGIN OF iout OCCURS 0,

kunag LIKE vbrk-kunag,

name1 LIKE kna1-name1,

kwert LIKE konv-kwert,

netwr LIKE vbrk-netwr,

END OF iout.

----

SELECTION SCREEN

----

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_erdat FOR vbrk-erdat OBLIGATORY.

s_kunag for vbrk-kunag.

SELECTION-SCREEN END OF BLOCK b1.

----

START-OF-SELECTION

----

START-OF-SELECTION.

PERFORM get_data.

PERFORM process_data.

PERFORM sub_alv_routines.

PERFORM comment_build USING t_list_top_of_page[].

g_repid = sy-repid.

PERFORM alv_build_fieldcat USING 'IOUT' g_repid.

PERFORM sub_modify_field_cat.

PERFORM sub_call_alv_grid TABLES iout.

&----

*& Form getdata

&----

text

----

--> p1 text
<-- p2 text

----

FORM get_data.

SELECT vbeln knumv netwr erdat kunag

FROM vbrk

INTO TABLE ivbrk

WHERE erdat IN s_erdat

and kunag in s_kunag

and fkart = 'F2'.

IF sy-subrc = 0.

SELECT knumv kposn kwert

FROM konv

INTO TABLE ikonv

FOR ALL ENTRIES IN ivbrk

WHERE knumv = ivbrk-knumv

AND stunr = '011'
AND zaehk = '01'

AND kschl = 'PR00'.

ENDIF.

IF sy-subrc = 0.

SELECT kunnr name1

FROM kna1

INTO table ikna1

FOR ALL ENTRIES IN ivbrk

WHERE kunnr = ivbrk-kunag.

endif.

ENDFORM. " get_data

&----

*& Form process_data

&----

text

----

--> p1 text
<-- p2 text

----

FORM process_data.

sort ivbrk by kunag knumv.

sort ikonv by knumv.

LOOP AT ivbrk.

MOVE ivbrk-netwr TO iout-netwr.

MOVE ivbrk-kunag TO iout-kunag.

at new kunag.

ON CHANGE OF ivbrk-kunag.

READ TABLE ikna1 WITH KEY kunnr = ivbrk-kunag.

IF sy-subrc = 0.

MOVE ikna1-name1 TO iout-name1.

ENDIF.

ENDON.

endat.

READ TABLE ikonv WITH KEY knumv = ivbrk-knumv binary search.

IF sy-subrc = 0.

loop at ikonv where knumv = ivbrk-knumv.

LV_kwert = ikonv-kwert.

lv_total = LV_kwert + lv_total.

clear lv_kwert.

endloop.

ENDIF.

Move lv_total to iout-kwert.

collect iout.

clear lv_total.

CLEAR lv_name1.

ENDLOOP.

CLEAR iout.

CLEAR ivbrk.

clear ikonv.

clear ikna1.

ENDFORM. " process_data

Former Member · ‎2008 Jan 01

Any help please....

Thanks,

Veni.

Former Member · ‎2008 Jan 02

Hi All,

I joined tables VBRK and KNA1, but it is still taking long time to execute if I run for one month it is taking 3 min and for one year I waited for 10 min and stopped the transaction. Are there any other things that I can modify to get good performance.

Thanks,

Veni.

FORM get_data.

SELECT vbrk_{vbeln vbrk}knumv vbrk_{netwr vbrk}erdat vbrk_{kunag kna1}name1

FROM vbrk

inner join kna1 on kna1_{kunnr = vbrk}kunag

INTO TABLE ivbrkk

WHERE vbrk~erdat IN s_erdat

and vbrk~fkart = 'F2'.

IF sy-subrc = 0.

SELECT knumv kposn kwert

FROM konv

INTO TABLE ikonv

FOR ALL ENTRIES IN ivbrkk

WHERE knumv = ivbrkk-knumv

AND stunr = '011'
AND zaehk = '01'

AND kschl = 'PR00'.

ENDIF.

ENDFORM. " get_data

&----

*& Form process_data

&----

text

----

--> p1 text
<-- p2 text

----

FORM process_data.

sort ivbrkk by kunag knumv.

sort ikonv by knumv.

LOOP AT ivbrkk.

MOVE ivbrkk-netwr TO iout-netwr.

MOVE ivbrkk-kunag TO iout-kunag.

MOVE ivbrkk-name1 TO iout-name1.

READ TABLE ikonv WITH KEY knumv = ivbrkk-knumv binary search.

IF sy-subrc = 0.

loop at ikonv where knumv = ivbrkk-knumv.

LV_kwert = ikonv-kwert.

lv_total = LV_kwert + lv_total.

clear lv_kwert.

endloop.

ENDIF.

Move lv_total to iout-kwert.

collect iout.

clear lv_total.

CLEAR lv_name1.

ENDLOOP.

CLEAR iout.

CLEAR ivbrkk.

clear ikonv.

ENDFORM. " process_data

former_member156446 · ‎2008 Jan 02

Hi Veni

s_erdat is the only selection screen available and which is not a primary key... and you dont have any other primary key field in ur selection screen... so if its taking long thats fine... the only reason its taking time is because ur not filtering stuff with primary key... so its totally fine for ur code to take time...

Former Member · ‎2008 Jan 03

Hi All,

My lead told me that, after getting data into IVBRK he asked me to put this data in to temporary table based on customer. Then he asked me to get the SUM(KWERT) in to local variable by using temp table by customer(so that this executes only 20 times if there are 20 customers). He said this will take very less time to execute.

I am confused with temp table by customer.

Can some one please help me to implement this.

Thanks,

Veni.

rahul2000 · ‎2007 Dec 22

Select Query inside the loop is not advisable.

What you can do is Select outside the loop,and use READ within the loop.

SELECT SINGLE name1

FROM kna1 INTO lv_name1 WHERE kunnr = ivbrk-kunag.

Do something like this within the loop:-

READ TABLE LV_NAME1 WHERE kunnr = ivbrk-kunag.

This will surely help to reduce the execution time.

Reward if it helps

Report taking long time to execute, performance