Performance tuning

Former Member · ‎02-19-2007

Hi guys!

I'll be always in favour to you if u could really help me this out.The program displays all the Sales order(RMA's) who's series starts with number 6 in our company ie for Return Manufacturing Authoriazation.What ever the orders which are returned back has a return reason text of more than 600 char long(for this i'm using FM read_text).The problem here is the we have around 6 fields in the selection screen and the end user only enters three fields ie Plant Org,Document Type(only two values ZORE and ZSRE) and the sales order creation date range.Now when i give a date range of four months it is taking 9 minutes to display the data.And the end user wants the date range of one year.When i give the range for 1 year it is running out of time and displays a runtime erro.Please find the solution for this to improve the performance.For your refrence i'm attaching my coding.Its really urgent.........................Please help..............................

REPORT z_rma_gr_report NO STANDARD PAGE HEADING LINE-SIZE 700.

TYPE-POOLS: slis.

TABLES: ekko,

ekkn,

ekpo,

ekbe,

vbak,

vbap.

SELECTION-SCREEN BEGIN OF BLOCK rmd WITH FRAME TITLE text-001.

SELECT-OPTIONS: s_org FOR vbak-vkorg OBLIGATORY, "PURCHASE ORG IMR928388

s_pnt FOR ekbe-werks, "PLANT

s_doc FOR vbak-auart, "DOCUMENT TYPE

s_so FOR vbak-vbeln, "RMA

s_pno FOR ekko-ebeln, "PURCHASE ORDER NUMBER

s_pdate FOR ekbe-budat, "PURCHASE ORDER DATE

s_cdate FOR vbak-erdat OBLIGATORY. "SO CREATION DATE

SELECTION-SCREEN END OF BLOCK rmd.

TYPES : BEGIN OF ty_itab,

rma LIKE vbak-vbeln,

enpointe_po LIKE ekkn-ebeln,

po_line_item LIKE ekkn-ebelp,

vendor_no LIKE vbap-zzp_lifnr,

vendor_name LIKE lfa1-name1,

material_doc LIKE ekbe-belnr,

gr_rev_date LIKE ekbe-budat,

rma_return(600) TYPE c,

END OF ty_itab.

TYPES: BEGIN OF ty_vbak,

vbeln TYPE vbak-vbeln,

erdat TYPE vbak-erdat,

auart TYPE vbak-auart,

END OF ty_vbak.

TYPES: BEGIN OF ty_vbap,

vbeln TYPE vbap-vbeln,

posnr TYPE vbap-posnr,

vgbel TYPE vbap-vgbel,

vgpos TYPE vbap-vgpos,

zzp_lifnr TYPE vbap-zzp_lifnr,

END OF ty_vbap.

TYPES: BEGIN OF ty_ekkn,

ebeln TYPE ekkn-ebeln,

ebelp TYPE ekkn-ebelp,

vbeln TYPE ekkn-vbeln,

vbelp TYPE ekkn-vbelp,

loekz TYPE ekkn-loekz,

END OF ty_ekkn.

TYPES: BEGIN OF ty_ekbe,

ebeln TYPE ekbe-ebeln,

ebelp TYPE ekbe-ebelp,

belnr TYPE ekbe-belnr,

bewtp TYPE ekbe-bewtp,

bwart TYPE ekbe-bwart,

budat TYPE ekbe-budat,

END OF ty_ekbe.

TYPES: BEGIN OF ty_ekko,

ebeln TYPE ekko-ebeln,

lifnr TYPE ekko-lifnr,

END OF ty_ekko.

TYPES: BEGIN OF ty_lfa1,

lifnr LIKE lfa1-lifnr,

name1 LIKE lfa1-name1,

END OF ty_lfa1.

DATA: t_itab TYPE STANDARD TABLE OF ty_itab WITH NON-UNIQUE DEFAULT KEY

INITIAL SIZE 10 WITH HEADER LINE .

DATA: w_fieldcat TYPE slis_fieldcat_alv.

DATA: t_fieldcat TYPE slis_t_fieldcat_alv.

DATA: t_vbak TYPE ty_vbak OCCURS 0 WITH HEADER LINE.

DATA: t_vbap TYPE ty_vbap OCCURS 0 WITH HEADER LINE.

DATA: t_ekko TYPE ty_ekko OCCURS 0 WITH HEADER LINE .

DATA: t_ekkn TYPE ty_ekkn OCCURS 0 WITH HEADER LINE.

DATA: t_ekbe TYPE ty_ekbe OCCURS 0 WITH HEADER LINE.

DATA: t_lfa1 TYPE ty_lfa1 OCCURS 0 WITH HEADER LINE.

DATA: BEGIN OF t_line OCCURS 0.

INCLUDE STRUCTURE tline.

DATA: END OF t_line.

START-OF-SELECTION.

PERFORM rma_sales.

END-OF-SELECTION.

PERFORM display_data.

&----

*& Form t_fieldcat

&----

Subroutine for fielcat and alv_list_display

----

FORM display_data.

PERFORM fieldcat.

PERFORM alv_list_display.

ENDFORM. "display_data

&----

*& Form fieldcat

&----

For the output field Columns

----

FORM fieldcat.

CLEAR w_fieldcat.

w_fieldcat-col_pos = '1'.

w_fieldcat-fieldname = 'RMA'.

w_fieldcat-seltext_l = 'RMA'.

w_fieldcat-ref_fieldname = 'VBELN'.

APPEND w_fieldcat TO t_fieldcat.

CLEAR w_fieldcat.

w_fieldcat-col_pos = '2'.

w_fieldcat-fieldname = 'ENPOINTE_PO'.

w_fieldcat-ref_fieldname = 'EBELN'.

w_fieldcat-seltext_l = 'ENPOINTE PO#'.

APPEND w_fieldcat TO t_fieldcat.

CLEAR w_fieldcat.

w_fieldcat-col_pos = '3'.

w_fieldcat-fieldname = 'PO_LINE_ITEM'.

w_fieldcat-ref_fieldname = 'EBELP'.

w_fieldcat-seltext_l = 'PO Line Item'.

APPEND w_fieldcat TO t_fieldcat.

CLEAR w_fieldcat.

w_fieldcat-col_pos = '4'.

w_fieldcat-fieldname = 'VENDOR_NO'.

w_fieldcat-ref_fieldname = 'ZZP_LIFNR'.

w_fieldcat-seltext_l = 'Vendor No'.

APPEND w_fieldcat TO t_fieldcat.

CLEAR w_fieldcat.

w_fieldcat-col_pos = '5'.

w_fieldcat-fieldname = 'VENDOR_NAME'.

w_fieldcat-ref_fieldname = 'NAME1'.

w_fieldcat-seltext_l = 'Vendor Name'.

APPEND w_fieldcat TO t_fieldcat.

CLEAR w_fieldcat.

w_fieldcat-col_pos = '6'.

w_fieldcat-fieldname = 'MATERIAL_DOC'.

w_fieldcat-ref_fieldname = 'BELNR'.

w_fieldcat-seltext_l = 'Material Do#(Reversal)'.

APPEND w_fieldcat TO t_fieldcat.

CLEAR w_fieldcat.

w_fieldcat-col_pos = '7'.

w_fieldcat-fieldname = 'GR_REV_DATE'.

w_fieldcat-ref_fieldname = 'BUDAT'.

w_fieldcat-seltext_l = 'GR Reversal Date'.

APPEND w_fieldcat TO t_fieldcat.

CLEAR w_fieldcat.

w_fieldcat-col_pos = '8'.

w_fieldcat-fieldname = 'RMA_RETURN'.

w_fieldcat-outputlen = 600.

w_fieldcat-datatype = 'CHAR'.

w_fieldcat-seltext_l = 'RMA(Return Reason Details)'.

APPEND w_fieldcat TO t_fieldcat.

ENDFORM. "fieldcat

&----

*& Form alv_list_display

&----

Displaying the output

----

FORM alv_list_display.

CALL FUNCTION 'REUSE_ALV_LIST_DISPLAY'

EXPORTING

i_callback_program = sy-repid

it_fieldcat = t_fieldcat

TABLES

t_outtab = t_itab.

ENDFORM. "alv_list_display

&----

*& Form rma_sales

&----

*Queries for Getting the Sales order Number and its corresponding PO,

*Line Item,Material Doc and Reversal date values.

----

FORM rma_sales.

SELECT vbeln

erdat

auart

FROM vbak

INTO TABLE t_vbak

WHERE vbeln IN s_so "Sales order

AND erdat IN s_cdate "Sales order creation date

AND auart IN s_doc "Document type

AND vkorg IN s_org. "IMR928535

SORT t_vbak BY vbeln.

IF sy-subrc = 0.

SELECT vbeln

posnr

vgbel

vgpos

zzp_lifnr

FROM vbap

INTO TABLE t_vbap

FOR ALL ENTRIES IN t_vbak

WHERE vbeln = t_vbak-vbeln

AND werks IN s_pnt. "IMR928388

IF sy-subrc = 0.

SELECT ebeln

ebelp

vbeln

vbelp

loekz

FROM ekkn INTO TABLE t_ekkn

FOR ALL ENTRIES IN t_vbap

WHERE ebeln IN s_pno

AND vbeln = t_vbap-vgbel

AND vbelp = t_vbap-vgpos.

AND loekz = ' '. "IMR928535

IF sy-subrc = 0.

SORT t_ekkn BY ebeln ebelp.

DELETE ADJACENT DUPLICATES FROM t_ekkn COMPARING ebeln ebelp."IMR928405

SELECT ebeln

ebelp

belnr

bewtp

bwart

budat

FROM ekbe INTO TABLE t_ekbe

FOR ALL ENTRIES IN t_ekkn

WHERE ebeln = t_ekkn-ebeln

AND ebelp = t_ekkn-ebelp

AND bewtp = 'E'

AND bwart = '122'

AND budat IN s_pdate.

ENDIF.

LOOP AT t_vbak.

LOOP AT t_vbap WHERE vbeln = t_vbak-vbeln.

LOOP AT t_ekkn WHERE vbeln = t_vbap-vgbel

AND vbelp = t_vbap-vgpos.

LOOP AT t_ekbe WHERE ebeln = t_ekkn-ebeln "IMR928405

AND ebelp = t_ekkn-ebelp.

t_itab-rma = t_vbak-vbeln.

t_itab-enpointe_po = t_ekkn-ebeln .

t_itab-po_line_item = t_ekkn-ebelp .

t_itab-material_doc = t_ekbe-belnr.

t_itab-gr_rev_date = t_ekbe-budat .

t_itab-vendor_no = t_vbap-zzp_lifnr.

ENDLOOP.

PERFORM vendor_detail.
PERFORM rejection_reason.

ENDLOOP.

PERFORM vendor_detail. "IMR928442

PERFORM rejection_reason.

IF NOT t_itab-rma IS INITIAL.

APPEND t_itab.

ENDIF.

CLEAR t_itab.

ENDLOOP.

ENDFORM. "rma_sales

&----

*& Form vendor_details

&----

*Subroutine for vendor name

----

FORM vendor_detail.

CLEAR t_lfa1.
REFRESH t_lfa1.

SELECT lifnr

name1 FROM lfa1

INTO TABLE t_lfa1

FOR ALL ENTRIES IN t_vbap

WHERE lifnr = t_vbap-zzp_lifnr.

IF sy-subrc = 0.

SORT t_lfa1 BY lifnr.

DELETE ADJACENT DUPLICATES FROM t_lfa1.

MOVE t_vbap-zzp_lifnr TO t_lfa1-lifnr.

LOOP AT t_lfa1 WHERE lifnr = t_vbap-zzp_lifnr.

READ TABLE t_lfa1 WITH

KEY lifnr = t_vbap-zzp_lifnr

BINARY SEARCH. "IMR928442

MOVE t_lfa1-name1 TO t_itab-vendor_name.

t_itab-vendor_name = t_lfa1-name1.

ENDLOOP.

ENDIF.

ENDFORM. "vendor_detail

&----

*& form rejection_reason

&----

Subroutine for Rma return reasons

----

FORM rejection_reason. "IMR928405

DATA: i_tdname TYPE thead-tdname.

DATA: l_vbak(10) TYPE c VALUE '0000000000'.
DATA: l_vbap(6) TYPE c VALUE '000000'.

DATA: text1(132) TYPE c,

text2(132) TYPE c,

text3(132) TYPE c,

text4(132) TYPE c,

text5(132) TYPE c.

*REFRESH: t_line. "IMR928521

*CLEAR: t_line.

OVERLAY t_vbak-vbeln WITH l_vbak."IMR928521
OVERLAY t_vbap-posnr WITH l_vbap.

*

CONCATENATE t_vbak-vbeln t_vbap-posnr INTO i_tdname.

CONDENSE i_tdname.

CALL FUNCTION 'READ_TEXT'

EXPORTING

id = 'Z010'

language = 'E'

name = i_tdname

object = 'VBBP'

TABLES

lines = t_line

EXCEPTIONS

id = 1

language = 2

name = 3

not_found = 4

object = 5

reference_check = 6

wrong_access_to_archive = 7

OTHERS = 8.

CLEAR: t_itab-rma_return.

IF sy-subrc <> 0.

ELSE .

READ TABLE t_line INDEX 1. "IMR928442

IF sy-subrc = 0.

text1 = t_line-tdline.

ENDIF.

READ TABLE t_line INDEX 2.

IF sy-subrc = 0.

text2 = t_line-tdline.

ENDIF.

READ TABLE t_line INDEX 3.

IF sy-subrc = 0.

text3 = t_line-tdline.

ENDIF.

READ TABLE t_line INDEX 4.

IF sy-subrc = 0.

text4 = t_line-tdline.

ENDIF.

READ TABLE t_line INDEX 5.

IF sy-subrc = 0.

text5 = t_line-tdline.

ENDIF.

*

LOOP AT t_line.
IF sy-tabix GT 5.
EXIT.
ENDIF.
CONCATENATE t_itab-rma_return t_line-tdline INTO t_itab-rma_return.

*

ENDLOOP.
ENDIF.

ENDIF.

CONCATENATE text1 text2 text3 text4 text5 INTO t_itab-rma_return.

ENDFORM. "vendor_detail

Former Member · ‎02-19-2007

Your problem is int this portion of code:


  LOOP AT t_vbak.


    LOOP AT t_vbap WHERE vbeln = t_vbak-vbeln.


      LOOP AT t_ekkn WHERE vbeln = t_vbap-vgbel
      AND vbelp = t_vbap-vgpos.

        LOOP AT t_ekbe WHERE ebeln = t_ekkn-ebeln           "IMR928405
        AND ebelp = t_ekkn-ebelp.

          t_itab-rma = t_vbak-vbeln.
          t_itab-enpointe_po = t_ekkn-ebeln .
          t_itab-po_line_item = t_ekkn-ebelp .
          t_itab-material_doc = t_ekbe-belnr.
          t_itab-gr_rev_date = t_ekbe-budat .
*t_itab-vendor_no = t_vbap-zzp_lifnr.

        ENDLOOP.

      ENDLOOP.

* PERFORM vendor_detail.
* PERFORM rejection_reason.
    ENDLOOP.

    PERFORM vendor_detail.                                  "IMR928442
    PERFORM rejection_reason.

    IF NOT t_itab-rma IS INITIAL.
      APPEND t_itab.
    ENDIF.
    CLEAR t_itab.
  ENDLOOP.

Nested loops are bad for performance. Triply nested loops will be far worse. This should give you an idea of how to fix the problem.:

<a href="/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops">The Performance of Nested Loops</a>

Rob

Former Member · ‎02-19-2007

Hi!

Just a very common idea: check out transaction SE30. It is very useful to determine, which part of your program is the slowest.

Regards

Tamá

raviprakash · ‎02-19-2007

Hi Younus,

I went through your code. Its really good. I found out a huge performance problem at one place. Here is the description :-

In Sub-Routine RMA_SALES, the following code should be changed:-

LOOP AT t_vbak.

LOOP AT t_vbap WHERE vbeln = t_vbak-vbeln.

LOOP AT t_ekkn WHERE vbeln = t_vbap-vgbel

AND vbelp = t_vbap-vgpos.

LOOP AT t_ekbe WHERE ebeln = t_ekkn-ebeln "IMR928405

AND ebelp = t_ekkn-ebelp.

t_itab-rma = t_vbak-vbeln.

t_itab-enpointe_po = t_ekkn-ebeln .

t_itab-po_line_item = t_ekkn-ebelp .

t_itab-material_doc = t_ekbe-belnr.

t_itab-gr_rev_date = t_ekbe-budat .

t_itab-vendor_no = t_vbap-zzp_lifnr.

ENDLOOP.

PERFORM vendor_detail.
PERFORM rejection_reason.

ENDLOOP.

*****************************NEW CODE would be as follows:-***********************

loop at t_ekbe.

loop at t_ekkn where ebeln = t_ekbe-ebeln and ebelp = t_ekbe-ebelp.

loop at t_vbap where vgbel = t_ekkn-vbeln and vgpos = t_ekkn-ebelp.

read table t_vbak with key vbeln = t_vbak-vbeln.

if sy-subrc eq 0.

t_itab-rma = t_vbak-vbeln.

t_itab-enpointe_po = t_ekkn-ebeln .

t_itab-po_line_item = t_ekkn-ebelp .

t_itab-material_doc = t_ekbe-belnr.

t_itab-gr_rev_date = t_ekbe-budat .

t_itab-vendor_no = t_vbap-zzp_lifnr.

endif.

endloop.

*********************************************************************************

Try this it would work :).

NOTE: Please award me appropiate points if the problem is solved :).

Looking forward for more queries.

Thanks and regards,

Ravi .

raviprakash · ‎02-19-2007

Hi Again,

As pointed out by Rob Burbank, nested loops are a problem in the code. I have calculated the most optimal number of traversing in the above code.

Though my code too has nested loops, but here the outer most loop has the least number of entries. Just replace your loops with mine and see it working fast.

Also, you can use field symbols to make it faster. Plus use binary search with read in one of my statements in above solution.

Hope i provided you with appropiate solution.

Thanks and regards,

Ravi.

Former Member · ‎02-19-2007

Ravi - you really need to use the binary search option (followed by some sort of indexed READ) instead of LOOPs in all cases. When you say "LOOP AT ... WHERE", it will have to loop through every line.

Rob

raviprakash · ‎02-19-2007

Hi Rob,

Ya you are true, but in the above senario, as i read the code, i found out that there would be multiple rows for each loop and not just one. So i have to use LOOPS for the outer loops. But as told by you, i have put a READ in the innermost loop. And yes, that should have a BINARY SEARCH with it, as pointed out by you. Thanks a lot, Rob.

Thus the new code would be like :-

*****************************NEW CODE would be as follows:-***********************

SORT t_vbak by vbeln.

loop at t_ekbe.

loop at t_ekkn where ebeln = t_ekbe-ebeln and ebelp = t_ekbe-ebelp.

loop at t_vbap where vgbel = t_ekkn-vbeln and vgpos = t_ekkn-ebelp.

read table t_vbak with key vbeln = t_vbak-vbeln BINARY SEARCH.

if sy-subrc eq 0.

t_itab-rma = t_vbak-vbeln.

t_itab-enpointe_po = t_ekkn-ebeln .

t_itab-po_line_item = t_ekkn-ebelp .

t_itab-material_doc = t_ekbe-belnr.

t_itab-gr_rev_date = t_ekbe-budat .

t_itab-vendor_no = t_vbap-zzp_lifnr.

endif.

endloop.

*********************************************************************************

Since the rest three loops has to return more than 1 entries, i can't use READ there.

NOTE: If you still find any issues then please do tell me Rob, it would be of

immense help to me.

Thanks and regards ,

Ravi .

Former Member · ‎02-19-2007

I would have only one loop - the outer one. Everything else should be replaced by a binary search followed by indexed reads.

Rob

raviprakash · ‎02-19-2007

Hi Rob,

How would you do that? From what i read from the program,

a) for each single entry of t_ekbe there would be multiple entries of t_ekkn based on keys.

b) for each single entry of t_ekkn there would be multiple entries of t_vbap based on keys.

c) for each single entry of t_vbap there would be multiple entries of t_vbak based on keys.

Also note: VBAK contains all the key entries from which the entries for VBAP EKKN and EKBE is derived.

If possible can you please throw some lights as on how would it be possible to achieve this using only one loop and several reads.

Thanks and regards,

Ravi.

NOTE: Please am asking this out of utter curiousity. Please dont mind :).

Former Member · ‎02-19-2007

Heve you checked the BLOG?

I'll try to get back a little later with how I would re-write the code.

Rob

Former Member · ‎02-19-2007

OK - I end up with:


FORM rma_sales.

*
*
*

  PERFORM do_reads.

*  LOOP AT t_vbak.
*
*
*    LOOP AT t_vbap WHERE vbeln = t_vbak-vbeln.
*
*
*      LOOP AT t_ekkn WHERE vbeln = t_vbap-vgbel
*      AND vbelp = t_vbap-vgpos.
*
*        LOOP AT t_ekbe WHERE ebeln = t_ekkn-ebeln           "IMR928405
*        AND ebelp = t_ekkn-ebelp.
*
*          t_itab-rma = t_vbak-vbeln.
*          t_itab-enpointe_po = t_ekkn-ebeln .
*          t_itab-po_line_item = t_ekkn-ebelp .
*          t_itab-material_doc = t_ekbe-belnr.
*          t_itab-gr_rev_date = t_ekbe-budat .
**t_itab-vendor_no = t_vbap-zzp_lifnr.
*
*        ENDLOOP.
*
*      ENDLOOP.
*
** PERFORM vendor_detail.
** PERFORM rejection_reason.
*    ENDLOOP.
*
*    PERFORM vendor_detail.                                  "IMR928442
*    PERFORM rejection_reason.
*
*    IF NOT t_itab-rma IS INITIAL.
*      APPEND t_itab.
*    ENDIF.
*    CLEAR t_itab.
*  ENDLOOP.

ENDFORM. "rma_sales

*&---------------------------------------------------------------------*
*&      Form  do_reads
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM do_reads .

  DATA: vbak_index LIKE sy-tabix,
        vbap_index LIKE sy-tabix,
        ekkn_index LIKE sy-tabix,
        ekbe_index LIKE sy-tabix.

  SORT: t_vbak BY vbeln,
        t_vbap BY vbeln,
        t_ekkn BY vbeln vbelp,
        t_ekbe BY ebeln ebelp.

  PERFORM get_all_vendors.

  LOOP AT t_vbak.

    READ TABLE t_vbap WITH KEY
      vbeln = t_vbak-vbeln
      BINARY SEARCH.
    IF sy-subrc = 0.
      vbap_index = sy-tabix.
      WHILE sy-subrc = 0.

        READ TABLE t_ekkn WITH KEY
          vbeln = t_vbap-vgbel
          vbelp = t_vbap-vgpos
          BINARY SEARCH.
        IF sy-subrc = 0.
          ekkn_index = sy-tabix.
          WHILE sy-subrc = 0.

            READ TABLE t_ekbe WITH KEY
              ebeln = t_ekkn-ebeln
              ebelp = t_ekkn-ebelp
              BINARY SEARCH.
            IF sy-subrc = 0.
              ekbe_index = sy-tabix.
              WHILE sy-subrc = 0.
                t_itab-rma          = t_vbak-vbeln.
                t_itab-enpointe_po  = t_ekkn-ebeln .
                t_itab-po_line_item = t_ekkn-ebelp .
                t_itab-material_doc = t_ekbe-belnr.
                t_itab-gr_rev_date  = t_ekbe-budat .
                PERFORM: vendor_detail,
                         rejection_reason.
                APPEND t_itab.                           " ????

                ekbe_index = ekbe_index + 1.
                READ TABLE t_ekbe INDEX ekbe_index.
                IF sy-subrc = 0.
                  IF t_ekbe-ebeln <> t_ekkn-ebeln OR
                     t_ekbe-ebelp <> t_ekkn-ebelp.
                    sy-subrc = 9.
                  ENDIF.
                ENDIF.
              ENDWHILE.
            ENDIF.

            ekkn_index = ekkn_index + 1.
            READ TABLE t_ekkn INDEX ekkn_index.
            IF sy-subrc = 0.
              IF t_ekkn-vbeln <> t_vbap-vgbel OR
                 t_ekkn-vbelp <> t_vbap-vgpos.
                sy-subrc = 9.
              ENDIF.
            ENDIF.
          ENDWHILE.
        ENDIF.

        vbap_index = vbap_index + 1.
        READ TABLE t_vbap INDEX vbap_index.
        IF sy-subrc = 0.
          IF t_vbap-vbeln <> t_vbak-vbeln.
            sy-subrc = 9.
          ENDIF.
        ENDIF.
      ENDWHILE.
    ENDIF.

    LOOP AT t_vbap WHERE vbeln = t_vbak-vbeln.

      LOOP AT t_ekkn WHERE vbeln = t_vbap-vgbel
      AND vbelp = t_vbap-vgpos.

        LOOP AT t_ekbe WHERE ebeln = t_ekkn-ebeln           "IMR928405
        AND ebelp = t_ekkn-ebelp.

          t_itab-rma = t_vbak-vbeln.
          t_itab-enpointe_po = t_ekkn-ebeln .
          t_itab-po_line_item = t_ekkn-ebelp .
          t_itab-material_doc = t_ekbe-belnr.
          t_itab-gr_rev_date = t_ekbe-budat .

        ENDLOOP.

      ENDLOOP.

    ENDLOOP.

    PERFORM vendor_detail.                                  "IMR928442
    PERFORM rejection_reason.

    IF NOT t_itab-rma IS INITIAL.
      APPEND t_itab.
    ENDIF.
    CLEAR t_itab.
  ENDLOOP.


ENDFORM.                    " do_reads

*&---------------------------------------------------------------------*
*&      Form  get_all_vendors
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM get_all_vendors .

  SELECT lifnr
  name1 FROM lfa1
  INTO TABLE t_lfa1.

  SORT t_lfa1 BY lifnr.

ENDFORM.                    " get_all_vendors

*&--------------------------------------------------------------------*
*& Form vendor_details
*&--------------------------------------------------------------------*
*Subroutine for vendor name
*---------------------------------------------------------------------*
FORM vendor_detail.

  READ TABLE t_lfa1 WITH
  KEY lifnr = t_vbap-zzp_lifnr
  BINARY SEARCH.

  t_itab-vendor_name = t_lfa1-name1.

ENDFORM. "vendor_detail

This is untested and should just be a model. In addition, getting vendor details needed some work.

Rob

Former Member · ‎02-20-2007

Hai Ravi!

Thanks a hundred times for going through my query thats really nice of you for your reply.But there are few conditions where the changed loops are not working particularly at loop at ekkn table.Any ways the problem is solved with the answer given by Rob even though all give u points on the issue worked

Former Member · ‎02-20-2007

Hai Rob!

We made it Rob thanks soooo much its working fine with single loop.I thought its not possible initially because as said by Ravi we have multiple PO's for each RMA's(sales order here).I started it with a doubt but really works fine but let me get through the it in testing with lots of values and check the performance.

Former Member · ‎02-21-2007

Hai Rob!

I'm facing the same problem in QAS after changing the coding.It triggers at the same point after runtime error(takes time and points out at select statement for EKKN table in my coding).Which was the same case previously when i give the date range of one year in the selection screen.And more over if i reduce the date range of 6 months it displays the output after 7minutes.This has improved from the previous time of 11 minutes.And when i give a date range for 8 or 9 months it goes into runtime error yet again now at different location it points out at subroutine Concatenate t_vbak t_vbap to t_itdname.This was the same case previously too before changing the nested loops.

More over when i give the date range for 1 year after one minute its jumping on to runtime error and points the problem at Select query for table EKKN.Where in it displays the output upto the date range of 6 months why this is happing i'm not able to understand.Please help we have a production probelm.The coding i've changed is instead of nested loops i've replaced it with one loop for t_vbak like u've forwarded the code(i just copied it and uncoded the previous one)

Former Member · ‎02-21-2007

Hi Rob!

Previously i've used the subroutine for vendor_details(and included the select statement and read statement in the same subroutine) in the loop of t_vbak but did'nt used another subroutine which u've created called get_all_vendor(which u used it before the loop at t_vbak) when i used it the time taken for the object LFA1 in ST05(number of iterations has reduced) has reduced but the object STHX which is the table for text(return rejection resons ie RMA return reasons text table) duration is increasing and when i try writing the READ_TEXT function out of the loop of t_vbak its not displaying the text for the concern t_vbak and t_vbeln ie for the concern text ID of 16 digit for example '0060000204000010'.I've tried various ways but one thing or the other thing is eating my performance

Former Member · ‎02-21-2007

You mention EKKN and LFA1, but I don't see these tables being used in this program. (Ignore this. I was looking at something else.)

I changed the way the vendor details are retrieved. Did you check that?

Has ST05 shoown you an area to look at?

Rob

Message was edited by:

Rob Burbank

Message was edited by:

Rob Burbank

Former Member · ‎02-21-2007

This code:


      SELECT ebeln
      ebelp
      vbeln
      vbelp
      loekz
      FROM ekkn INTO TABLE t_ekkn
      FOR ALL ENTRIES IN t_vbap
      WHERE ebeln IN s_pno
      AND vbeln = t_vbap-vgbel
      AND vbelp = t_vbap-vgpos.

will probably have problems finding an index, particularly if s_pno is empty. Try taking that out of the select (VBELN and VBELP are a secondary index) and then remove entries from the internal table that are not in s_pno.

Rob

Former Member · ‎02-22-2007

Rob!

Which field or which condition should i take out from the select statement.Even if we dont provide any thing in the s_po as you know it will go up with dynamic select statements and find the required index and in this case as the transparent table itself has secondary index V(index name).I know the root cause for killing my time is this select statement(as i've seen in ST05 this cross the time and takes large duration and no other select statement takes as much as time this does).

Coming back to ur answer how can i take that fields out of select statement(if its vbeln and vbelp then we cannot display the output bcoz it does'nt satisfy my conditions) and if it is Ebeln then there is not much difference.Be specific please

Former Member · ‎02-22-2007

See if this is better:


SELECT ebeln ebelp vbeln vbelp loekz
  FROM ekkn
  INTO TABLE t_ekkn
  FOR ALL ENTRIES IN t_vbap
  WHERE vbeln = t_vbap-vgbel
  AND   vbelp = t_vbap-vgpos.

DELETE t_ekkn WHERE NOT
  ebeln IN s_po.

Rob

Former Member · ‎02-26-2007

Hai Rob!

After the performance issue now i've encountered a issue in QAS.As u've been through my coding plz find a bug the problem is only in QAS but not in DEV.When we dont have any value in vbap-vgbel then it should not display the concern RMA.If we check it out in VA03 then the concern RMA has no PO.But still it displays the output with some other PO.I've debugged it the problem is when we dont have any VBAP-VGBEL for the concern VBAK-VBELN then it should not show the ouptut for that concern RMA.(but i dont know from where it is getting the wrong one from).And more over i did'nt changed the loops to read statements(bcoz ur read statements where not satisfying some conditions and where displaying the wrong data,so when i managed wth my own logic which solved some of the issues but this one really troubling).Plz plzzzzzzzzzz help.

Former Member · ‎03-02-2007

Hai Rob!

Sorry to trouble you again i have a same performance problem yet again.

can i use the delete statement that you've mentioned for the table t_vbak.For the same select statement this time with other requirement the end user will be using only posting date that is t_ekbe-budat plz find this in the first page.Now i've changed the style of my selection options.What my user wants is now he'll be entering Posting date range instead of sales order creation date it t_vbak-erdat.Leaving sales order numbe , sales order date as blank.With this the first select statements retrievs huge data more than 40,600 entries but if i give the Sales order date range then it retrievs 4069 entries only.So for this entries we again have 50,300 entries in t_vbap and time is runing out at t_ekkn table.

i'm trying to use this statement

delete t_vbak where not vbeln in s_po date which is posting date here no sales order date which was the previous case but no use.. can u give some suggestion to reduce the entries and get exact sales order for the concern posting date

Former Member · ‎02-22-2007

Instead of nested loop use only one loop and read the next internal table using the key field.Check sy-subrc equal to zero,move the data into final internal table itab.

This will improves the performance of the program.Even use if not t_vbak[] is initial.

select vbeln posnr vgbel zzp_lifnr from vbap

into table t_vbap

for all entries in t_vbak

where vbeln = tvbak-vbeln

and werks in s_pnt.

if sy-subrc eq 0.

message e000 'no data found'.

endif.

Former Member · ‎02-22-2007

please go through my coding and the chain of my questions.The problem regarding the nested loops is solved and the problem here is not with the select statement in t_vbap but in the select statement mentioned above this is having huge data and the problem is the date range mentioned above is not accomidating that memory of 62MB(which it displays in the runtime error and points that the problem is in the above select statement.

The problem here is purely based on resource management(Memory our operating system has called Malloc(Roll Area Memory distribution done by the Administrator) and time management(this we call as performance as u know).Both are inversly proportional in SAP.So this is tough time for me out here,any ways please go through the Page 1 and 2 and help me out again.Thanks soooo much for spending time.........(this is itself is more to me)