UPDATED: Performance Optimization for Code (URGENT, VERY IMPORTANT)

[Former Member]

Hi,

My current program takes a lot of time to pull up data from VBAP and VBRP.

Though it will run nightly, how can I increase Performance Optimization in the following Code (4 Random Parts):

1.

• CALCULATE THE OPEN ORDERS FOR PROCESSING MATERIAL

• Get the pending order data

SELECT SUM( a~klmeng ) INTO V_MTD_2

FROM ( ( vbak AS b INNER JOIN vbuk AS c

ON b_{vbeln = c}vbeln )

INNER JOIN vbap AS a

ON a_{vbeln = b}vbeln )

WHERE b~vkorg = I_TVKO-VKORG

AND b~vtweg <> '02'

AND b~vtweg <> '05'

AND c~gbstk = 'A'

AND a~matnr = I_MARC_VALUES-MATNR

AND a~abgru = space.

-

---

2.

• Get DAILY OPEN QTY

SELECT SUM( a~klmeng ) INTO V_DL_1

FROM ( ( vbak AS b INNER JOIN vbuk AS c

ON b_{vbeln = c}vbeln )

INNER JOIN vbap AS a

ON a_{vbeln = b}vbeln )

WHERE b~vkorg = I_TVKO-VKORG

AND b~vtweg <> '02'

AND b~vtweg <> '05'

AND c~gbstk = 'A'

AND a~matnr = I_MARC_VALUES-MATNR

AND a~abgru = space

AND a~erdat = v_date.

-

---

3.

• DAILY SALES

break-point.

SELECT SUM( FKLMG ) INTO DAILY_SLS

FROM VBRP

WHERE MATNR = I_ZDAILY_MVMT-MATNR AND

WERKS = I_ZDAILY_MVMT-WERKS AND

ERDAT = V_DATE AND

VTWEG_AUFT <> '02' AND

VTWEG_AUFT <> '05'.

-

---

4.

• CALCULATE DAILY BACKORDER QUANTITY

SELECT SUM( KLMENG ) INTO I_ZDAILY_MVMT-DLY_BO

FROM VBAP

WHERE MATNR = I_ZDAILY_MVMT-MATNR AND

WERKS = I_ZDAILY_MVMT-WERKS AND

ERDAT = V_DATE.

• Points will be rewarded and all responses will be highly appreciated.

Thanks,

John

[Former Member]

Hi,

I think the first and second query is fine.

You have to look at 3. DAILY SALES and 4.CALCULATE DAILY BACKORDER QUANTITY as VBAP & VBRP doesnot uses a key field.

[Former Member]

The problem is that records are being fetched/analyzed sequentially.

What should I be looking to change to increase performance optimization?

- John

[Former Member]

Hi,

If the third and fourth query is in the loop inside this itab I_ZDAILY_MVMT and if this itab has vbeln and posnr, use select single with the key field and sum it manually. Something like this

LOOP AT I_ZDAILY_MVMT .

• DAILY SALES

SELECT SINGLE FKLMG INTO DAILY_SLS

FROM VBRP

WHERE

VBELN = I_ZDAILY_MVMT-VBELN AND

POSNR = I_ZDAILY_MVMT-POSNR AND

MATNR = I_ZDAILY_MVMT-MATNR AND

WERKS = I_ZDAILY_MVMT-WERKS AND

ERDAT = V_DATE AND

VTWEG_AUFT <> '02' AND

VTWEG_AUFT <> '05'.

If SY-subrc = 0.

Manually declare a separate variable of the same type here and SUM FKLMG.

Endif.

Endloop.

Hope u have VBELN and POSNR field in the itab I_ZDAILY_MVMT

Similarly do for the other query also. Hope it will increase the performance.

Regds,

Senthil

Message was edited by:

senthil kumar

[Former Member]

What is the structure of I_ZDAILY? Doesn't it have a key field (VBELN) for VBRK & VBRP?

Are queries 3 & 4 inside loop? If so, you can avoid it using FOR ALL ENTRIES IN I_ZDAILY.

[Former Member]

Have you used ST05 to identify bottlenecks?

Rob

[Former Member]

No I haven't. However, I will try.

In ST05, what should i look for exactly?

[Former Member]

Two things - look for statments especially repeated statements with high execution times. They will be hi-lited.

Also use the explain function to see how expensive the statements are.

Rob

[Former Member]

ST05 will display the duration for each database access.

You can identify which SELECT statements are eating up the time.

If it is not possible to change the way SELECT statements are coded, you can try to create index on VBAP & VBRP tables for fields MATNR & WERKS fields.

[Former Member]

Thanks for the help everyone.

In ST05, i found the 2 selects (from codes 3 & 4.) Code 3 takes 38 seconds to select sum from VBRP and code 4 takes 52 seconds to select sum from VBAP.

I will try the index suggestion.

-john

[Former Member]

Hey John,

Any success on this one?

[Former Member]

Hi Darshil,

Well, before trying the Index suggestion, I am looking for an alternative. Since this isn't in a loop and the I_ZDAILY_MVMT tables doesn't have a VBELN field, I am looking for additional routes.

The fact is that I_ZDAILY_MVMT is an Internal Tables, referencing to DB table ZDAILY_MVMT, which i created. Since no data is stored there yet, I am looking for an additional route (maybe on adding the VBELN field.)

If anyone can suggest anything else, it would be greatly appreciated.

- John

[Former Member]

This will actually be pretty easy. You should use table VAPMA to select sales order items based on material. It's indexed on material and will give you the sales document and item number (quickly).

Rob