Solved: Performance improvement

Former Member · ‎2008 May 09

Hi All,

Please check the below main logic code of report and change the code to improve the performance of the report, because its taking too much time to run.

START-OF-SELECTION.

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

CALL FUNCTION 'Z_PRT_PROTOCOL_REPORT_CALL'.

DISPLAY SELECTIONS IF REQUESTED
-------------------------------

PERFORM DISPLAY_SELECTIONS.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Obtaining WBS Elements'

EXCEPTIONS

OTHERS = 1.

PERFORM GET_CURR_RATE USING TKA01-WAERS SY-DATUM

CHANGING GS_CORATE.

SELECT * FROM TKA02 WHERE KOKRS = P_KOKRS.

ENDSELECT.

REFRESH WBS_TAB.

CLEAR WBS_TAB.

REFRESH CURR_TAB.

Build s_pctr from Profit Center Group.

IF NOT P_PRCTRH IS INITIAL.

PERFORM BUILD_HIERARCHY.

ENDIF.

IF NOT S_PRCTR-LOW IS INITIAL.

IF P_ALL = 'X' OR P_INDIR = 'X'.

SELECT KOSTL PRCTR FROM CSKS INTO TABLE GT_CSKS

WHERE PRCTR IN S_PRCTR AND datbi => sy-datum.

ENDIF.

SELECT * FROM PRPS WHERE PKOKR = P_KOKRS AND PRCTR IN S_PRCTR.

WBS_TAB-INTNO = PRPS-PSPNR.

WBS_TAB-OBJNR = PRPS-OBJNR.

WBS_TAB-PRCTR = PRPS-PRCTR.

WBS_TAB-PSPHI = PRPS-PSPHI.

APPEND WBS_TAB.

ENDSELECT.

SORT WBS_TAB BY INTNO.

IF P_ALL = 'X' OR P_INDIR = 'X'.

SORT GT_CSKS BY KOSTL.

PERFORM PROCESS_MAIN_CC.

ENDIF.

PERFORM PROCESS_MAIN_WBS.

PERFORM OBTAIN_OTHER_DATA.

ELSEIF NOT S_KOSTL IS INITIAL.

CLEAR GT_CSKS.

REFRESH GT_CSKS.

SELECT KOSTL PRCTR FROM CSKS INTO TABLE GT_CSKS

WHERE KOKRS = P_KOKRS

AND KOSTL IN S_KOSTL

AND datbi => sy-datum.

PERFORM PROCESS_MAIN_CC.

PERFORM OBTAIN_OTHER_DATA.

ELSEIF NOT WBS IS INITIAL.

PERFORM BUILD_WBS_HIERARCHY.

SORT WBS_TAB BY INTNO.

PERFORM PROCESS_MAIN_WBS.

PERFORM OBTAIN_OTHER_DATA.

ELSE.

PERFORM PROCESS_MAIN_PO.

PERFORM OBTAIN_OTHER_DATA.

ENDIF.

FORM PROCESS_MAIN_WBS.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Obtaining PO data'

EXCEPTIONS

OTHERS = 1.

DATA: LC_PSPID LIKE PROJ-PSPID.

LOOP AT WBS_TAB.

CLEAR: HOLD_PO_NBR,

HOLD_PO_LINE.

SELECT * FROM EKKN

WHERE PS_PSP_PNR = WBS_TAB-INTNO

AND EBELN IN S_PO

AND KOKRS = P_KOKRS.

CHECK EKKN-LOEKZ <> 'X'.

CLEAR GC_PROFL.

WRITE WBS_TAB-PSPHI TO LC_PSPID.

*select direct projects only if p_direct = 'X'.

IF P_DIRECT = 'X'.

SELECT SINGLE PROFL FROM PROJ INTO GC_PROFL

WHERE PSPID = LC_PSPID.

IF GC_PROFL+4(3) = '002'

OR GC_PROFL+4(3) = '007'.

CONTINUE.

ENDIF.

select indirect projects and cost centers only when p_indir = 'X'.

IF P_INDIR = 'X'.

SELECT SINGLE PROFL FROM PROJ INTO GC_PROFL

WHERE PSPID = LC_PSPID.

IF GC_PROFL+4(3) NE '002'

AND GC_PROFL+4(3) NE '007'.

CONTINUE.

ENDIF.

CLEAR: GC_EINDT,

GC_PRCTR.

GC_PRCTR = WBS_TAB-PRCTR.

PERFORM PROCESS_MAIN2.

ENDSELECT.

ENDLOOP.

ENDFORM.

----

FORM PROCESS_MAIN_PO *

----

........ *

----

FORM PROCESS_MAIN_PO.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Obtaining data via PO nbr'

EXCEPTIONS

OTHERS = 1.

CLEAR: HOLD_PO_NBR, HOLD_PO_LINE.

SELECT * FROM EKKN WHERE EBELN IN S_PO AND KOKRS = P_KOKRS.

CHECK EKKN-LOEKZ <> 'X'.

CLEAR: GC_EINDT,

GC_PRCTR.

GC_PRCTR = EKKN-PRCTR.

PERFORM PROCESS_MAIN2.

ENDSELECT.

ENDFORM.

----

FORM OBTAIN_OTHER_DATA *

----

........ *

----

FORM OBTAIN_OTHER_DATA.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Obtain GR Transactions'

EXCEPTIONS

OTHERS = 1.

SORT ITAB BY PO_NO LINE ZEKKN.

LOOP AT ITAB.

ON CHANGE OF ITAB-PO_NO OR

ITAB-LINE.

PERFORM GET_GR_AMTS_FOR_PO_LINE.

ENDON.

ENDLOOP.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Obtain IR Transactions'

EXCEPTIONS

OTHERS = 1.

SORT GR_TAB BY PO_NO LINE ZEKKN.

LOOP AT ITAB.

PERFORM MOVE_GR_AMTS_TO_ITAB.

PERFORM GET_IR_AMTS_FOR_PO_LINE.

PERFORM FIGURE_COMMITMENTS_COOI.

perform figure_committments.

MODIFY ITAB.

ENDLOOP.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Obtaining Other Data'

EXCEPTIONS

OTHERS = 1.

SORT ITAB BY VEND_NO.

LOOP AT ITAB.

ON CHANGE OF ITAB-VEND_NO.

CLEAR LFA1.

SELECT SINGLE * FROM LFA1 WHERE LIFNR = ITAB-VEND_NO.

ENDON.

ITAB-VEND_NAME = LFA1-NAME1.

MODIFY ITAB.

ENDLOOP.

SORT ITAB BY WBS.

LOOP AT ITAB WHERE NOT WBS IS INITIAL.

ON CHANGE OF ITAB-WBS.

CLEAR: PRPS, PROJ.

SELECT SINGLE * FROM PRPS WHERE PSPNR = ITAB-WBS.

SELECT SINGLE * FROM PROJ WHERE PSPNR = PRPS-PSPHI.

ENDON.

ITAB-OBJ_TITLE = PRPS-POST1.

ITAB-COST_OBJ = PRPS-POSID.

ITAB-PROJ_NBR = PROJ-PSPID.

MODIFY ITAB.

ENDLOOP.

SORT ITAB BY KOSTL.

LOOP AT ITAB WHERE NOT KOSTL IS INITIAL.

ON CHANGE OF ITAB-KOSTL.

CLEAR CSKT.

SELECT * FROM CSKT WHERE KOKRS = P_KOKRS

AND KOSTL = ITAB-KOSTL.

ENDSELECT.

ENDON.

ITAB-OBJ_TITLE = CSKT-KTEXT.

WRITE CSKT-KOSTL TO ITAB-COST_OBJ.

MODIFY ITAB.

ENDLOOP.

SORT ITAB BY AUFNR.

LOOP AT ITAB WHERE NOT AUFNR IS INITIAL.

ON CHANGE OF ITAB-AUFNR.

CLEAR COAS.

SELECT SINGLE * FROM COAS WHERE AUFNR = ITAB-AUFNR.

ENDON.

ITAB-OBJ_TITLE = COAS-KTEXT.

WRITE COAS-AUFNR TO ITAB-COST_OBJ.

MODIFY ITAB.

ENDLOOP.

SORT ITAB BY ANLN1.

LOOP AT ITAB WHERE NOT ANLN1 IS INITIAL.

ON CHANGE OF ITAB-ANLN1.

CLEAR ANLA.

SELECT * FROM ANLA WHERE BUKRS = TKA02-BUKRS

AND ANLN1 = ITAB-ANLN1.

ENDSELECT.

ENDON.

ITAB-OBJ_TITLE = ANLA-TXT50.

WRITE ANLA-ANLN1 TO ITAB-COST_OBJ.

MODIFY ITAB.

ENDLOOP.

LOOP AT ITAB.

MOVE-CORRESPONDING ITAB TO SUM_TAB.

COLLECT SUM_TAB.

ENDLOOP.

ENDFORM.

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

FUNCTION: PROCESS_MAIN2 *
DESCRIPTION: *

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

FORM PROCESS_MAIN2.

SELECT SINGLE * FROM EKKO WHERE EBELN = EKKN-EBELN.

CHECK EKKO-LIFNR IN VEND_NO.

CHECK EKKO-AEDAT IN S_POCDAT.

SELECT SINGLE * FROM EKPO WHERE EBELN = EKKN-EBELN AND

EBELP = EKKN-EBELP.

CHECK EKPO-LOEKZ <> 'L' AND

EKPO-LOEKZ <> 'S'.

SELECT SINGLE EINDT FROM EKET INTO GC_EINDT

WHERE EBELN = EKPO-EBELN

AND EBELP = EKPO-EBELP

AND EINDT IN S_EINDT.

IF NOT S_EINDT IS INITIAL.

CHECK GC_EINDT IN S_EINDT.

ENDIF.

CLEAR ITAB.

ITAB-PO_CREATE = EKKO-AEDAT.

ITAB-DEL_DAT = GC_EINDT.

ITAB-PO_NO = EKKO-EBELN.

ITAB-CURR_PO = EKKO-WAERS.

ITAB-CURR_CO = TKA01-WAERS.

ITAB-LINE = EKPO-EBELP.

ITAB-GR_SW = EKPO-WEPOS.

ITAB-GR_NON_VAL_SW = EKPO-WEUNB.

IF EKPO-WEUNB = 'X'. "Goods Receipt, Non-Value

ITAB-GR_SW = ''. "switch evaluation of commitment to IR

ENDIF.

ITAB-TEXT = EKPO-TXZ01.

ITAB-QTY_ORD = EKPO-MENGE.

ITAB-UOM = EKPO-MEINS.

ITAB-AMOUNT_DOC = EKPO-EFFWR.

ITAB-WBS = EKKN-PS_PSP_PNR.

ITAB-COST_ELEM = EKKN-SAKTO.

ITAB-KOSTL = EKKN-KOSTL.

ITAB-PRCTR = GC_PRCTR.

ITAB-ANLN1 = EKKN-ANLN1.

ITAB-AUFNR = EKKN-AUFNR.

ITAB-ZEKKN = EKKN-ZEKKN.

IF EKKN-VPROZ <> 0.

PERCENT = EKKN-MENGE / EKPO-MENGE.

ITAB-AMOUNT_DOC = ITAB-AMOUNT_DOC * PERCENT .

ITAB-QTY_ORD = ITAB-QTY_ORD * PERCENT .

ENDIF.

IF ITAB-CURR_CO NE ITAB-CURR_PO.

itab-amount_co = itab-amount_doc * ekko-wkurs.
Convert from PO curr to CO currency

GS_CONV_AMT = ITAB-AMOUNT_DOC / GS_CORATE.

PERFORM GET_CURR_RATE USING ITAB-CURR_PO EKKO-AEDAT

CHANGING GS_RATE.

ITAB-AMOUNT_CO = GS_CONV_AMT * GS_RATE.

ELSE.

ITAB-AMOUNT_CO = ITAB-AMOUNT_DOC.

ENDIF.

Convert from PO curr to CO Code currency

CLEAR: ITAB-CURR_LOC, GS_RATE.

PERFORM GET_COCD_RATE USING EKKO-BUKRS

CHANGING ITAB-CURR_LOC GS_RATE.

IF ITAB-CURR_LOC NE ITAB-CURR_PO.

Get company code/local currency and rate.

GS_CONV_AMT = ITAB-AMOUNT_DOC / GS_RATE.

PERFORM GET_CURR_RATE USING ITAB-CURR_PO ITAB-PO_CREATE

CHANGING GS_RATE.

ITAB-AMOUNT_LOC = GS_CONV_AMT * GS_RATE.

ELSE.

ITAB-AMOUNT_LOC = ITAB-AMOUNT_DOC.

ENDIF.

ITAB-VEND_NO = EKKO-LIFNR.

if itab-amount_doc <> 0.

APPEND ITAB.

CLEAR ITAB.

endif.
clear itab.

ENDFORM.

FORM PROCESS_MAIN_CC.

CALL FUNCTION 'SAPGUI_PROGRESS_INDICATOR'

EXPORTING

TEXT = 'Obtaining PO data'

EXCEPTIONS

OTHERS = 1.

LOOP AT GT_CSKS.

SELECT * FROM EKKN

WHERE KOSTL = GT_CSKS-KOSTL

AND EBELN IN S_PO

AND KOKRS = P_KOKRS.

CHECK EKKN-LOEKZ <> 'X'.

CLEAR: GC_EINDT,

GC_PRCTR.

GC_PRCTR = GT_CSKS-PRCTR.

PERFORM PROCESS_MAIN2.

ENDSELECT.

ENDLOOP.

ENDFORM.

Edited by: Subramanyam Sesetty on May 9, 2008 11:13 AM

Former Member · ‎2008 May 09

Hi,

i think u can avoid select * statement also instead of that use

select F1 F2 F3

from xxxx

into itab

where.......

...........

If u have to get 70% of the fields from a table then only u should use select* . if u have to retrieve only 5 to 6 fields from table then specify only those fields.

hope it will help u.

Regards,

kk.

Former Member · ‎2008 May 09

Hi,

u have avoidable SELECT-ENDSELECTs(some in Loop too) in ur code. why dont u popualte into to an internal table instead of populating the data into work area and then appending into the table.

say like this:

SELECT * FROM PRPS WHERE PKOKR = P_KOKRS AND PRCTR IN S_PRCTR.

WBS_TAB-INTNO = PRPS-PSPNR.

WBS_TAB-OBJNR = PRPS-OBJNR.

WBS_TAB-PRCTR = PRPS-PRCTR.

WBS_TAB-PSPHI = PRPS-PSPHI.

APPEND WBS_TAB.

ENDSELECT.

directly populate into internal table.

regards,

madhumitha

Former Member · ‎2008 May 09

Yes i did that, is there any other things....

Former Member · ‎2008 May 09

Hi,

Have u removed the selects from the loop? u can instead read the table inside the LOOP. As kusuma said, u can avoid select *.

In the code below, u can actually populate the sum_tab in the first loop itself. try to combine the subsequent loops, if possible.

Ex:

LOOP AT ITAB WHERE NOT ANLN1 IS INITIAL. 
ON CHANGE OF ITAB-ANLN1. 
CLEAR ANLA. 
SELECT * FROM ANLA WHERE BUKRS = TKA02-BUKRS 
AND ANLN1 = ITAB-ANLN1. 
ENDSELECT. 
ENDON. 
ITAB-OBJ_TITLE = ANLA-TXT50. 
WRITE ANLA-ANLN1 TO ITAB-COST_OBJ. 
MODIFY ITAB. 
ENDLOOP. 

LOOP AT ITAB.
MOVE-CORRESPONDING ITAB TO SUM_TAB.
COLLECT SUM_TAB.
ENDLOOP.

regards,

madhumitha

Former Member · ‎2008 May 10

Hi

Avoid select-endselect.

selects widin loops

try using select single *.....

slect all entries..

for more info refer the following links if will get good performance tuning hwlp especially for select queries

http://www.erpgenie.com/abap/performance.htm

kindly reward points if helpful.

Former Member · ‎2008 May 09

Hi,

i think u can avoid select * statement also instead of that use

select F1 F2 F3

from xxxx

into itab

where.......

...........

If u have to get 70% of the fields from a table then only u should use select* . if u have to retrieve only 5 to 6 fields from table then specify only those fields.

hope it will help u.

Regards,

kk.

By Category

Related Content

Activity Groups

Industry Groups

Influence and Feedback Groups

Interest Groups

Location Groups

Customer Only Groups

Forums

Related Resources

Products

Learning and Support

About

My SAP Profile

My SAP Profile

Performance improvement