Skip to Content
0
Former Member
Feb 19, 2007 at 12:53 PM

Performance tuning

32 Views

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.

ENDIF.

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.

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