Performance issue

Former Member · ‎05-26-2006

Hi friends,

Please See the below code.It is not taking much time to exicute but when we done run time analysis, the database utillization is neary 60% So it should be reduced.

I have done my best but still it not reduced.

Can any body tell me what changes need to be done.

I am sure of reward points.

REPORT ZPSPAR12_SER_ORD

NO STANDARD PAGE HEADING

MESSAGE-ID ZPS

LINE-COUNT 65

LINE-SIZE 120.

&----

*& TABLES

&----

TABLES: AUFK, "Order master data

VIQMEL, "Notification Header

KNA1, "General Data in Customer Master

IHPA, "Plant Maintenance: Partners

PMCO, "Cost structure of maintenance order

T003O, "Order Types

T001W, "Plant

AFKO. "Order header data PP orders

&----

*& TYPE POOLS

&----

TYPE-POOLS SLIS. "Global types for generic building blocks

&----

*& PROGRAM VARIABLES

&----

DATA: G_COL TYPE I, "Column position for fieldcat

G_REVENUE LIKE PMCO-WRT00, "Revenues

G_ACTCOST LIKE PMCO-WRT01, "Actual Costs

G_PLCOST LIKE PMCO-WRT02, "Plan Costs

lv_lines type i.

&----

*& STRUCTURES

&----

DATA: I_FIELDCATTAB_WA TYPE SLIS_FIELDCAT_ALV, "Fieldcat Work Area for ALV

I_TITLE_WA TYPE SLIS_LISTHEADER, "Title Work Area for ALV

GT_LAYOUT TYPE SLIS_LAYOUT_ALV. "Layout Settings for Color

&----

*& INTERNAL TABLES

&----

Internal table of plants for auth check

DATA: BEGIN OF it_plant OCCURS 0,

werks like t001w-werks,

END OF it_plant.

" Internal table containing AUFK table contents

DATA I_AUFKTAB LIKE AUFK OCCURS 0 WITH HEADER LINE.

" Internal table containing final data for the Report screen

DATA: BEGIN OF I_FINTAB OCCURS 0,

AUFNR LIKE AUFK-AUFNR,

KTEXT LIKE AUFK-KTEXT,

KUNUM LIKE VIQMEL-KUNUM,

NAME1 LIKE KNA1-NAME1,

I_PARNR LIKE IHPA-PARNR,

WRT00 LIKE PMCO-WRT00,

WRT01 LIKE PMCO-WRT01,

USER4 LIKE AUFK-USER4,

WRT02 LIKE PMCO-WRT01,

PROFIT(10) TYPE P DECIMALS 2,

PROFITP(5) TYPE P DECIMALS 2,

GSTRP LIKE AFKO-GSTRP,

LINE_COL(4) TYPE C,

END OF I_FINTAB.

" Internal table containing PMCO table contents

DATA: I_PMCO LIKE PMCO OCCURS 0 WITH HEADER LINE,

" Internal table containing final data for the Report screen

" with selection screen criteria

I_FINALTAB LIKE I_FINTAB OCCURS 0 WITH HEADER LINE,

" Fieldcat Internal table for ALV

I_FIELDCATTAB TYPE SLIS_T_FIELDCAT_ALV ,

" Title Internal table for ALV

I_TITLE TYPE SLIS_T_LISTHEADER .

&----

*& SELECT-OPTIONS

&----

SELECT-OPTIONS: S_AUFNR FOR AUFK-AUFNR, "Job Number

S_KTEXT FOR AUFK-KTEXT, "Job Description

S_KUNUM FOR VIQMEL-KUNUM, "Customer Number

S_NAME1 FOR KNA1-NAME1, "Customer Name

S_PARNR FOR IHPA-PARNR NO INTERVALS

NO-EXTENSION MATCHCODE OBJECT USER_COMP, "Supervisor

S_WRT00 FOR PMCO-WRT00 , "Actual Costs

S_WRT01 FOR PMCO-WRT01 , "Plan Costs

S_USER4 FOR AUFK-USER4 , "Estimated Costs

S_WRT02 FOR PMCO-WRT02, "Revenues

S_PROFIT FOR PMCO-WRT03 , "Profit

S_PROFP FOR PMCO-WRT04 NO INTERVALS

NO-EXTENSION, "Profit %

S_GSTRP FOR AFKO-GSTRP, "Period of Time

S_WERKS FOR AUFK-WERKS, "Plant

S_AUART FOR AUFK-AUART. "Order Type

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

AT SELECTION-SCREEN

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

Event which occurs each time the user hits enter on the selection
screen.

AT SELECTION-SCREEN.

perform plant_authorization_check.

perform validate_select_options.

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

START-OF-SELECTION

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

START-OF-SELECTION.

"Retrieves the data from the table AUFK into internal table I_AUFKTAB

PERFORM SUB_SELECTION_AUFKTAB.

"Final Internal table containing the report fields

PERFORM SUB_SELECTION_FINALTAB.

"Defining the fieldcatalog to be used for the report

PERFORM SUB_FIELDCAT_BUILD.

"Define the Top-Of-Page contents

PERFORM SUB_FIELDCAT_TITLE.

"Mapping the internal table I_FINALTAB with fieldcat

PERFORM SUB_FIELDCAT_DISPLAY.

&----

*& Form SUB_SELECTION_AUFKTAB

&----

Retrieves the data from the table AUFK into internal table I_AUFKTAB

----

FORM SUB_SELECTION_AUFKTAB.

SELECT AUFNR KTEXT USER4 OBJNR INTO CORRESPONDING FIELDS OF TABLE I_AUFKTAB

FROM AUFK

FOR ALL ENTRIES IN it_plant

WHERE AUFNR IN S_AUFNR AND

KTEXT IN S_KTEXT AND

WERKS IN S_WERKS AND

AUART IN S_AUART AND

USER4 IN S_USER4 AND

werks eq it_plant-werks.

ENDFORM. "SUB_SELECTION_AUFKTAB

&----

*& Form SUB_SELECTION_FINALTAB

&----

Final Internal table containing the report fields

----

FORM SUB_SELECTION_FINALTAB.

LOOP AT I_AUFKTAB.

I_FINTAB-AUFNR = I_AUFKTAB-AUFNR.

I_FINTAB-KTEXT = I_AUFKTAB-KTEXT.

I_FINTAB-USER4 = I_AUFKTAB-USER4.

SELECT SINGLE * FROM IHPA WHERE OBJNR = I_AUFKTAB-OBJNR AND PARVW = 'AG' .

IF SY-SUBRC = 0.

"Retrieving customer number from VIQMEL into final internal table

I_FINTAB-KUNUM = IHPA-PARNR.

ENDIF.

SELECT SINGLE * FROM KNA1 WHERE KUNNR = I_FINTAB-KUNUM .

IF SY-SUBRC = 0.

"Retrieving customer name from KNA1 into final internal table

I_FINTAB-NAME1 = KNA1-NAME1.

ENDIF.

SELECT SINGLE * FROM IHPA WHERE OBJNR = I_AUFKTAB-OBJNR AND PARVW = 'VU'.

IF SY-SUBRC = 0.

"Retrieving supervisor from IHPA into final internal table

I_FINTAB-I_PARNR = IHPA-PARNR.

ENDIF.

"Clearing the internal table contents I_PMCO

CLEAR I_PMCO[].

SELECT * FROM PMCO INTO TABLE I_PMCO WHERE OBJNR = I_AUFKTAB-OBJNR.

CLEAR: G_REVENUE,G_ACTCOST,G_PLCOST.

LOOP AT I_PMCO .

"Calculating the Plancosts, Actual Costs and Revenues depending on Type

"and Category

IF I_PMCO-BELTP = '2' .

G_REVENUE = G_REVENUE + I_PMCO-WRT00 + I_PMCO-WRT01 + I_PMCO-WRT02 +

I_PMCO-WRT03 + I_PMCO-WRT04 + I_PMCO-WRT05 + I_PMCO-WRT06 + I_PMCO-WRT07 +

I_PMCO-WRT08 + I_PMCO-WRT09 + I_PMCO-WRT10 + I_PMCO-WRT11 + I_PMCO-WRT12 +

I_PMCO-WRT13 + I_PMCO-WRT14 + I_PMCO-WRT15 + I_PMCO-WRT16.

ENDIF.

IF I_PMCO-BELTP = '1' and I_PMCO-WRTTP = '04'.

G_ACTCOST = G_ACTCOST + I_PMCO-WRT00 + I_PMCO-WRT01 + I_PMCO-WRT02 +

I_PMCO-WRT03 + I_PMCO-WRT04 + I_PMCO-WRT05 + I_PMCO-WRT06 + I_PMCO-WRT07 +

I_PMCO-WRT08 + I_PMCO-WRT09 + I_PMCO-WRT10 + I_PMCO-WRT11 + I_PMCO-WRT12 +

I_PMCO-WRT13 + I_PMCO-WRT14 + I_PMCO-WRT15 + I_PMCO-WRT16.

ELSEIF I_PMCO-BELTP = '1' and I_PMCO-WRTTP = '01'.

G_PLCOST = G_PLCOST + I_PMCO-WRT00 + I_PMCO-WRT01 + I_PMCO-WRT02 +

I_PMCO-WRT03 + I_PMCO-WRT04 + I_PMCO-WRT05 + I_PMCO-WRT06 + I_PMCO-WRT07 +

I_PMCO-WRT08 + I_PMCO-WRT09 + I_PMCO-WRT10 + I_PMCO-WRT11 + I_PMCO-WRT12 +

I_PMCO-WRT13 + I_PMCO-WRT14 + I_PMCO-WRT15 + I_PMCO-WRT16.

ENDIF.

ENDLOOP.

SELECT SINGLE * FROM AFKO WHERE AUFNR = I_AUFKTAB-AUFNR.

" Retrieving the time period.

IF SY-SUBRC = 0.

I_FINTAB-GSTRP = AFKO-GSTRP.

ENDIF.

I_FINTAB-WRT00 = G_ACTCOST.

I_FINTAB-WRT01 = G_PLCOST.

I_FINTAB-WRT02 = G_REVENUE.

IF G_REVENUE < '0.00'.

G_REVENUE = - G_REVENUE.

I_FINTAB-WRT02 = G_REVENUE.

ENDIF.

I_FINTAB-PROFIT = I_FINTAB-WRT02 - I_FINTAB-WRT00.

IF I_FINTAB-WRT02 IS INITIAL.

I_FINALTAB-PROFITP = 0.

ELSE.

I_FINTAB-PROFITP = I_FINTAB-PROFIT * 100 / I_FINTAB-WRT02.

ENDIF.

APPEND I_FINTAB.

CLEAR I_FINTAB.

ENDLOOP.

" Moving to final internal table for the corresponding selection screen “values.

LOOP AT I_FINTAB WHERE WRT00 IN S_WRT00 AND WRT01 IN S_WRT01 AND WRT02 IN S_WRT02

AND PROFIT IN S_PROFIT AND GSTRP IN S_GSTRP AND KUNUM IN S_KUNUM AND NAME1 IN S_NAME1

AND I_PARNR IN S_PARNR.

I_FINALTAB = I_FINTAB.

APPEND I_FINALTAB.

CLEAR I_FINALTAB.

ENDLOOP.

" Final Internal Table is empty

IF ( I_FINALTAB[] IS INITIAL ).

MESSAGE S010.

ENDIF..

" Color handling depending on the Profit Percentage

LOOP AT I_FINALTAB.

IF I_FINALTAB-PROFITP < S_PROFP-LOW.

I_FINALTAB-LINE_COL = 'C610'.

ELSE.

I_FINALTAB-LINE_COL = 'C300'.

ENDIF.

MODIFY I_FINALTAB.

CLEAR I_FINALTAB.

ENDLOOP.

" Default sort order - Order Number

SORT I_FINALTAB BY AUFNR.

ENDFORM. "SUB_SELECTION_FINALTAB

&----

*& Form SUB_FIELDCAT_BUILD

&----

Defining the fieldcatalog to be used for the report

----

FORM SUB_FIELDCAT_BUILD.

PERFORM SUB_FIELDCAT USING '' 'AUFNR' text-001 G_COL 'X'.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'KTEXT' text-002 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'KUNUM' text-003 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'NAME1' text-004 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'I_PARNR' text-005 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'WRT00' text-006 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'WRT01' text-007 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'USER4' text-008 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'WRT02' text-009 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'PROFIT' text-010 G_COL ''.

ADD 1 TO G_COL.

PERFORM SUB_FIELDCAT USING '' 'PROFITP' text-011 G_COL ''.

ADD 1 TO G_COL.

ENDFORM. "SUB_FIELDCAT_BUILD

&----

*& Form SUB_FIELDCAT

&----

Defining the fields in the fieldcatalog used for the report

----

--> L_TABNAME : Table name for the fieldcat
--> L_FNAME : Field name for the fieldcat
--> L_SEL : Field Description for the fieldcat
--> L_COL : Column position for the fieldcat
--> L_KEY : Key for the fieldcat

----

FORM SUB_FIELDCAT USING L_TABNAME TYPE SLIS_TABNAME

L_FNAME TYPE SLIS_FIELDNAME

L_SEL TYPE DD03P-SCRTEXT_L

L_COL TYPE I

L_KEY TYPE C.

I_FIELDCATTAB_WA-REF_TABNAME = L_TABNAME.

I_FIELDCATTAB_WA-FIELDNAME = L_FNAME.

I_FIELDCATTAB_WA-SELTEXT_L = L_SEL.

I_FIELDCATTAB_WA-COL_POS = L_COL.

I_FIELDCATTAB_WA-KEY = L_KEY.

APPEND I_FIELDCATTAB_WA TO I_FIELDCATTAB.

ENDFORM. " SUB_FIELDCAT

&----

*& Form SUB_FIELDCAT_DISPLAY

&----

Mapping the internal table I_FINALTAB with fieldcat

----

FORM SUB_FIELDCAT_DISPLAY.

GT_LAYOUT-ZEBRA = 'X'.

GT_LAYOUT-DETAIL_POPUP = ' '.

GT_LAYOUT-INFO_FIELDNAME = 'LINE_COL'.

CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'

EXPORTING

I_CALLBACK_PROGRAM = 'ZPSPAR12_SER_ORD'

IT_FIELDCAT = I_FIELDCATTAB

I_CALLBACK_TOP_OF_PAGE = 'TOP_OF_PAGE'

IS_LAYOUT = GT_LAYOUT

I_SAVE = 'A'

TABLES

T_OUTTAB = I_FINALTAB

EXCEPTIONS

PROGRAM_ERROR = 1

OTHERS = 2.

ENDFORM. "SUB_FIELDCAT_DISPLAY

&----

*& Form SUB_FIELDCAT_TITLE

&----

Define the Top-Of-Page contents

----

FORM SUB_FIELDCAT_TITLE.

I_TITLE_WA-TYP = 'H'.

I_TITLE_WA-INFO = text-014.

APPEND I_TITLE_WA TO I_TITLE.

I_TITLE_WA-TYP = 'S'.

I_TITLE_WA-KEY = text-012.

I_TITLE_WA-INFO = SY-UNAME.

APPEND I_TITLE_WA TO I_TITLE.

I_TITLE_WA-TYP = 'S'.

I_TITLE_WA-KEY = text-013.

I_TITLE_WA-INFO0(2) = SY-DATUM6(2).

I_TITLE_WA-INFO+2(1) = '.'.

I_TITLE_WA-INFO3(2) = SY-DATUM4(2).

I_TITLE_WA-INFO+5(1) = '.'.

I_TITLE_WA-INFO6(4) = SY-DATUM0(4).

APPEND I_TITLE_WA TO I_TITLE.

ENDFORM. "SUB_FIELDCAT_TITLE

&----

*& Form TOP_OF_PAGE

&----

TOP-OF-PAGE

----

FORM TOP_OF_PAGE.

CALL FUNCTION 'REUSE_ALV_COMMENTARY_WRITE'

EXPORTING

IT_LIST_COMMENTARY = I_TITLE.

ENDFORM. "TOP_OF_PAGE

INCLUDE ZPSPAR12_FORMS_F01.

include:

----

***INCLUDE ZPSPAR12_FORMS_F01 .

----

&----

*& Form plant_authorization_check

&----

text

----

--> p1 text
<-- p2 text

----

FORM plant_authorization_check .

refresh it_plant[].

Populate internal table with all plants

if sy-ucomm eq 'ONLI'.

if s_werks is not initial.

loop at s_werks.

if s_werks-option = 'EQ'.

it_plant-werks = s_werks-low.

append it_plant.

elseif s_werks-option = 'BT'.

select werks into it_plant-werks from T001W where werks between s_werks-low and s_werks-high.

append it_plant.

endselect.

endif.

endloop.

else.

select werks into it_plant-werks from T001W.

append it_plant.

endselect.

endif.

Allow user to enter individual plants without initiating the authority check

loop at it_plant.

AUTHORITY-CHECK OBJECT 'I_IWERK'

ID 'TCD' FIELD SY-TCODE

ID 'IWERK' FIELD it_plant-werks.

IF SY-SUBRC ne 0. " If user has authorization

delete it_plant.

ENDIF.

endloop.

DESCRIBE TABLE it_plant LINES lv_lines.

if lv_lines eq 0.

message E155.

endif.

ENDFORM. " plant_authorization_check

&----

*& Form validate_select_options

&----

text

----

--> p1 text
<-- p2 text

----

FORM validate_select_options .

SELECT SINGLE * FROM AUFK WHERE AUFNR IN S_AUFNR.

IF SY-SUBRC <> 0 AND NOT ( S_AUFNR IS INITIAL ).

*---Invalid Order Number

MESSAGE E001.

ENDIF.

SELECT SINGLE * FROM AUFK WHERE KTEXT IN S_KTEXT.

IF SY-SUBRC <> 0 AND NOT ( S_KTEXT IS INITIAL ).

*---Invalid Order Description

MESSAGE E002.

ENDIF.

SELECT SINGLE * FROM VIQMEL WHERE KUNUM IN S_KUNUM.

IF SY-SUBRC <> 0 AND NOT ( S_KUNUM IS INITIAL ).

*---Invalid Customer Number

MESSAGE E003.

ENDIF.

SELECT SINGLE * FROM KNA1 WHERE NAME1 IN S_NAME1.

IF SY-SUBRC <> 0 AND NOT ( S_NAME1 IS INITIAL ).

*---Invalid Customer Description

MESSAGE E004.

ENDIF.

SELECT SINGLE * FROM IHPA WHERE PARNR IN S_PARNR.

IF SY-SUBRC <> 0 AND NOT ( S_PARNR IS INITIAL ).

*---Invalid Supervisor

MESSAGE E005.

ENDIF.

SELECT SINGLE * FROM AUFK WHERE USER4 IN S_USER4.

IF SY-SUBRC <> 0 AND NOT ( S_USER4 IS INITIAL ).

*---Invalid Estimation Costs

MESSAGE E006.

ENDIF.

SELECT SINGLE * FROM T001W WHERE WERKS IN S_WERKS.

IF SY-SUBRC <> 0 AND NOT ( S_WERKS IS INITIAL ).

*---Invalid Plant

MESSAGE E008.

ENDIF.

SELECT SINGLE * FROM T003O WHERE AUART IN S_AUART.

IF SY-SUBRC <> 0 AND NOT ( S_AUART IS INITIAL ).

*---Invalid Order Type

MESSAGE E009.

ENDIF.

ENDFORM. " validate_select_options

Thanks in Advance.

Former Member · ‎05-26-2006

don't use select statement in loop .

in ur code u use like that...

LOOP AT I_AUFKTAB.

I_FINTAB-AUFNR = I_AUFKTAB-AUFNR.

I_FINTAB-KTEXT = I_AUFKTAB-KTEXT.

I_FINTAB-USER4 = I_AUFKTAB-USER4.

SELECT SINGLE * FROM IHPA WHERE OBJNR = I_AUFKTAB-OBJNR AND PARVW = 'AG' .

IF SY-SUBRC = 0.

select all the value outside the loop.....ur performance increase many times...

Former Member · ‎05-26-2006

Hi,

Remove all the SELECT* stmts and use only the required items from the table.

For eg: select single gstrp from AFKO where...

This will reduce the database utilisation.

Also, remove all the select stmts within the loop. Use FOR ALL ENTRIES instead.

Hope these hints help you

Regards,

Anjali

former_member188685 · ‎05-26-2006

Hi,

have a look at this part.. this is really take lot of time.

if sy-ucomm eq 'ONLI'.
if s_werks is not initial.
loop at s_werks.
if s_werks-option = 'EQ'.
it_plant-werks = s_werks-low.
append it_plant.
elseif s_werks-option = 'BT'.
select werks into it_plant-werks from T001W where werks between s_werks-low and s_werks-high.
append it_plant.
endselect.

endif.

endloop.
else.
select werks into it_plant-werks from T001W.
append it_plant.
endselect.
endif.

try to avoid select and endselect and use for all entries.

Regards

vijay

Former Member · ‎05-26-2006

Hi,

In the tables declaration,declare only the tables which u are using in select-options.There is no need to use into corresponding fields in the statement

SELECT AUFNR KTEXT USER4 OBJNR INTO CORRESPONDING FIELDS OF TABLE I_AUFKTAB

FROM AUFK

instead u can just use into table.

instead of select * use select required fields , whereever applicable.

Award if it helps.

regards,

keerthi.

Former Member · ‎05-26-2006

Your problems seem to start in the 'validate_select_options' form. There you are doing a number of unindexed selects on fairly large tables with select options that may be empty. You also have to make sure that the select options are not empty for all tests - here and in the rest of the program.

Try getting rid of these three statements:


* Here you <b>must</b> make sure that s_ktext is not empty <b>before</b> doing the select. But I would just get rid of it entirely.
  SELECT SINGLE * FROM aufk WHERE ktext IN s_ktext.
  IF sy-subrc <> 0 AND NOT ( s_ktext IS INITIAL ).
*---Invalid Order Description
    MESSAGE e002.
  ENDIF.

  SELECT SINGLE * FROM kna1 WHERE name1 IN s_name1.
  IF sy-subrc <> 0 AND NOT ( s_name1 IS INITIAL ).
*---Invalid Customer Description
    MESSAGE e004.
  ENDIF.

  SELECT SINGLE * FROM aufk WHERE user4 IN s_user4.
  IF sy-subrc <> 0 AND NOT ( s_user4 IS INITIAL ).
*---Invalid Estimation Costs
    MESSAGE e006.
  ENDIF.

Also ensure that the select options are not empty everywhere before doing a select.

Rob

Former Member · ‎05-26-2006

In addiion to the above, check if there is a view that combines some of the tables that you are using, eg. CAUFV is a view with data from AFKO and AUFK.

MattG.

LucianoBentiveg · ‎05-26-2006

I was analizing your source code with AboveSoft analizer, and there is a lot of querys with low performance, see performance report <a href="http://personales.ciudad.com.ar/kakon/analizer2.html">here</a>.

Regards.