21 Replies Latest reply: Oct 11, 2010 7:04 AM by John sat RSS

Report is running long time

John sat
Currently Being Moderated

Dear all ,

 

We have zreport in PROD and report worked fine till last month.

 

Zreport we are using every month for service tax .

 

The report will get execute  with in 10 to 15 minutes .

 

We (Abaper) have checked for this month zreport is running long time , its not giving any error .

 

 

Kindly suggest

 

Regards

  • Re: Report is running long time
    Markus Doehr
    Currently Being Moderated

    - check your selection criterias

    - check your database statistics

    - run the report through transaction SE30 and see where the time is being consumed

     

     

     

    Markus

    • Re: Report is running long time
      John sat
      Currently Being Moderated

      Dear Marcus ,

       

      I have  executed only one day ,still there is no output its keep on running .

      - I have checked our database statistics from DB13 , its successfully completed .

      - i have run the report through transaction SE30 and enen though its not completed .

       

      Kindly suggest .

  • Re: Report is running long time
    Nick Loy
    Currently Being Moderated
    We (Abaper) have checked for this month zreport is running long time

     

     

    Do the same in debugging mode, then you can find out where it is taking time.

     

     

     

    Regards,

    Nick Loy

  • Re: Report is running long time
    Eric Brunelle
    Currently Being Moderated

    run the job

     

    while it runs, check inSM50

    double-click on background process running your job.

     

    check SQL request being run.

     

    adapt index or DB stats on that table.

    • Re: Report is running long time
      John sat
      Currently Being Moderated

      Dear Eric,

       

      Now job is running and i have checked SM50 and I have double clicked the  dialog process running our job .

       

      No Ty. PID      Status  Reasn Start Err Sem CPU      Time   Cl. User

       

      1  DIA 1331222  Running       Yes           147:44   102    320 S2K_BASIS

       

       

       

      Report / Spool action

       

      ZFI_SERVICE_TAX

       

       

       

       

      Main Program

       

      ZFI_SERVICE_TAX

       

       

       

      Action                    Table

       

      Sequential Read           BSAD

       

       

       

      Waiting f.                                                                   since

       

       

       

       

       

      Last SQL statement

       

      SELECT

        "KUNNR" , "BELNR" , "BUDAT" , "DMBTR" , "GJAHR" , "XBLNR" , "HKONT" , "UMSKZ" , "SHKZG" , "AUGGJ" ,

        "REBZJ" , "AUGBL" , "BUZEI"

      FROM

        "BSAD"

      WHERE

        "MANDT" = :A0 AND "BUKRS" = :A1 AND "AUGGJ" = :A2 AND "AUGBL" = :A3 AND "PSWSL" = :A4 AND "SHKZG" = :A5

        AND ( "UMSKZ" = :A6 OR "UMSKZ" = :A7 ) OR "MANDT" = :A8 AND "BUKRS" = :A9 AND "AUGGJ" = :A10 AND

        "AUGBL" = :A11 AND "PSWSL" = :A12 AND "SHKZG" = :A13 AND ( "UMSKZ" = :A14 OR "UMSKZ" = :A15 ) OR

        "MANDT" = :A16 AND "BUKRS" = :A17 AND "AUGGJ" = :A18 AND "AUGBL" = :A19 AND "PSWSL" = :A20 AND "SHKZG" =

        :A21 AND ( "UMSKZ" = :A22 OR "UMSKZ" = :A23 ) OR "MANDT" = :A24 AND "BUKRS" = :A25 AND "AUGGJ" = :A26

        AND "AUGBL" = :A27 AND "PSWSL" = :A28 AND "SHKZG" = :A29 AND ( "UMSKZ" = :A30 OR "UMSKZ" = :A31 ) OR

        "MANDT" = :A32 AND "BUKRS" = :A33 AND "AUGGJ" = :A34 AND "AUGBL" = :A35 AND "PSWSL" = :A36 AND "SHKZG" =

        :A37 AND ( "UMSKZ" = :A38 OR "UMSKZ" = :A39 )#

       

       

       

      Database                         Number              Time (usec)         Recs.

       

      Direct Read                        19              21,509                   6

      Sequential Read                    47            101906536                632

      Insert                              0                   0                   0

      Update                              0                   0                   0

      Delete                              0                   0                   0

      Sources                       960,273  (Bytes)

      RSQL                          132,880  (Bytes)

      Commit                                                  0

      DB Procedure Calls                  0                   0

      Roles / Memory                   Number              Time (usec)

       

      Roll In                             1               2,159

      Roll Out                            0                   0

      Roll                          253,952  (Bytes)

      Page                           73,728  (Bytes)

      Memory (Total)                             12,581,164  (Bytes)

      Memory(Total Private)                               0  (Bytes)

      Memory (used)                               4,189,856  (Bytes)

      Memory (Max. Transaction)                  25,140,032  (Bytes)

      Memory (Max. Dialog Step)                  12,569,568  (Bytes)

       

       

       

      Development Environ.             Number              Time (usec)

       

      Generate                                                0

      Load Report                                        86,508

      Load Screen                   960,273  (Bytes)          0

      Load CUA                                               50

      Read Dictionary                                         0

      RFC                                 0  Calls     0.000000000000000E+00

      Queue API                           0  (Bytes)          0

      Ad Opcodes                          0                   0

       

      Kindly suggest

  • Re: Report is running long time
    Nick Loy
    Currently Being Moderated
    SELECT
    "KUNNR" , "BELNR" , "BUDAT" , "DMBTR" , "GJAHR" , "XBLNR" , "HKONT" , "UMSKZ" , "SHKZG" , "AUGGJ" ,
    "REBZJ" , "AUGBL" , "BUZEI"
    FROM
    "BSAD"

     

    Exxxxxxxxxxxxxxxxxxxpensive select statement

     

     

    Check with your business requirement and change the logic of ABAP program by taking help of ABAP.

    And take a look at the size of BSAD table and Indexes you are using on that table, If required rebuild old ones and create new indices with valid fields.

     

     

     

     

    Regards,

    Nick Loy

    • Re: Report is running long time
      John sat
      Currently Being Moderated

      Hi,

        Can note 1349849 be applied. When checking in sm50 the cluster table rfglb is been read. So is there is a solution if the above note is applied. But nowhere in the program there is direct access to the table.

       

       

      Thanks in advance,

       

      Sathiesh

       

      Edited by: satheesh0812 on Oct 8, 2010 10:14 AM

      • Re: Report is running long time
        Sunil Bujade
        Currently Being Moderated

        Note 1349849  is only valid for 500 to 604 and for tables BSEG/RFBLG and you are having issue with BSAD.

        can you please check when last stats executed for table BSAD via db20.

        Also you can do analysis using SE30 for your report why it is taking long time.

        • Re: Report is running long time
          John sat
          Currently Being Moderated

          Dear Sunil,

           

          please check below for  last stats executed for table BSAD via db20

           

          Table                 BSAD

           

                Statistics are current (|Changes| < 50 %)

          New Method           E

          New Sample Size      P10

           

           

          Old Method           E                       Date                 25.07.2010

          Old Sample Size      P10                     Time                 15:01:53

           

           

          Old Number                   443,010         Deviation Old -> New      23  %

          New Number                   543,316         Deviation New -> Old      18- %

          Inserted Rows                110,614         Percentage Too Old        25  %

          Changed Rows                      31         Percentage Too Old         0  %

          Deleted Rows                  10,308         Percentage Too New         2  %

           

           

          Use                  A

          Active Flag          A

          Analysis Method

          Sample Size

           

          Kindly suggest

        • Re: Report is running long time
          John sat
          Currently Being Moderated

          Hi,

            The z report contains select queries for bsad and bseg table.

           

          In db20 while checking the table statistics for bseg i get the error

          Table BSEG does not exist in the ABAP Dictionary.

           

          But the table(BSEG)  is active when checked in se11.

           

          but for the bsad table the statistics is

          Table                 BSAD

           

                Statistics are current (|Changes| < 50 %)

          New Method           E

          New Sample Size      P10

           

           

          Old Method           E                       Date                 25.07.2010

          Old Sample Size      P10                     Time                 15:01:53

           

           

          Old Number                   443,010         Deviation Old -> New      23  %

          New Number                   543,316         Deviation New -> Old      18- %

          Inserted Rows                110,614         Percentage Too Old        25  %

          Changed Rows                      31         Percentage Too Old         0  %

          Deleted Rows                  10,308         Percentage Too New         2  %

           

           

          Use                  A

          Active Flag          A

          Analysis Method

          Sample Size

           

           

          Any suggestions.

           

          When trying to trace using se30 we are not getting any results becos the report is not generated at all. But the status is running.

           

          Edited by: satheesh0812 on Oct 8, 2010 10:49 AM

           

          Edited by: satheesh0812 on Oct 8, 2010 10:50 AM

          • Re: Report is running long time
            Sunil Bujade
            Currently Being Moderated

            In DB20 can you please create stats for table BSAD?

            BSEG is a Cluster Table hence you can't find in DB20.

            • Re: Report is running long time
              John sat
              Currently Being Moderated

              Dear Suil,

               

              let me know how to create stats In DB20 for table BSAD?

               

              Kindly suggest

              • Re: Report is running long time
                Sunil Bujade
                Currently Being Moderated

                DB20 --> enter table name --> BSAD --> click on create (cntrl+F2)

                • Re: Report is running long time
                  John sat
                  Currently Being Moderated

                  Dear Sunil ,

                   

                  i have created Statistics for Table BSAD ,pls check below.

                   

                  Table                 BSAD

                   

                        Statistics are current (|Changes| < 50 %)

                  New Method           E

                  New Sample Size      P10

                   

                   

                  Old Method           E                       Date                 08.10.2010

                  Old Sample Size      P10                     Time                 14:30:18

                   

                   

                  Old Number                   542,990         Deviation Old -> New       0  %

                  New Number                   542,990         Deviation New -> Old       0  %

                  Inserted Rows                      0         Percentage Too Old         0  %

                  Changed Rows                       0         Percentage Too Old         0  %

                  Deleted Rows                       0         Percentage Too New         0  %

                   

                   

                  Use                  A

                  Active Flag          A

                  Analysis Method

                  Sample Size

                   

                  how to proceed further  for my issue .

                   

                  kindly suggest

    • Re: Report is running long time
      John sat
      Currently Being Moderated

      Dear Nick,

       

      Let me know how to check the size of BSAD table and Indexes are using on that table ?

       

      kindly suggets

    • Re: Report is running long time
      Markus Doehr
      Currently Being Moderated
      Nick Loy wrote:

      >

      >

      SELECT
      > "KUNNR" , "BELNR" , "BUDAT" , "DMBTR" , "GJAHR" , "XBLNR" , "HKONT" , "UMSKZ" , "SHKZG" , "AUGGJ" ,
      > "REBZJ" , "AUGBL" , "BUZEI"
      > FROM
      > "BSAD"

      >

       

      So this means that you real all data of table BSAD. I think your nested select is just taking so much time.

       

      I suggest, as already advised, to check with the develop to either use a WHERE clause to make the amount of data to be read smaller or use other methods like joins to get just the data you need.

       

      Without knowing the logic of the program and what its doing it's pointless to start the analysis from a database approach.

       

       

       

      Markus

      • Re: Report is running long time
        John sat
        Currently Being Moderated

        Hi,

         

        In the program the select query for bsad is as follows:

         

        SELECT kunnr belnr budat dmbtr augbl rebzg gjahr XBLNR HKONT BUDAT AUGDT UMSKZ SHKZG AUGGJ REBZJ PSWSL

        FROM BSAD

        INTO CORRESPONDING FIELDS OF TABLE I_BSAD

        WHERE kunnr IN s_kunnr

        AND bukrs EQ p_bukrs

        AND gjahr EQ p_gjahr

        AND budat IN s_budat

        • AND shkzg EQ 'H'

        AND blart EQ 'DZ'

        AND ( UMSKZ EQ 'X'

        OR UMSKZ EQ ' ' )

        AND HKONT NE '0011404501'

        AND HKONT NE '0011404502'.

         

        When checking in the debugging mode the program is stuck after the statement

         

        IF I_BSAD[] IS NOT INITIAL.

         

        SELECT DMBTR BELNR REBZG BUZEI FROM BSEG

        APPENDING TABLE I_BSAD_ST

        FOR ALL ENTRIES IN I_BSAD

        WHERE BELNR EQ I_BSAD-REBZG

        AND BUKRS EQ P_BUKRS

        AND GJAHR EQ I_BSAD-REBZJ " ( GJAHR EQ I_BSAD-GJAHR OR

        AND HKONT EQ '0011404501'

        OR HKONT EQ '0011404502'.

         

        ENDIF.

        Kindly suggest.

  • Re: Report is running long time
    John sat
    Currently Being Moderated

    Resolved by ABAPER

Actions