Urgent - Performance Optimization help

[Former Member]

Hi,

My current program has 12 fields, each fields being a different month. So field 12 is the current month, field 11 is last month and so on until field 1 (current - 11.)

In there, I also have to subtract one of our customers invoices using the following formula:

SELECT SUM( FKIMG )

FROM VBRP INNER JOIN VBAK

• ON VBRP_{VBELN = VBAP}VBELN

• AND VBRP_{POSNR = VBAP}POSNR

• INNER JOIN VBAK

ON VBRP_{VBELN = VBAK}VBELN

INTO GALLO_SUB

WHERE VBRP~MATNR = I_ALV-MATNR

AND VBRP~WERKS = I_ALV-WERKS

AND VBRP~ERDAT IN I_MONTH_RANGE

AND VBRP~PSTYV = 'TAB'

AND VBAK~AUART = 'ZDS'.

PAST_MONTH_VALUE = PAST_MONTH_VALUE - INVOICE.

On the screen, the user enters 4 plants (select option) and a vendor (there are other selection criteria, such as matnr, but for this case, we just have the plants and a vendor.)

For some reason, the code above keeps increasing in time spent every time the code is accessed. For example, the 1st time is 158 milliseconds, Then 175 milliseconds and so on. It finishes at 2 seconds, which make the program time out (600 seconds restriction for our sap system.)

Why does it increase each time? This is accessed for the looping month. So say the current month and year is 06/2007. 1st we get the monthly sales and then we get the invoices and calculate the difference. Its loop at the 12 months for each material (for this, i am guessing each material that is found in the vendor.)

Please help.

Thanks,

John

This is an extract of the log:

SELECT

SUM( T_00."FKIMG" )

FROM

"VBRP" T_00 INNER JOIN "VBAP" T_01 ON T_01."MANDT" = @P000 AND T_00."VBELN" =

T_01."VBELN" AND T_00."POSNR" = T_01."POSNR" INNER JOIN "VBAK" T_02 ON T_02."M

ANDT" = @P001 AND T_00."VBELN" = T_02."VBELN"

WHERE

T_00."MANDT" = @P002 AND T_01."MATNR" = @P003 AND T_01."WERKS" = @P004 AND (

T_00."ERDAT" BETWEEN @P005 AND @P006 OR T_00."ERDAT" BETWEEN @P007 AND @P008

OR T_00."ERDAT" BETWEEN @P009 AND @P00A OR T_00."ERDAT" BETWEEN @P00B AND

@P00C OR T_00."ERDAT" BETWEEN @P00D AND @P00E OR T_00."ERDAT" BETWEEN @P00F

AND @P010 OR T_00."ERDAT" BETWEEN @P011 AND @P012 OR T_00."ERDAT" BETWEEN

@P013 AND @P014 OR T_00."ERDAT" BETWEEN @P015 AND @P016 OR T_00."ERDAT"

BETWEEN @P017 AND @P018 OR T_00."ERDAT" BETWEEN @P019 AND @P01A OR

T_00."ERDAT" BETWEEN @P01B AND @P01C OR T_00."ERDAT" BETWEEN @P01D AND @P01E

OR T_00."ERDAT" BETWEEN @P01F AND @P020 OR T_00."ERDAT" BETWEEN @P021 AND

@P022 OR T_00."ERDAT" BETWEEN @P023 AND @P024 OR T_00."ERDAT" BETWEEN @P025

AND @P026 OR T_00."ERDAT" BETWEEN @P027 AND @P028 OR T_00."ERDAT" BETWEEN

@P029 AND @P02A OR T_00."ERDAT" BETWEEN @P02B AND @P02C OR T_00."ERDAT"

BETWEEN @P02D AND @P02E OR T_00."ERDAT" BETWEEN @P02F AND @P030 OR

T_00."ERDAT" BETWEEN @P031 AND @P032 OR T_00."ERDAT" BETWEEN @P033 AND @P034

OR T_00."ERDAT" BETWEEN @P035 AND @P036 OR T_00."ERDAT" BETWEEN @P037 AND

@P038 OR T_00."ERDAT" BETWEEN @P039 AND @P03A OR T_00."ERDAT" BETWEEN @P03B

AND @P03C OR T_00."ERDAT" BETWEEN @P03D AND @P03E OR T_00."ERDAT" BETWEEN

@P03F AND @P040 OR T_00."ERDAT" BETWEEN @P041 AND @P042 OR T_00."ERDAT"

BETWEEN @P043 AND @P044 OR T_00."ERDAT" BETWEEN @P045 AND @P046 OR

T_00."ERDAT" BETWEEN @P047 AND @P048 OR T_00."ERDAT" BETWEEN @P049 AND @P04A

OR T_00."ERDAT" BETWEEN @P04B AND @P04C OR T_00."ERDAT" BETWEEN @P04D AND

@P04E OR T_00."ERDAT" BETWEEN @P04F AND @P050 OR T_00."ERDAT" BETWEEN @P051

AND @P052 OR T_00."ERDAT" BETWEEN @P053 AND @P054 OR T_00."ERDAT" BETWEEN

@P055 AND @P056 OR T_00."ERDAT" BETWEEN @P057 AND @P058 OR T_00."ERDAT"

BETWEEN @P059 AND @P05A OR T_00."ERDAT" BETWEEN @P05B AND @P05C OR

T_00."ERDAT" BETWEEN @P05D AND @P05E OR T_00."ERDAT" BETWEEN @P05F AND @P060

OR T_00."ERDAT" BETWEEN @P061 AND @P062 OR T_00."ERDAT" BETWEEN @P063 AND

@P064 OR T_00."ERDAT" BETWEEN @P065 AND @P066 OR T_00."ERDAT" BETWEEN @P067

AND @P068 OR T_00."ERDAT" BETWEEN @P069 AND @P06A OR T_00."ERDAT" BETWEEN

@P06B AND @P06C OR T_00."ERDAT" BETWEEN @P06D AND @P06E OR T_00."ERDAT"

BETWEEN @P06F AND @P070 OR T_00."ERDAT" BETWEEN @P071 AND @P072 OR

T_00."ERDAT" BETWEEN @P073 AND @P074 OR T_00."ERDAT" BETWEEN @P075 AND @P076

OR T_00."ERDAT" BETWEEN @P077 AND @P078 OR T_00."ERDAT" BETWEEN @P079 AND

@P07A OR T_00."ERDAT" BETWEEN @P07B AND @P07C OR T_00."ERDAT" BETWEEN @P07D

AND @P07E OR T_00."ERDAT" BETWEEN @P07F AND @P080 OR T_00."ERDAT" BETWEEN

@P081 AND @P082 OR T_00."ERDAT" BETWEEN @P083 AND @P084 OR T_00."ERDAT"

BETWEEN @P085 AND @P086 OR T_00."ERDAT" BETWEEN @P087 AND @P088 OR

T_00."ERDAT" BETWEEN @P089 AND @P08A OR T_00."ERDAT" BETWEEN @P08B AND @P08C

OR T_00."ERDAT" BETWEEN @P08D AND @P08E OR T_00."ERDAT" BETWEEN @P08F AND

@P090 OR T_00."ERDAT" BETWEEN @P091 AND @P092 OR T_00."ERDAT" BETWEEN @P093

AND @P094 OR T_00."ERDAT" BETWEEN @P095 AND @P096 OR T_00."ERDAT" BETWEEN

@P097 AND @P098 OR T_00."ERDAT" BETWEEN @P099 AND @P09A OR T_00."ERDAT"

BETWEEN @P09B AND @P09C OR T_00."ERDAT" BETWEEN @P09D AND @P09E OR

T_00."ERDAT" BETWEEN @P09F AND @P0A0 OR T_00."ERDAT" BETWEEN @P0A1 AND @P0A2

OR T_00."ERDAT" BETWEEN @P0A3 AND @P0A4 OR T_00."ERDAT" BETWEEN @P0A5 AND

@P0A6 OR T_00."ERDAT" BETWEEN @P0A7 AND @P0A8 OR T_00."ERDAT" BETWEEN @P0A9

AND @P0AA OR T_00."ERDAT" BETWEEN @P0AB AND @P0AC OR T_00."ERDAT" BETWEEN

@P0AD AND @P0AE OR T_00."ERDAT" BETWEEN @P0AF AND @P0B0 OR T_00."ERDAT"

BETWEEN @P0B1 AND @P0B2 OR T_00."ERDAT" BETWEEN @P0B3 AND @P0B4 OR

T_00."ERDAT" BETWEEN @P0B5 AND @P0B6 OR T_00."ERDAT" BETWEEN @P0B7 AND @P0B8

OR T_00."ERDAT" BETWEEN @P0B9 AND @P0BA OR T_00."ERDAT" BETWEEN @P0BB AND

@P0BC OR T_00."ERDAT" BETWEEN @P0BD AND @P0BE OR T_00."ERDAT" BETWEEN @P0BF

AND @P0C0 OR T_00."ERDAT" BETWEEN @P0C1 AND @P0C2 OR T_00."ERDAT" BETWEEN

@P0C3 AND @P0C4 OR T_00."ERDAT" BETWEEN @P0C5 AND @P0C6 OR T_00."ERDAT"

BETWEEN @P0C7 AND @P0C8 OR T_00."ERDAT" BETWEEN @P0C9 AND @P0CA OR

T_00."ERDAT" BETWEEN @P0CB AND @P0CC OR T_00."ERDAT" BETWEEN @P0CD AND @P0CE

OR T_00."ERDAT" BETWEEN @P0CF AND @P0D0 OR T_00."ERDAT" BETWEEN @P0D1 AND

@P0D2 OR T_00."ERDAT" BETWEEN @P0D3 AND @P0D4 OR T_00."ERDAT" BETWEEN @P0D5

AND @P0D6 OR T_00."ERDAT" BETWEEN @P0D7 AND @P0D8 OR T_00."ERDAT" BETWEEN

@P0D9 AND @P0DA OR T_00."ERDAT" BETWEEN @P0DB AND @P0DC OR T_00."ERDAT"

BETWEEN @P0DD AND @P0DE OR T_00."ERDAT" BETWEEN @P0DF AND @P0E0 OR

T_00."ERDAT" BETWEEN @P0E1 AND @P0E2 OR T_00."ERDAT" BETWEEN @P0E3 AND @P0E4

OR T_00."ERDAT" BETWEEN @P0E5 AND @P0E6 OR T_00."ERDAT" BETWEEN @P0E7 AND

@P0E8 OR T_00."ERDAT" BETWEEN @P0E9 AND @P0EA OR T_00."ERDAT" BETWEEN @P0EB

AND @P0EC OR T_00."ERDAT" BETWEEN @P0ED AND @P0EE OR T_00."ERDAT" BETWEEN

Variable

A0(CH,3) = 100

A1(CH,3) = 100

A2(CH,3) = 100

A3(CH,18) = 000000000000051706

A4(CH,4) = 1020

A5(NU,8) = 20060701

A6(NU,8) = 20060731

A7(NU,8) = 20060801

A8(NU,8) = 20060831

A9(NU,8) = 20060901

A10(NU,8) = 20060931

A11(NU,8) = 20061001

A12(NU,8) = 20061031

A13(NU,8) = 20061101

A14(NU,8) = 20061131

A15(NU,8) = 20061201

A16(NU,8) = 20061231

A17(NU,8) = 20070101

A18(NU,8) = 20070131

A19(NU,8) = 20070201

A20(NU,8) = 20070231

A21(NU,8) = 20070301

A22(NU,8) = 20070331

A23(NU,8) = 20070401

A24(NU,8) = 20070431

A25(NU,8) = 20070501

A26(NU,8) = 20070531

A27(NU,8) = 20060701

A28(NU,8) = 20060731

A29(NU,8) = 20060801

A30(NU,8) = 20060831

A31(NU,8) = 20060901

A32(NU,8) = 20060931

A33(NU,8) = 20061001

[Former Member]

Well, I think the main problem is that the SELECT is not using any index. Use MATNR to select against VAPMA and get the document number there.

Also do the SUM outside of the SELECT.

Rob

[Former Member]

Hi,

Thank you for the tips.

Question: why do the sum outside of the select?

Thank You,

Stanley

[Former Member]

The database generally is less efficient at doing these aggregate functions. ABAP is faster.

Rob

[former_member194669]

Hi,

I find the key u are using not a key fields for select

VBRP~MATNR = I_ALV-MATNR

AND VBRP~WERKS = I_ALV-WERKS

AND VBRP~ERDAT IN I_MONTH_RANGE

AND VBRP~PSTYV = 'TAB'

AND VBAK~AUART = 'ZDS'.

in VBRP the primary key VBELN & POSNR,and could not find any secondary index on MATNR WERKS.......

aRs

[Former Member]

Hi,

It seems that you are doing this select query inside you loop. Please move this query from this loop and put it before your loop. And inside the loop, just read the appropriate data using the key whatever you want. Yes, you need to add some fields in your select query and create an internal table for that, but that will improve your performance drastically. And yes, dont forget to use the INDEX fields.

Regards,

Sandip

[Former Member]

Hi,

I don't think that I will be able to move the select outside the loop.

If you can give me a few tips, that would be great.

Here is how the report works:

We loop at the internal table holding the data (in this case materials and etc) based on the selection criteria entered.

So inside the loop, we get the sales for the month.

So it sort of looks like:

loop at data_table.

month_1 (these are subroutines, where we calculate the sales and invoices for that month, material and plant.)

.....

month12

endloop.

How could I move the select outside the loop?

Thanks,

John

[Former Member]

It would be better if you could post your code. Trying to help from a desription of what the program should be doing can be slow and painful (for everyone;)

Rob

[Former Member]

LOOP AT I_MARC_VALUES.

REFRESH: RETURN, ATP1, ATP2, OPEN_PO, INBOUND, LS_STYLEROW.

CLEAR: BAPI_ONHAND, INBOUND_QTY,

MS_1, MS_2, MS_CURRENT, MS_OPEN, MS_FINAL, ONHAND.

I_ALV-WERKS = I_MARC_VALUES-WERKS.

I_ALV-DISPO = I_MARC_VALUES-DISPO.

I_ALV-DISMM = I_MARC_VALUES-DISMM.

I_ALV-LIFNR = I_MARC_VALUES-ZZLIFNR.

I_ALV-MATNR = I_MARC_VALUES-MATNR.

I_ALV-MAKTX = I_MARC_VALUES-MAKTX.

----

---

• CALCULATING MONTHLY SALES

----

---

• MONTHLY SALES 1 (CURRENT - 11)

PERFORM PAST_MONTH USING T_DATE

'-11'

'MONTHLY_1'

CHANGING I_ALV-MONTHLY_1.

• MONTHLY SALES 2 (CURRENT - 10)

PERFORM PAST_MONTH USING T_DATE

'-10'

'MONTHLY_2'

CHANGING I_ALV-MONTHLY_2.

• MONTHLY SALES 3 (CURRENT - 9)

PERFORM PAST_MONTH USING T_DATE

'-9'

'MONTHLY_3'

CHANGING I_ALV-MONTHLY_3.

• MONTHLY SALES 4 (CURRENT - 😎

PERFORM PAST_MONTH USING T_DATE

'-8'

'MONTHLY_4'

CHANGING I_ALV-MONTHLY_4.

• MONTHLY SALES 5 (CURRENT - 7)

PERFORM PAST_MONTH USING T_DATE

'-7'

'MONTHLY_5'

CHANGING I_ALV-MONTHLY_5.

• MONTHLY SALES 6 (CURRENT - 6)

PERFORM PAST_MONTH USING T_DATE

'-6'

'MONTHLY_6'

CHANGING I_ALV-MONTHLY_6.

• MONTHLY SALES 7 (CURRENT - 5)

PERFORM PAST_MONTH USING T_DATE

'-5'

'MONTHLY_7'

CHANGING I_ALV-MONTHLY_7.

• MONTHLY SALES 8 (CURRENT - 4)

PERFORM PAST_MONTH USING T_DATE

'-4'

'MONTHLY_8'

CHANGING I_ALV-MONTHLY_8.

• MONTHLY SALES 9 (CURRENT - 3)

PERFORM PAST_MONTH USING T_DATE

'-3'

'MONTHLY_9'

CHANGING I_ALV-MONTHLY_9.

• MONTHLY SALES 10 (CURRENT - 2)

PERFORM PAST_MONTH USING T_DATE

'-2'

'MONTHLY_10'

CHANGING I_ALV-MONTHLY_10.

• MONTHLY SALES 11 (CURRENT - 1)

PERFORM PAST_MONTH USING T_DATE

'-1'

'MONTHLY_11'

CHANGING I_ALV-MONTHLY_11.

-

---

INSIDE THE SUBROUTINE

-

---

&----

---

*& Form PAST_MONTH

&----

---

• text

----

---

• -->CURR_DATE text

• -->PAST_MONTH text

• -->V_FIELDNAME TEXT

• <--PAST_MONTH_VALUE text

----

---

FORM PAST_MONTH USING CURR_DATE

PAST_MONTH

V_FIELDNAME

CHANGING PAST_MONTH_VALUE.

DATA: TEMP_DATE LIKE SY-DATUM,

NEW_DATE TYPE SPMON,

TEMP_DATE_END LIKE SY-DATUM.

CALL FUNCTION 'RE_ADD_MONTH_TO_DATE'

EXPORTING

MONTHS = PAST_MONTH

OLDDATE = CURR_DATE

IMPORTING

NEWDATE = TEMP_DATE.

NEW_DATE(4) = TEMP_DATE(4).

NEW_DATE4(2) = TEMP_DATE4(2).

TEMP_DATE_END = TEMP_DATE.

TEMP_DATE_END+6(2) = '31'.

• CLEAR DATE RANGE FROM PREVIOUS MONTH

CLEAR: I_MONTH_RANGE, CONSUMP.

• IF VENDOR = '300276', GET THE CONSUMPTION VALUES

• FOR THE SPECIFIC MONTH IF IT IS IN BETWEEN AUGUST 2006

• AND JULY 2007 (HARDCODED UNTIL FEATURE IS NO LONGER NEEDED)

• FILL DATE RANGE

I_MONTH_RANGE-SIGN = 'I'.

I_MONTH_RANGE-OPTION = 'BT'.

I_MONTH_RANGE-LOW = TEMP_DATE.

I_MONTH_RANGE-HIGH = TEMP_DATE_END.

APPEND I_MONTH_RANGE.

READ TABLE I_TVKWZ INTO I_TVKWZ_2 WITH KEY WERKS = I_ALV-WERKS.

IF I_ALV-LIFNR = '0000300276' AND TEMP_DATE IN I_CONSUMP.

FC_YEAR = TEMP_DATE(4).

FC_MONTH = TEMP_DATE+4(2).

SELECT * INTO TABLE I_MVER FROM MVER

WHERE GJAHR = FC_YEAR

AND WERKS = I_ALV-WERKS

AND MATNR = I_ALV-MATNR.

LOOP AT I_MVER.

CASE FC_MONTH.

WHEN '01'.

CONSUMP = I_MVER-GSV01.

WHEN '02'.

CONSUMP = I_MVER-GSV02.

WHEN '03'.

CONSUMP = I_MVER-GSV03.

WHEN '04'.

CONSUMP = I_MVER-GSV04.

WHEN '05'.

CONSUMP = I_MVER-GSV05.

WHEN '06'.

CONSUMP = I_MVER-GSV06.

WHEN '07'.

CONSUMP = I_MVER-GSV07.

WHEN '08'.

CONSUMP = I_MVER-GSV08.

WHEN '09'.

CONSUMP = I_MVER-GSV09.

WHEN '10'.

CONSUMP = I_MVER-GSV10.

WHEN '11'.

CONSUMP = I_MVER-GSV11.

WHEN '12'.

CONSUMP = I_MVER-GSV12.

ENDCASE.

PAST_MONTH_VALUE = CONSUMP.

ENDLOOP.

PERFORM CONVERT_MAT_UNIT USING I_ALV-MATNR

'EA'

'CSE'

PAST_MONTH_VALUE

CHANGING PAST_MONTH_VALUE.

ELSE.

SELECT SUM( UMMENGE ) FROM S501

INTO PAST_MONTH_VALUE

WHERE SSOUR = SPACE

AND VRSIO = '000'

AND SPMON = NEW_DATE

AND VKORG = I_TVKWZ_2-VKORG

AND MATNR = I_ALV-MATNR

AND sptag = c_sptag_ini

AND spwoc = c_spwoc_ini

AND spbup = c_spbup_ini

AND VTWEG IN I_VTWEG.

• SUBTRACTING INVOICES (VBRP)

SELECT VBRP~FKIMG

FROM VBRP INNER JOIN VAPMA

ON VBRP_{VBELN = VAPMA}VBELN

AND VBRP_{POSNR = VAPMA}POSNR

INNER JOIN VBAK

ON VBRP_{VBELN = VBAK}VBELN

INTO CORRESPONDING FIELDS OF TABLE I_VBRP

WHERE VAPMA~MATNR = I_ALV-MATNR

AND VAPMA~WERKS = I_ALV-WERKS

AND VBRP~ERDAT IN I_MONTH_RANGE

AND VBRP~PSTYV = 'TAB'

AND VBAK~AUART = 'ZDS'.

BREAK SSANDLER.

PAST_MONTH_VALUE = PAST_MONTH_VALUE - GALLO_SUB.

PERFORM CONVERT_MAT_UNIT USING I_ALV-MATNR

'EA'

'CSE'

PAST_MONTH_VALUE

CHANGING PAST_MONTH_VALUE.

ENDIF.

CONCATENATE 'M' SPACE NEW_DATE+4(2) '/' NEW_DATE(4)

INTO FLD_HEADER.

W_FIELDCAT_LVC-scrtext_s = FLD_HEADER.

W_FIELDCAT_LVC-scrtext_m = FLD_HEADER.

W_FIELDCAT_LVC-scrtext_l = FLD_HEADER.

MODIFY I_FIELDCAT_LVC FROM W_FIELDCAT_LVC

TRANSPORTING SCRTEXT_S SCRTEXT_M SCRTEXT_L

WHERE FIELDNAME = V_FIELDNAME.

ENDFORM. " PAST_MONTH

[Former Member]

I don't see anything here that would cause an increase in execution time everytime the form is called; however, I don't think this is you main problem. You want to increase the performance, so you should go to VAPMA to get the document numbers first.

I notice you removed the SUM. Do you not need it or are you going to look at it later?

Rob

[Former Member]

Hi,

Thanks for the tips.

Am i not going to VAPMA currently or do i need to do something different?

I removed the sum because i'm going to use ABAP aggregate functions instead of DB aggregate functions.

Here is how it looked with sum:

SELECT SUM( FKIMG )

FROM VBRP INNER JOIN VBAP

ON VBRP_{VBELN = VBAP}VBELN

AND VBRP_{POSNR = VBAP}POSNR

INNER JOIN VBAK

ON VBRP_{VBELN = VBAK}VBELN

INTO GALLO_SUB

WHERE VBAP~MATNR = I_ALV-MATNR

AND VBAP~WERKS = I_ALV-WERKS

AND VBRP~ERDAT IN I_MONTH_RANGE

AND VBRP~PSTYV = 'TAB'

AND VBAK~AUART = 'ZDS'.

[Former Member]

I'll have another look at it in a few minutes. I guess it looks OK.

Give it a try. It should be better.

Rob

Message was edited by:

Rob Burbank