How to reduce execution time ?

[Former Member]

Hi friends...

I have created a report to display vendor opening balances,

total debit ,total credit , total balance & closing balance for the given date range. it is working fine .But it takes more time to execute . How can I reduce execution time ?

Plz help me. It's a very urgent report...

The coding is as below.....

report yfiin_rep_vendordetail no standard page heading.

tables : bsik,bsak,lfb1,lfa1.

type-pools : slis .

--

---

TABLE STRUCTURE--

---

types : begin of tt_bsik,

bukrs type bukrs,

lifnr type lifnr,

budat type budat,

augdt type augdt,

dmbtr type dmbtr,

wrbtr type wrbtr,

shkzg type shkzg,

hkont type hkont,

bstat type bstat_d ,

prctr type prctr,

name1 type name1,

end of tt_bsik,

begin of tt_lfb1,

lifnr type lifnr,

mindk type mindk,

end of tt_lfb1,

begin of tt_lfa1,

lifnr type lifnr,

name1 type name1,

ktokk type ktokk,

end of tt_lfa1,

begin of tt_opbal,

bukrs type bukrs,

lifnr type lifnr,

gjahr type gjahr,

belnr type belnr_d,

budat type budat,

bldat type bldat,

waers type waers,

dmbtr type dmbtr,

wrbtr type wrbtr,

shkzg type shkzg,

blart type blart,

monat type monat,

hkont type hkont,

bstat type bstat_d ,

prctr type prctr,

name1 type name1,

tdr type dmbtr,

tcr type dmbtr,

tbal type dmbtr,

end of tt_opbal,

begin of tt_bs ,

bukrs type bukrs,

lifnr type lifnr,

name1 type name1,

prctr type prctr,

tbal type dmbtr,

bala type dmbtr,

balb type dmbtr,

balc type dmbtr,

bald type dmbtr,

bale type dmbtr,

gbal type dmbtr,

end of tt_bs.

************WORK AREA DECLARATION *********************

data : gs_bsik type tt_bsik,

gs_bsak type tt_bsik,

gs_lfb1 type tt_lfb1,

gs_lfa1 type tt_lfa1,

gs_ageing type tt_ageing,

gs_bs type tt_bs,

gs_opdisp type tt_bs,

gs_final type tt_bsik,

gs_opbal type tt_opbal,

gs_opfinal type tt_opbal.

************INTERNAL TABLE DECLARATION*************

data : gt_bsik type standard table of tt_bsik,

gt_bsak type standard table of tt_bsik,

gt_lfb1 type standard table of tt_lfb1,

gt_lfa1 type standard table of tt_lfa1,

gt_ageing type standard table of tt_ageing,

gt_bs type standard table of tt_bs,

gt_opdisp type standard table of tt_bs,

gt_final type standard table of tt_bsik,

gt_opbal type standard table of tt_opbal,

gt_opfinal type standard table of tt_opbal.

• ALV DECLARATIONS *******************

data : gs_fcat type slis_fieldcat_alv ,

gt_fcat type slis_t_fieldcat_alv ,

gs_sort type slis_sortinfo_alv,

gs_fcats type slis_fieldcat_alv ,

gt_fcats type slis_t_fieldcat_alv.

**********global data declration***************

data : kb type dmbtr ,

return like bapireturn ,

balancespgli like bapi3008-bal_sglind,

noteditems like bapi3008-ntditms_rq,

keybalance type table of bapi3008_3 with header line,

opbalance type p.

• SELECTION SCREEN DECLARATIONS *********************

selection-screen begin of block b1 with frame .

select-options : so_bukrs for bsik-bukrs obligatory,

so_lifnr for bsik-lifnr,

so_hkont for bsik-hkont,

so_prctr for bsik-prctr ,

so_mindk for lfb1-mindk,

so_ktokk for lfa1-ktokk.

selection-screen end of block b1.

selection-screen : begin of block b1 with frame.

parameters : p_rb1 radiobutton group rad1 .

select-options : so_date for sy-datum .

selection-screen : end of block b1.

********************************ASSIGNING ALV GRID

****field catalog for balance report

gs_fcats-col_pos = 1.

gs_fcats-fieldname = 'BUKRS'.

gs_fcats-seltext_m = text-001.

append gs_fcats to gt_fcats .

gs_fcats-col_pos = 2 .

gs_fcats-fieldname = 'LIFNR'.

gs_fcats-seltext_m = text-002.

append gs_fcats to gt_fcats .

gs_fcats-col_pos = 3.

gs_fcats-fieldname = 'NAME1'.

gs_fcats-seltext_m = text-003.

append gs_fcats to gt_fcats .

gs_fcats-col_pos = 4.

gs_fcats-fieldname = 'BALC'.

gs_fcats-seltext_m = text-016.

append gs_fcats to gt_fcats .

gs_fcats-col_pos = 5.

gs_fcats-fieldname = 'BALA'.

gs_fcats-seltext_m = text-012.

append gs_fcats to gt_fcats .

gs_fcats-col_pos = 6.

gs_fcats-fieldname = 'BALB'.

gs_fcats-seltext_m = text-013.

append gs_fcats to gt_fcats .

gs_fcats-col_pos = 7.

gs_fcats-fieldname = 'TBAL'.

gs_fcats-seltext_m = text-014.

append gs_fcats to gt_fcats .

gs_fcats-col_pos = 8.

gs_fcats-fieldname = 'GBAL'.

gs_fcats-seltext_m = text-015.

append gs_fcats to gt_fcats .

data : repid1 type sy-repid.

repid1 = sy-repid.

• INITIALIZATION EVENTS ******************************

initialization.

*Clearing the work area.

clear gs_bsik.

• Refreshing the internal tables.

refresh gt_bsik.

******************START OF SELECTION EVENTS **************************

start-of-selection.

*get data for balance report.

perform sub_openbal.

perform sub_openbal_display.

&----

---

*& Form sub_openbal

&----

---

• text

----

---

• --> p1 text

• <-- p2 text

----

---

form sub_openbal .

if so_date-low > sy-datum or so_date-high > sy-datum .

message i005(yfi02).

leave screen.

endif.

select bukrs lifnr gjahr belnr budat bldat

waers dmbtr wrbtr shkzg blart monat hkont prctr

from bsik into table gt_opbal

where bukrs in so_bukrs and lifnr in so_lifnr

and hkont in so_hkont and prctr in so_prctr

and budat in so_date .

select bukrs lifnr gjahr belnr budat bldat

waers dmbtr wrbtr shkzg blart monat hkont prctr

from bsak appending table gt_opbal

for all entries in gt_opbal

where lifnr = gt_opbal-lifnr

and budat in so_date .

if sy-subrc <> 0.

message i007(yfi02).

leave screen.

endif.

select lifnr mindk from lfb1 into table gt_lfb1

for all entries in gt_opbal

where lifnr = gt_opbal-lifnr and mindk in so_mindk.

select lifnr name1 ktokk from lfa1 into table gt_lfa1

for all entries in gt_opbal

where lifnr = gt_opbal-lifnr and ktokk in so_ktokk.

loop at gt_opbal into gs_opbal .

loop at gt_lfb1 into gs_lfb1 where lifnr = gs_opbal-lifnr.

loop at gt_lfa1 into gs_lfa1 where lifnr = gs_opbal-lifnr.

gs_opfinal-bukrs = gs_opbal-bukrs.

gs_opfinal-lifnr = gs_opbal-lifnr.

gs_opfinal-gjahr = gs_opbal-gjahr.

gs_opfinal-belnr = gs_opbal-belnr.

gs_opfinal-budat = gs_opbal-budat.

gs_opfinal-bldat = gs_opbal-bldat.

gs_opfinal-waers = gs_opbal-waers.

gs_opfinal-dmbtr = gs_opbal-dmbtr.

gs_opfinal-wrbtr = gs_opbal-wrbtr.

gs_opfinal-shkzg = gs_opbal-shkzg.

gs_opfinal-blart = gs_opbal-blart.

gs_opfinal-monat = gs_opbal-monat.

gs_opfinal-hkont = gs_opbal-hkont.

gs_opfinal-prctr = gs_opbal-prctr.

gs_opfinal-name1 = gs_lfa1-name1.

if gs_opbal-shkzg = 'H'.

gs_opfinal-tcr = gs_opbal-dmbtr * -1.

gs_opfinal-tdr = '000000'.

else.

gs_opfinal-tdr = gs_opbal-dmbtr.

gs_opfinal-tcr = '000000'.

endif.

append gs_opfinal to gt_opfinal.

endloop.

endloop.

endloop.

sort gt_opfinal by bukrs lifnr prctr .

so_date-low = so_date-low - 1 .

loop at gt_opfinal into gs_opfinal.

call function 'BAPI_AP_ACC_GETKEYDATEBALANCE'

exporting

companycode = gs_opfinal-bukrs

vendor = gs_opfinal-lifnr

keydate = so_date-low

balancespgli = ' '

noteditems = ' '

importing

return = return

tables

keybalance = keybalance.

clear kb .

loop at keybalance .

kb = keybalance-lc_bal + kb .

endloop.

gs_opdisp-balc = kb.

gs_opdisp-bukrs = gs_opfinal-bukrs.

gs_opdisp-lifnr = gs_opfinal-lifnr.

gs_opdisp-name1 = gs_opfinal-name1.

at new lifnr .

sum .

gs_opfinal-tbal = gs_opfinal-tdr + gs_opfinal-tcr .

gs_opdisp-tbal = gs_opfinal-tbal.

gs_opdisp-bala = gs_opfinal-tdr .

gs_opdisp-balb = gs_opfinal-tcr .

gs_opdisp-gbal = keybalance-lc_bal + gs_opfinal-tbal .

append gs_opdisp to gt_opdisp.

endat.

clear gs_opdisp.

clear keybalance .

endloop.

delete adjacent duplicates from gt_opdisp.

endform. " sub_openbal

*&----

---

*& Form sub_openbal_display

*&----

---

• text

*----

---

• --> p1 text

• <-- p2 text

*----

---

form sub_openbal_display .

call function 'REUSE_ALV_GRID_DISPLAY'

exporting

• I_INTERFACE_CHECK = ' '

• I_BYPASSING_BUFFER = ' '

• I_BUFFER_ACTIVE = ' '

i_callback_program = repid1

• I_CALLBACK_PF_STATUS_SET = ' '

• I_CALLBACK_USER_COMMAND = ' '

• I_CALLBACK_TOP_OF_PAGE = ' '

• I_CALLBACK_HTML_TOP_OF_PAGE = ' '

• I_CALLBACK_HTML_END_OF_LIST = ' '

• I_STRUCTURE_NAME =

• I_BACKGROUND_ID = ' '

• I_GRID_TITLE =

• I_GRID_SETTINGS =

• IS_LAYOUT =

it_fieldcat = gt_fcats

• IT_EXCLUDING =

• IT_SPECIAL_GROUPS =

• IT_SORT =

• IT_FILTER =

• IS_SEL_HIDE =

• I_DEFAULT = 'X'

• I_SAVE = 'X'

• IS_VARIANT =

• it_events =

• IT_EVENT_EXIT =

• IS_PRINT =

• IS_REPREP_ID =

• I_SCREEN_START_COLUMN = 0

• I_SCREEN_START_LINE = 0

• I_SCREEN_END_COLUMN = 0

• I_SCREEN_END_LINE = 0

• IT_ALV_GRAPHICS =

• IT_HYPERLINK =

• IT_ADD_FIELDCAT =

• IT_EXCEPT_QINFO =

• I_HTML_HEIGHT_TOP =

• I_HTML_HEIGHT_END =

• IMPORTING

• E_EXIT_CAUSED_BY_CALLER =

• ES_EXIT_CAUSED_BY_USER =

tables

t_outtab = gt_opdisp

exceptions

program_error = 1

others = 2

.

if sy-subrc <> 0.

message id sy-msgid type sy-msgty number sy-msgno

with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.

endif.

endform. " sub_openbal_display

[former_member191735]

I think you are using for all entries statement in almost all select statements but i didnt see any condtion before you are using for all entries statement.

If you are using for all entries in gt_opbal ... make sure that gt_opbal has some records other wise it will try to read all records from the data base tables.

Try to check before using for all entries in the select statement like

if gt_opbal is not initial.

select adfda adfadf afdadf into table

for all entries in gt_opbal.

else.

select abdf afad into table

from abcd

where a = 1

and b = 2.

endif.

i didnt see anything wrong in your report but this is major time consuming when you dont have records in the table which you are using for all entries.

[Former Member]

While writing select statement try to use all the primary key in where part also mantain the order of field same as the database table. For all entries also please compare all primary keys for fetching data