topic Re: UPDATED: Performance Optimization for Code (URGENT, VERY IMPORTANT) in Application Development and Automation Discussions

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

Former Member — Wed, 23 May 2007 20:45:01 GMT

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):

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.

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.

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'.

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

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

Former Member — Wed, 23 May 2007 20:50:09 GMT

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.

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

Former Member — Wed, 23 May 2007 20:57:25 GMT

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.

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

Former Member — Wed, 23 May 2007 21:11:26 GMT

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

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

- John

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

Former Member — Wed, 23 May 2007 21:25:07 GMT

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

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

Former Member — Wed, 23 May 2007 21:47:51 GMT

Have you used ST05 to identify bottlenecks?

Rob

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

Former Member — Wed, 23 May 2007 21:48:58 GMT

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

In ST05, what should i look for exactly?

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

Former Member — Wed, 23 May 2007 21:52:50 GMT

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

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

Former Member — Wed, 23 May 2007 21:57:07 GMT

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.

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

Former Member — Wed, 23 May 2007 22:16:02 GMT

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

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

Former Member — Wed, 23 May 2007 23:42:02 GMT

Hey John,

Any success on this one?

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

Former Member — Thu, 24 May 2007 12:24:49 GMT

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

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

Former Member — Thu, 24 May 2007 13:01:48 GMT

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