topic Re: Delay in report execution in Application Development and Automation Discussions

Delay in report execution

Former Member — Fri, 03 Aug 2007 10:26:51 GMT

Hello all,

i have written following code, it take so much time in execution , any tips so that report executes in less execution time:

REPORT ZHR_COURSES_AI .

TABLES:HRP1000,

HRP1001,

HRP1002,

HRP1021,

HRP1026,

HRP1035,

HRT1002.

type-pools: slis.

*Data Declaration

*----

TYPES: BEGIN OF CTAB,

COURSE_TYPE TYPE HRP1000-STEXT,

TRAINING_CATEGORY TYPE HRP1000-SHORT,

TRAINING_CATEGORY_DESC TYPE HRP1000-STEXT,

COURSE_TITLE TYPE HRP1000-STEXT,

LOCATION TYPE HRP1000-STEXT,

DURATION TYPE HRP1035-NDAYS,

EXTERNAL_FEE TYPE HRP1021-EKOST,

INTERNAL_FEE TYPE HRP1021-IKOST,

SAPID TYPE HRP1000-OBJID,

COURSES_CONTENTS TYPE HRT1002-TLINE,

END OF CTAB.

DATA: ATAB TYPE STANDARD TABLE OF CTAB INITIAL SIZE 0 WITH HEADER LINE,

DTAB TYPE CTAB.

DATA: BEGIN OF BTAB OCCURS 10,

SAPID LIKE HRP1000-OBJID,

END OF BTAB.

DATA: TEMP LIKE HRP1000-OBJID,

TEMP1 LIKE HRP1002-TABNR,

TEMP2 LIKE HRP1001-OTJID.

*ALV data declarations

data: fieldcatalog type slis_t_fieldcat_alv with header line,

gd_tab_group type slis_t_sp_group_alv,

gd_layout type slis_layout_alv,

gd_repid like sy-repid.

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

*Start-of-selection.

START-OF-SELECTION.

perform data_retrieval.

perform build_fieldcatalog.

perform build_layout.

perform display_alv_report.

&----

*& Form BUILD_FIELDCATALOG

&----

Build Fieldcatalog for ALV Report

----

form build_fieldcatalog.

There are a number of ways to create a fieldcat.
For the purpose of this example i will build the fieldcatalog manualy
by populating the internal table fields individually and then
appending the rows. This method can be the most time consuming but can
also allow you more control of the final product.

Beware though, you need to ensure that all fields required are
populated. When using some of functionality available via ALV, such as
total. You may need to provide more information than if you were
simply displaying the result
I.e. Field type may be required in-order for
the 'TOTAL' function to work.

fieldcatalog-fieldname = 'COURSE_TYPE'.

fieldcatalog-seltext_m = 'COURSE_TYPE'.

fieldcatalog-col_pos = 0.

fieldcatalog-outputlen = 10.

fieldcatalog-emphasize = 'X'.

fieldcatalog-key = 'X'.

fieldcatalog-do_sum = 'X'.
fieldcatalog-no_zero = 'X'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'TRAINING_CATEGORY'.

fieldcatalog-seltext_m = 'TRAINING_CATEGORY'.

fieldcatalog-col_pos = 1.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'TRAINING_CATEGORY_DESC'.

fieldcatalog-seltext_m = 'TRAINING_CATEGORY_DESC'.

fieldcatalog-col_pos = 2.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'COURSE_TITLE'.

fieldcatalog-seltext_m = 'COURSE_TITLE'.

fieldcatalog-col_pos = 3.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'LOCATION'.

fieldcatalog-seltext_m = 'LOCATION'.

fieldcatalog-col_pos = 4.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'DURATION'.

fieldcatalog-seltext_m = 'DURATION'.

fieldcatalog-col_pos = 5.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'EXTERNAL_FEE'.

fieldcatalog-seltext_m = 'EXTERNAL_FEE'.

fieldcatalog-col_pos = 6.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'INTERNAL_FEE'.

fieldcatalog-seltext_m = 'INTERNAL_FEE'.

fieldcatalog-col_pos = 7.

fieldcatalog-outputlen = 15.

fieldcatalog-do_sum = 'X'. "Display column total

fieldcatalog-datatype = 'CURR'.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'SAPID'.

fieldcatalog-seltext_m = 'SAPID'.

fieldcatalog-col_pos = 8.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

fieldcatalog-fieldname = 'COURSES_CONTENTS'.

fieldcatalog-seltext_m = 'COURSES_CONTENTS'.

fieldcatalog-col_pos = 9.

append fieldcatalog to fieldcatalog.

clear fieldcatalog.

endform. " BUILD_FIELDCATALOG

&----

*& Form BUILD_LAYOUT

&----

Build layout for ALV grid report

----

form build_layout.

gd_layout-no_input = 'X'.

gd_layout-colwidth_optimize = 'X'.

gd_layout-totals_text = 'Totals'(201).

gd_layout-totals_only = 'X'.
gd_layout-f2code = 'DISP'. "Sets fcode for when double
"click(press f2)
gd_layout-zebra = 'X'.
gd_layout-group_change_edit = 'X'.
gd_layout-header_text = 'helllllo'.

endform. " BUILD_LAYOUT

&----

*& Form DISPLAY_ALV_REPORT

&----

Display report using ALV grid

----

form display_alv_report.

gd_repid = sy-repid.

call function 'REUSE_ALV_GRID_DISPLAY'

exporting

i_callback_program = gd_repid

i_callback_top_of_page = 'TOP-OF-PAGE' "see FORM
i_callback_user_command = 'USER_COMMAND'
i_grid_title = outtext

is_layout = gd_layout

it_fieldcat = fieldcatalog[]

it_special_groups = gd_tabgroup
IT_EVENTS = GT_XEVENTS

i_save = 'X'

is_variant = z_template

tables

t_outtab = ATAB

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. " DISPLAY_ALV_REPORT

&----

*& Form DATA_RETRIEVAL

&----

Retrieve data form EKPO table and populate itab it_ekko

----

form data_retrieval.

SELECT * FROM HRP1035.

BTAB-SAPID = HRP1035-OBJID.

APPEND BTAB.

ENDSELECT.

LOOP AT BTAB.

For Course Type

SELECT * FROM HRP1026 WHERE OBJID = BTAB-SAPID.

ATAB-SAPID = BTAB-SAPID.

ATAB-COURSE_TYPE = HRP1026-EXTRN.

IF ATAB-COURSE_TYPE = 'X'.

ATAB-COURSE_TYPE = 'External'.

Else.

ATAB-COURSE_TYPE = 'Internal'.

ENDIF.

ENDSELECT.

For Training Category

SELECT * FROM HRP1001 WHERE OBJID = BTAB-SAPID AND RELAT = '020'.

TEMP = HRP1001-SOBID.

ENDSELECT.

SELECT * FROM HRP1000 WHERE OBJID = TEMP AND OTYPE = 'D'.

ATAB-TRAINING_CATEGORY = HRP1000-SHORT.

ATAB-TRAINING_CATEGORY_DESC = HRP1000-STEXT.

ENDSELECT.

*For Course Title

SELECT * FROM HRP1000 WHERE OBJID = BTAB-SAPID AND OTYPE = 'E'.

ATAB-COURSE_TITLE = HRP1000-STEXT.

ENDSELECT.

For Location

SELECT * FROM HRP1001 WHERE OBJID = BTAB-SAPID AND RELAT = '024'.

TEMP = HRP1001-SOBID.

ENDSELECT.

SELECT * FROM HRP1000 WHERE OBJID = TEMP.

ATAB-LOCATION = HRP1000-STEXT.

ENDSELECT.

*For Duration

SELECT * FROM HRP1035 WHERE OBJID = BTAB-SAPID.

ATAB-DURATION = HRP1035-NDAYS.

ENDSELECT.

*For Fee

SELECT * FROM HRP1021 WHERE OBJID = BTAB-SAPID.

ATAB-EXTERNAL_FEE = HRP1021-EKOST.

ATAB-INTERNAL_FEE = HRP1021-IKOST.

ENDSELECT.

For Course Contents

SELECT * FROM HRP1002 WHERE OBJID = BTAB-SAPID.

TEMP1 = HRP1002-TABNR.

ENDSELECT.

SELECT * FROM HRT1002 WHERE TABNR = TEMP1.

ATAB-COURSES_CONTENTS = HRT1002-TLINE.

ENDSELECT.

APPEND ATAB.

ENDLOOP.

*LOOP AT ATAB.

WRITE:/ ATAB-COURSE_TYPE,ATAB-TRAINING_CATEGORY.
WRITE:ATAB-TRAINING_CATEGORY_DESC,ATAB-COURSE_TITLE.
WRITE:ATAB-LOCATION,ATAB-DURATION,ATAB-EXTERNAL_FEE.
WRITE:ATAB-INTERNAL_FEE,ATAB-SAPID,ATAB-COURSES_CONTENTS.

*ENDLOOP.

ENDFORM.

Re: Delay in report execution

seshatalpasai_madala — Fri, 03 Aug 2007 10:31:05 GMT

Hi,

Check code in BOLD.

Avoid SELECT... ENDSELECT,

instead use SELECT INTO TABLE itab. and use

LOOP AT ITAB

ENDLOOP.

USE

<b>SELECT OBJID AS SAPID FROM HRP1035 INTO CORRESPODNING FIELDS OF TABLE BTAB.</b>

INSTEAD OF

SELECT * FROM HRP1035.

BTAB-SAPID = HRP1035-OBJID.

APPEND BTAB.

ENDSELECT.

Regards,

Sesh

Re: Delay in report execution

Former Member — Fri, 03 Aug 2007 10:32:11 GMT

Hi,

Use select into table instead of select...endselect

Never use select statement within a loop, alternatively use For all entries.

Pls assign pts if useful

Regards,

Shruthi

Re: Delay in report execution

Former Member — Fri, 03 Aug 2007 10:36:22 GMT

SELECT * FROM HRP1035.

BTAB-SAPID = HRP1035-OBJID.

APPEND BTAB.

ENDSELECT.

for the above one use this one

select objid from hrp1035 into table btab.

declare sapid like HRP1035-OBJID

SELECT * FROM HRP1001 WHERE OBJID = BTAB-SAPID AND RELAT = '020'.

TEMP = HRP1001-SOBID.

ENDSELECT.

if btab[] is not initial.

select sobid into table temp for all entries in btab where OBJID = BTAB-SAPID AND RELAT = '020'.

REWARD POINTS FOR HELPFUL ANSWERS,

bEST rEGDS,

KIRAN.M

Re: Delay in report execution

Former Member — Fri, 03 Aug 2007 10:36:46 GMT

your useing slect and endselect

it is taking so much time to retrive the data

SELECT * FROM HRP1035.

BTAB-SAPID = HRP1035-OBJID.

APPEND BTAB.

ENDSELECT.

SELECT * FROM HRP1036

INTO SOME INTERNAL TABLE

WHERE BTAB-SAPID = HRP1036-OBJID

APPEND BTAB

SO IT WILL EXECUTE SOME WHAT FAST THEN PREVIOUS

REWARSD IF USE FULL

Re: Delay in report execution

Former Member — Fri, 03 Aug 2007 10:36:52 GMT

Hi,

donot declare the

tables: statement as it will occupy more memory

which also decreases the execution speed..

avoid tables statement

and avoid the select and endselect statements

as this is becoming a loop for the data base

ie it will the data base for n number of times on a same table

so instead use the

into table or atleast into corresponding

but recommended is into table only..

and

declare the internal tables seperately for each select st

and then modify the internal table or else use the field symbols

to modify the internal tables

instead of using the select inside a loop

better go for all entries..

reward points if helpful,

thanks & regards,

venkatesh

Re: Delay in report execution

Former Member — Fri, 03 Aug 2007 10:38:22 GMT

And do similar way for all select statements

things to remember

1. dont use select *

2. select and end select use - > for all entries

Reward points for helpful answers.

Thanks,

Kiran.M

Re: Delay in report execution

Former Member — Fri, 03 Aug 2007 10:51:47 GMT

please go through the select query method s ..

change your all select queries ...

you are using select ...endselect it is not adviceable ...

for each one data it will go data base gets data and again it does as many data retreivals.

so you have to write the select query in such a way to get all the data @ one stretch into your internal table ..

i am giving you some select query types :

" *Select directly into an internal table

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO TABLE it_bseg.

" * Select directly into an internal table where fields are in a
* different order or not all fields are specified

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO CORRESPONDING FIELDS OF TABLE it_bseg.

" *Select... endselect command

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO wa_bseg.

APPEND wa_bseg TO it_bseg.

ENDSELECT.

"*Select FOR ALL ENTRIES command

SELECT bukrs belnr gjahr bldat monat budat xblnr awtyp awkey

UP TO 100 ROWS

FROM bkpf

INTO TABLE it_bkpf.

* The FOR ALL ENTRIES comand only retrieves data which matches
* entries within a particular internal table.

SELECT bukrs belnr gjahr buzei mwskz umsks prctr hkont xauto koart

dmbtr mwart hwbas aufnr projk shkzg kokrs

FROM bseg

INTO TABLE it_bseg

FOR ALL ENTRIES IN it_bkpf

WHERE bukrs EQ it_bkpf-bukrs AND

belnr EQ it_bkpf-belnr AND

gjahr EQ it_bkpf-gjahr.

ENDIF.

Reward points if it is usefull .....

Girish