Performance issue in Report (getting time out error)

[Former Member]

Hi experts,

I am doing Performance for a Report (getting time out error)

Please see the code below and .

while looping internal table IVBAP after 25 minutes its showing time out error at this poit ->

SELECT MAX( ERDAT ) .

please send alternate code for this .

Advance thanks

from

Nagendra

• Get Sales Order Details

CLEAR IVBAP.

REFRESH IVBAP.

SELECT VBELN POSNR MATNR NETWR KWMENG WERKS FROM VBAP

INTO CORRESPONDING FIELDS OF TABLE IVBAP

FOR ALL ENTRIES IN IVBAK

WHERE VBELN = IVBAK-VBELN

AND MATNR IN Z_MATNR

AND WERKS IN Z_WERKS

AND ABGRU = ' '.

• Check for Obsolete Materials - Get Product Hierarhy/Mat'l Description

SORT IVBAP BY MATNR WERKS.

CLEAR: WK_MATNR, WK_WERKS, WK_PRDHA, WK_MAKTX,

WK_BLOCK, WK_MMSTA, WK_MSTAE.

LOOP AT IVBAP.

CLEAR WK_INVDATE. "I6677.sn

SELECT MAX( ERDAT ) FROM VBRP INTO WK_INVDATE WHERE

AUBEL EQ IVBAP-VBELN AND

AUPOS EQ IVBAP-POSNR.

IF SY-SUBRC = 0.

MOVE WK_INVDATE TO IVBAP-INVDT.

MODIFY IVBAP.

ENDIF. "I6677.e n

SELECT SINGLE * FROM MBEW WHERE "I6759.sn

MATNR EQ IVBAP-MATNR AND

BWKEY EQ IVBAP-WERKS AND

BWTAR EQ SPACE.

IF SY-SUBRC = 0.

MOVE MBEW-STPRS TO IVBAP-STPRS.

IVBAP-TOT = MBEW-STPRS * IVBAP-KWMENG.

MODIFY IVBAP.

ENDIF. "I6759.en

IF IVBAP-MATNR NE WK_MATNR OR IVBAP-WERKS NE WK_WERKS.

CLEAR: WK_BLOCK, WK_MMSTA, WK_MSTAE, WK_PRDHA, WK_MAKTX.

MOVE IVBAP-MATNR TO WK_MATNR.

MOVE IVBAP-WERKS TO WK_WERKS.

SELECT SINGLE MMSTA FROM MARC INTO MARC-MMSTA

WHERE MATNR = WK_MATNR

AND WERKS = WK_WERKS.

IF NOT MARC-MMSTA IS INITIAL.

MOVE '*' TO WK_MMSTA.

ENDIF.

SELECT SINGLE LVORM PRDHA MSTAE MSTAV FROM MARA

INTO (MARA-LVORM, MARA-PRDHA, MARA-MSTAE, MARA-MSTAV)

WHERE MATNR = WK_MATNR.

IF ( NOT MARA-MSTAE IS INITIAL ) OR

( NOT MARA-MSTAV IS INITIAL ) OR

( NOT MARA-LVORM IS INITIAL ).

MOVE '*' TO WK_MSTAE.

ENDIF.

MOVE MARA-PRDHA TO WK_PRDHA.

SELECT SINGLE MAKTX FROM MAKT INTO WK_MAKTX

WHERE MATNR = WK_MATNR

AND SPRAS = SY-LANGU.

ENDIF.

IF Z_BLOCK EQ 'B'.

IF WK_MMSTA EQ ' ' AND WK_MSTAE EQ ' '.

DELETE IVBAP.

CONTINUE.

ENDIF.

ELSEIF Z_BLOCK EQ 'U'.

IF WK_MMSTA EQ '' OR WK_MSTAE EQ ''.

DELETE IVBAP.

CONTINUE.

ENDIF.

ELSE.

IF WK_MMSTA EQ '' OR WK_MSTAE EQ ''.

MOVE '*' TO WK_BLOCK.

ENDIF.

ENDIF.

IF WK_PRDHA IN Z_PRDHA. "I4792

MOVE WK_BLOCK TO IVBAP-BLOCK.

MOVE WK_PRDHA TO IVBAP-PRDHA.

MOVE WK_MAKTX TO IVBAP-MAKTX.

MODIFY IVBAP.

ELSE. "I4792

DELETE IVBAP. "I4792

ENDIF. "I4792

IF NOT Z_ALNUM[] IS INITIAL. "I9076

SELECT SINGLE * FROM MAEX "I9076

WHERE MATNR = IVBAP-MATNR "I9076

AND ALNUM IN Z_ALNUM. "I9076

IF SY-SUBRC <> 0. "I9076

DELETE IVBAP. "I9076

ENDIF. "I9076

ENDIF. "I9076

ENDLOOP.

[Former Member]

Hi Nagendra..

As the fore gournd time will defaultly set 25mins by BASIS.. If there are more records like 3 lakhs to populate then it shows timed out message.

And in loop you are using Select Statement which causes you Performance issue too...

First select the all data to be populate in to Internal Tables and then loop it....

So, the solution is run you program in Back Ground.

Regards,

sg.

Edited by: Suneel Kumar Gopisetty on Sep 24, 2008 11:32 AM

[Former Member]

Hi Nagendra!

Get Sales Order Details

CLEAR IVBAP.

REFRESH IVBAP.

check ivbak is not initial

SELECT VBELN POSNR MATNR NETWR KWMENG WERKS FROM VBAP

INTO CORRESPONDING FIELDS OF TABLE IVBAP

FOR ALL ENTRIES IN IVBAK

WHERE VBELN = IVBAK-VBELN

AND MATNR IN Z_MATNR

AND WERKS IN Z_WERKS

AND ABGRU = ' '.

Check for Obsolete Materials - Get Product Hierarhy/Mat'l Description

SORT IVBAP BY MATNR WERKS.

CLEAR: WK_MATNR, WK_WERKS, WK_PRDHA, WK_MAKTX,

WK_BLOCK, WK_MMSTA, WK_MSTAE.

avoid select widin loop. instead do selection outside loop.u can use read statement......and then loop if required.

LOOP AT IVBAP.

CLEAR WK_INVDATE. "I6677.sn

SELECT MAX( ERDAT ) FROM VBRP INTO WK_INVDATE WHERE

AUBEL EQ IVBAP-VBELN AND

AUPOS EQ IVBAP-POSNR.

IF SY-SUBRC = 0.

MOVE WK_INVDATE TO IVBAP-INVDT.

MODIFY IVBAP.

ENDIF. "I6677.e n

SELECT SINGLE * FROM MBEW WHERE "I6759.sn

MATNR EQ IVBAP-MATNR AND

BWKEY EQ IVBAP-WERKS AND

BWTAR EQ SPACE.

IF SY-SUBRC = 0.

MOVE MBEW-STPRS TO IVBAP-STPRS.

IVBAP-TOT = MBEW-STPRS * IVBAP-KWMENG.

MODIFY IVBAP.

ENDIF. "I6759.en

IF IVBAP-MATNR NE WK_MATNR OR IVBAP-WERKS NE WK_WERKS.

CLEAR: WK_BLOCK, WK_MMSTA, WK_MSTAE, WK_PRDHA, WK_MAKTX.

MOVE IVBAP-MATNR TO WK_MATNR.

MOVE IVBAP-WERKS TO WK_WERKS.

SELECT SINGLE MMSTA FROM MARC INTO MARC-MMSTA

WHERE MATNR = WK_MATNR

AND WERKS = WK_WERKS.

IF NOT MARC-MMSTA IS INITIAL.

MOVE '*' TO WK_MMSTA.

ENDIF.

SELECT SINGLE LVORM PRDHA MSTAE MSTAV FROM MARA

INTO (MARA-LVORM, MARA-PRDHA, MARA-MSTAE, MARA-MSTAV)

WHERE MATNR = WK_MATNR.

IF ( NOT MARA-MSTAE IS INITIAL ) OR

( NOT MARA-MSTAV IS INITIAL ) OR

( NOT MARA-LVORM IS INITIAL ).

MOVE '*' TO WK_MSTAE.

ENDIF.

MOVE MARA-PRDHA TO WK_PRDHA.

SELECT SINGLE MAKTX FROM MAKT INTO WK_MAKTX

WHERE MATNR = WK_MATNR

AND SPRAS = SY-LANGU.

ENDIF.

IF Z_BLOCK EQ 'B'.

IF WK_MMSTA EQ ' ' AND WK_MSTAE EQ ' '.

DELETE IVBAP.

CONTINUE.

ENDIF.

ELSEIF Z_BLOCK EQ 'U'.

IF WK_MMSTA EQ '' OR WK_MSTAE EQ ''.

DELETE IVBAP.

CONTINUE.

ENDIF.

ELSE.

IF WK_MMSTA EQ '' OR WK_MSTAE EQ ''.

MOVE '*' TO WK_BLOCK.

ENDIF.

ENDIF.

IF WK_PRDHA IN Z_PRDHA. "I4792

MOVE WK_BLOCK TO IVBAP-BLOCK.

MOVE WK_PRDHA TO IVBAP-PRDHA.

MOVE WK_MAKTX TO IVBAP-MAKTX.

MODIFY IVBAP.

ELSE. "I4792

DELETE IVBAP. "I4792

ENDIF. "I4792

IF NOT Z_ALNUM[] IS INITIAL. "I9076

SELECT SINGLE * FROM MAEX "I9076

WHERE MATNR = IVBAP-MATNR "I9076

AND ALNUM IN Z_ALNUM. "I9076

IF SY-SUBRC 0. "I9076

DELETE IVBAP. "I9076

ENDIF. "I9076

ENDIF. "I9076

endloop.

U have used many select queries widin loop-endloop which is a big hindrance as far as performance is concerned.Avoid such practice.

Thanks

Deepika

[kiran_k8]

Nag,

Check if you can avoid writing select statements inside the loop.

K.Kiran.

[Former Member]

Hi,

Try this.

Instead of using select stmt inside the loop, fetch all the data from VBRP for all entries in VBAP. Then make use of read stmt inside the loop. This may decrease the time.

One more suggestion instead of using corresponding fields of, keep the internal table fields with the same name and same order as u fetch from the table. This will also reduce ur time.

Also check whether the table is initial or not before using for all entries stmt.

Then u can write ur query as

IF IVBAK[] IS NOT INITIAL.

SELECT VBELN POSNR MATNR NETWR KWMENG WERKS FROM VBAP

INTO TABLE IVBAP

FOR ALL ENTRIES IN IVBAK

WHERE VBELN = IVBAK-VBELN

AND MATNR IN Z_MATNR

AND WERKS IN Z_WERKS

AND ABGRU = ' '.

ENDIF.

Sharin.