Report time out

[Former Member]

I have a z report  "PR status report" .

When i run this to DEV & Quality its working fine, but when i run it to PRD then its time out & even after stop the report it is running in background & then server get stuck & i have to restart the services.

Please tell me solution???

[Former Member]

I have a z report  "PR status report" .

When i run this to DEV & Quality its working fine, but when i run it to PRD then its time out & even after stop the report it is running in background & then server get stuck & i have to restart the services.

Please tell me solution???

[laurent_fournier2]

Hello,

Probably , there are too much data in PRD that causes the timeout. You must probably redesign or improve the selection  of the report or execute the report with as many selection-options possible. Can you paste a bit of the code in order to help you ?

[Former Member]

here is the code.

FORM DATA_RETRIEVAL.

           SELECT distinct WERKS BANFN BNFPO BADAT AFNAM BEDNR FRGZU MATNR TXZ01 MENGE MEINS LFDAT EBELN EBELP BEDAT BSMNG FRGKZ PREIS BADAT ERDAT
           INTO (ITAB-WERKS,ITAB-BANFN,ITAB-BNFPO,ITAB-BADAT,ITAB-AFNAM,ITAB-BEDNR,ITAB-FRGZU,
                 ITAB-MATNR,ITAB-TXZ01,ITAB-MENGE,ITAB-MEINS,ITAB-LFDAT,ITAB-EBELN,ITAB-EBELP,ITAB-BEDAT,ITAB-BSMNG,ITAB-FRGKZ,ITAB-PREIS,ITAB-BADAT,ITAB-ERDAT)
           FROM EBAN
           WHERE BANFN IN M_BANFN AND
                 MATNR IN M_MATNR AND
                 WERKS IN M_WERKS AND
                 BADAT IN M_BADAT AND
                 AFNAM IN M_AFNAM.
*                FRGDT IN M_FRGDT.
*
*        SELECT SINGLE EKKO~EBELN INTO (ITAB-EBELN) FROM EKKO
*        JOIN EBAN ON EKKO~EBELN = EBAN~EBELN.

           SELECT SINGLE LFA1~NAME1 INTO (ITAB-NAME1) FROM LFA1
           JOIN EKKO ON LFA1~LIFNR = EKKO~LIFNR AND
                        EKKO~EBELN = ITAB-EBELN.

           SELECT SINGLE UDATE INTO (ITAB-UDATE) FROM CDHDR
           WHERE OBJECTID    eq   ITAB-EBELN AND
                 TCODE       EQ   'ME29N'  AND
                 CHANGE_IND  EQ   'U'.

           SELECT SINGLE UDATE INTO (ITAB-FRGDT) FROM CDHDR
          WHERE OBJECTID    eq   ITAB-BANFN AND
                ( TCODE     =   'ME54N'    or
                  tcode     =   'ME55' OR TCODE EQ   'ME52N' )  and
                CHANGE_IND  EQ   'U'.

           SELECT sum( LABST ) sum( INSME ) INTO (ITAB-M_USTK,ITAB-M_QSTK)
           FROM MARD
           WHERE WERKS IN M_WERKS and
                 MATNR = ITAB-MATNR.

          SELECT SINGLE SUM( MENGE ) INTO (ITAB-GR_MENGE)
           FROM MSEG
           WHERE EBELN eq ITAB-EBELN AND
                 EBELP EQ ITAB-EBELP AND
                 BWART eq '101'
           GROUP BY EBELN EBELP.

           select SINGLE sum( menge ) into  (ITAB-M_gr101)
           from ekbe
           WHERE EBELN eq ITAB-EBELN AND
                 EBELP EQ ITAB-EBELP AND
           shkzg = 'S' and
           BEWTP ='E'.

           select SINGLE sum( menge ) into  (ITAB-M_gr102)
           from ekbe
           WHERE EBELN eq ITAB-EBELN AND
                 EBELP EQ ITAB-EBELP AND
                 shkzg = 'H' and
                 BEWTP ='E'.

           ITAB-GR_MENGE =  ITAB-M_gr101 - ITAB-M_gr102 .

           if ITAB-GR_MENGE  <> 0.
           SELECT SINGLE BUDAT INTO (ITAB-GR_BUDAT) FROM EKBE
           WHERE EBELN eq ITAB-EBELN AND
                 EBELP EQ ITAB-EBELP AND
           shkzg = 'S' and
           BEWTP ='E'.
           endif.
           APPEND ITAB.
           CLEAR ITAB.

           ENDSELECT.


ENDFORM.

[Former Member]

Move all select singles out of SELECT-ENDSELECT.

Also, you have 5 aggregate functions (SUM) within select-endselect. - really bad idea.

Your query is going to take too much time and hog all resources in production. Dont run it in production unless this issue is fixed.

Also, I hope one of the selection criteria is mandatory.

[Former Member]

if i move all select singles out then my report showing wrong result.

[Former Member]

By out I mean -

1. Create an internal table for each select single.

2. Fetch all the data according to where clause in those internal tables BEFORE your ENDSELECT query.

3. Now, you have all the data required from database in internal tables

4. Replace select single with "read table INTERNAL_TABLE with key" syntax.

[gouravkumar64]

example:-

select single budat into table it_itab from ekbe

where .......

read table it_itab into wa_itab with key .............

u can try.

[gouravkumar64]

Also Join may be taking much time.For all entries Is better.

[ThomasZloch]

Please read about runtime analysis tools, use them, search for previous discussions of similar problems, post again with specific details if you did not solve it yourself using this information.

Discussion locked.

Thomas