topic Re: Urgent - Performance Optimization help in Application Development and Automation Discussions

Urgent - Performance Optimization help

Former Member — Wed, 27 Jun 2007 19:50:05 GMT

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

Re: Urgent - Performance Optimization help

Former Member — Wed, 27 Jun 2007 19:56:53 GMT

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

Re: Urgent - Performance Optimization help

Former Member — Wed, 27 Jun 2007 20:09:46 GMT

Hi,

Thank you for the tips.

Question: why do the sum outside of the select?

Thank You,

Stanley

Re: Urgent - Performance Optimization help

former_member194669 — Wed, 27 Jun 2007 20:10:00 GMT

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

Re: Urgent - Performance Optimization help

Former Member — Wed, 27 Jun 2007 20:16:43 GMT

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

Re: Urgent - Performance Optimization help

Former Member — Wed, 27 Jun 2007 20:22:24 GMT

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

Rob

Re: Urgent - Performance Optimization help

Former Member — Wed, 27 Jun 2007 20:24:32 GMT

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

Re: Urgent - Performance Optimization help

Former Member — Wed, 27 Jun 2007 20:27:47 GMT

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

Re: Urgent - Performance Optimization help

Former Member — Wed, 27 Jun 2007 20:31:45 GMT

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)