Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Report taking long time to run ...

Former Member
0 Kudos

Dear Experts,

I have the below report that pulls all Purchase orders for a single Company code. The report is taking more than 4 hours in production. Need to optimise the running time. Please help.

START-OF-SELECTION.

PERFORM get_po_details.

END-OF-SELECTION.

PERFORM process_data.

*&---------------------------------------------------------------------*

*&      Form  process_data

*&---------------------------------------------------------------------*

FORM process_data.

  data:l_date(10) type c,

       l_menge(16) type c,

       l_len type i,

       l_len1 type i.

*Looping the Internal Tables

  IF NOT it_ekpo[] IS INITIAL.

    LOOP AT it_ekpo INTO wa_ekpo.

      CLEAR : f_flag,l_date.

      v_erekz = wa_ekpo-erekz.

      wa_output-ebelp = wa_ekpo-ebelp.

      if wa_ekpo-txz01 ca '#'.

        replace '#' with ' ' into wa_ekpo-txz01.

      endif.

      wa_output-txz01 = wa_ekpo-txz01.

      wa_output-menge = wa_ekpo-menge.

      wa_output-meins = wa_ekpo-meins.

      wa_output-netpr = wa_ekpo-netpr.

      wa_output-netwr = wa_ekpo-netwr.

      wa_output-afnam = wa_ekpo-afnam.

      READ TABLE it_ekko INTO wa_ekko WITH KEY ebeln = wa_ekpo-ebeln.

      IF sy-subrc = 0.

        wa_output-bukrs = wa_ekko-bukrs.

        wa_output-lifnr = wa_ekko-lifnr.

        wa_output-ebeln = wa_ekko-ebeln.

        wa_output-bedat = wa_ekko-bedat.

* Format date

        l_date+0(2) = wa_output-bedat+6(2).

        l_date+2(2) = wa_output-bedat+4(2).

        l_date+4(4) = wa_output-bedat+0(4).

        concatenate l_date+0(2) l_date+2(2) l_date+4(4) into l_date separated by c_dot.

        wa_output-bedat = l_date.

      ENDIF.

      READ TABLE it_lfa1 INTO wa_lfa1 WITH KEY lifnr = wa_ekko-lifnr  sperr = 'X'.

      IF sy-subrc = 0.

        continue.

      else.

        wa_output-stceg = wa_lfa1-stceg .

      ENDIF.

      IF NOT wa_output-ebelp IS INITIAL.

*Fetch the Invoice Quantity

      PERFORM invoice_quantity.

*Check for Open PO

        IF f_flag NE '1'.

          CONTINUE.

        ELSE.

*PO Quantity

          v_po = qty_invd - wa_output-menge.

*PO Quantity substracted IR Qunatity.

          v_qo = wa_output-menge - qty_invd.

          l_menge = v_qo.

          CALL FUNCTION 'FTR_CORR_SWIFT_DELETE_ENDZERO'

            CHANGING

              c_value = l_menge.

          l_len = strlen( l_menge ) .

          l_len1 = l_len - 1.

          if l_menge+l_len1(1) = '.'.

            replace '.' with ' ' into l_menge.

          endif.

          wa_output-menge = l_menge .     "Quantity

*Difference of PO amount and Invoice amount.

          wa_output-netwr = wa_output-netwr - amt_invd.

          IF  ( v_po LT c_0  AND v_erekz NE c_x AND

            wa_output-netwr GE c_0 ).

            READ TABLE it_ekkn INTO wa_ekkn WITH KEY ebeln = wa_ekpo-ebeln

                                                     ebelp = wa_ekpo-ebelp.

            IF sy-subrc = 0.

              wa_output-wempf = wa_ekkn-wempf.

            ENDIF.

            APPEND wa_output TO it_output.

            CLEAR :wa_output, wa_ekko, wa_ekpo, wa_ekbe,qty_invd,

                   amt_invd,v_po,v_qo.

          else.

            clear: qty_invd,amt_invd,v_po,v_qo.

          ENDIF.

        ENDIF.

      ENDIF.

    ENDLOOP.

  ENDIF.

ENDFORM.                    " process_data

*&---------------------------------------------------------------------*

*&      Form  get_po_details

*&---------------------------------------------------------------------*

FORM get_po_details.

  REFRESH:it_ekko,it_ekpo,it_ekkn.

* Fetch the Po number, Vendor Details, Po Data for given COmpany Code

  SELECT  ebeln       "PO.Number

          bukrs     "Company Code

          lifnr       "Vendor

          bedat       "PO.Date

         INTO TABLE it_ekko

         FROM ekko

         WHERE bukrs IN s_bukrs

         AND lifnr IN s_lifnr

         and bedat >= c_date.

  IF NOT it_ekko[] IS INITIAL.

    SELECT  lifnr       "Vendor

            sperr

            stceg       "VAT Registration Number

           INTO TABLE it_lfa1

           FROM lfa1

           FOR ALL ENTRIES IN it_ekko

           WHERE lifnr = it_ekko-lifnr.

* Fetch the Po number,PO.Item

    SORT it_ekko BY ebeln.

    SELECT   ebeln    "PO.Number

             ebelp    "PO.ITEm

             loekz    "Indicator

             txz01    "Text

             menge    "Quantity

             meins    "Unit.Measure

             netpr    "price per Unit

             netwr    "Extended price ordered

             erekz

             afnam

             INTO TABLE it_ekpo

             FROM ekpo

             FOR ALL ENTRIES IN it_ekko

             WHERE ebeln EQ it_ekko-ebeln

             AND loekz EQ c_space.

  ENDIF.

  IF NOT it_ekpo[] IS INITIAL.

    SORT it_ekpo by ebeln.

    SELECT ebeln

           ebelp

           wempf

           INTO TABLE it_ekkn

           FROM ekkn

           FOR ALL ENTRIES IN it_ekpo

           WHERE ebeln = it_ekpo-ebeln

           AND   ebelp = it_ekpo-ebelp.

*Fetch history of PO details

    SELECT  ebeln    "PO number

            ebelp    "Item no.of PO

            zekkn    "Sequential Number of Account Assignment

            vgabe    "Transaction/event type

            gjahr    "Material Document Year

            belnr    "Number of Material Document

            buzei    "Item in Material Document

            menge

            shkzg

            INTO TABLE it_ekbe

            FROM ekbe

            FOR ALL ENTRIES IN it_ekpo

            WHERE ebeln = it_ekpo-ebeln

            AND   ebelp = it_ekpo-ebelp

            AND zekkn = '01'

            AND ( vgabe = '2' OR vgabe = '3' ).

   ENDIF.

  IF NOT it_ekbe[] IS INITIAL.

  SORT it_ekbe by ebeln.

* Fetch Invoice details for PO

    SELECT belnr   "Accounting Document Number

           gjahr   "Fiscal Year

           buzei   "Document Item in Invoice Document

           ebeln   "Purchasing Document Number

           ebelp   "Item Number of Purchasing Document

           wrbtr

           shkzg

           menge

           INTO TABLE it_ekbe_invoiced

           FROM rseg

           FOR ALL ENTRIES IN it_ekbe

           WHERE belnr = it_ekbe-belnr

           and gjahr = it_ekbe-gjahr

           and buzei = it_ekbe-buzei

           and ebeln = it_ekbe-ebeln

           and ebelp = it_ekbe-ebelp.

  endif.

  IF NOT it_ekbe_invoiced[] IS INITIAL.

  SORT it_ekbe_invoiced by belnr gjahr buzei.

  ENDIF.

ENDFORM.                    " get_po_details

*&---------------------------------------------------------------------*

*&      Form  invoice_quantity

*&---------------------------------------------------------------------*

FORM invoice_quantity.

CLEAR wa_ekbe.

READ TABLE it_ekbe INTO wa_ekbe with key ebeln = wa_ekpo-ebeln

                                          ebelp = wa_ekpo-ebelp.

IF sy-subrc = 0.

    CLEAR wa_ekbe.

    LOOP AT it_ekbe INTO wa_ekbe WHERE ebeln EQ wa_ekpo-ebeln

                                 AND ebelp EQ wa_ekpo-ebelp.

*   Quantity invoiced.

*  If the first character of document number is 2, ignore them

      IF wa_ekbe-belnr+0(1) <> '2'.

         READ TABLE it_ekbe_invoiced into wa_ekbe_invoiced with key belnr = wa_ekbe-belnr

                                                                    gjahr = wa_ekbe-gjahr

                                                                    buzei = wa_ekbe-buzei

                                                                    ebeln = wa_ekpo-ebeln

                                                                    ebelp = wa_ekpo-ebelp BINARY SEARCH.

* Summation of Invoiced quantity based on Debit/Credit Indicator

            IF wa_ekbe_invoiced-shkzg = 'S'.

               qty_invd = qty_invd + wa_ekbe_invoiced-menge.

               amt_invd = amt_invd + wa_ekbe_invoiced-wrbtr.

            ELSE.

               qty_invd = qty_invd - wa_ekbe_invoiced-menge.

               amt_invd = amt_invd - wa_ekbe_invoiced-wrbtr.

            ENDIF.

            f_flag = '1'.

      ENDIF.

  clear wa_ekbe_invoiced.

ENDLOOP.

ELSE.

    CLEAR amt_invd.

    qty_invd = '0'.

    f_flag = '1'.

  ENDIF.

ENDFORM.                    " invoice_quantity

I tried using joins in Form "get_po_details" for the select statements but still it takes the same time and even more in fact.

Inner join for tables EKKO and LFA1 worked, join for EKPO and EKKN worked but join for EKBE and RSEG fails. Still running time not reduced:-(

Please help. Thanks.

Kind Regards,

Hema Sundar.

2 REPLIES 2

Former Member
0 Kudos

Hi Hema,

Please use the Transaction ST05 for analysis, and find out which query is taking long time and fine tune the same.

The SQL trace tells you:

  • The SQL statements executed by your program. 
  • The values that the system uses for particular database access and changes. 
  • How the system converts ABAP Open SQL statements (such as SELECT) into Standard SQL statements. 
  • Where your application executes COMMITs. 
  • Where your application repeats the same database access. 
  • The database accesses and changes that occur in the update part of your application.

Please reffer the below link , for the trace procedure.

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/5a1de990-0201-0010-8d83-a06bdda1d...

Regards,

Arun

ThomasZloch
Active Contributor
0 Kudos