Skip to Content
author's profile photo Former Member
Former Member

Performance issue in Report (getting time out error)

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

4 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Sep 24, 2008 at 03:31 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 24, 2008 at 03:33 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Sep 24, 2008 at 03:34 AM

    Nag,

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

    K.Kiran.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Sep 24, 2008 at 03:38 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.