resolve performance issue / performance tuning

Former Member — Thu, 22 Dec 2016 20:35:42 GMT

Please review the code and let me know how can I increase the performance of this program.

At selection screen there are three option

Select 1000 records
Select 5000 records
Select all records

I have tried to make the changes still performance is not updated yet

DATA: gt_user TYPE TABLE OF usr02 WITH HEADER LINE,
gt_user_role TYPE TABLE OF agr_users WITH HEADER LINE,

********** DO NOT CHANGE THIS SECTION **********
g_begintime TYPE i,
g_endtime TYPE i,
g_tottime TYPE i.

DATA: BEGIN OF gt_output OCCURS 0,
bname LIKE usr02-bname,
name_text LIKE adrp-name_text,
agr_name LIKE agr_users-agr_name,
text LIKE agr_texts-text,
role_count TYPE i,
END OF gt_output.

TYPE-POOLS: slis.
DATA: l_number TYPE i.
SELECTION-SCREEN: BEGIN OF BLOCK sw WITH FRAME TITLE text-000.

PARAMETERS:p_val_1 RADIOBUTTON GROUP a,
p_val_2 RADIOBUTTON GROUP a,
p_val_3 RADIOBUTTON GROUP a.
SELECTION-SCREEN ULINE.
PARAMETERS:p_demo RADIOBUTTON GROUP a.
SELECTION-SCREEN: END OF BLOCK sw.

SELECTION-SCREEN BEGIN OF LINE.
SELECTION-SCREEN PUSHBUTTON 01(25) as_f1 USER-COMMAND as_dtl.
SELECTION-SCREEN END OF LINE.

INITIALIZATION.
PERFORM set_info.

AT SELECTION-SCREEN.
IF sy-ucomm EQ 'AS_DTL'.
PERFORM asignment_details USING 'Z_ASIGNMENT_01'.
ENDIF.

START-OF-SELECTION.
IF p_demo EQ 'X'.
PERFORM required_output.
ELSE.
PERFORM asignment_test.
ENDIF.

PERFORM output.
********** END NO CHANGE **********

*&---------------------------------------------------------------------*
*& Form combine_tables
*&---------------------------------------------------------------------*
* Combine user and role tables
*----------------------------------------------------------------------*
FORM combine_tables.
DATA: ls_usr21 TYPE usr21,
ls_adrp TYPE adrp,
ls_texts TYPE agr_texts.

LOOP AT gt_user.
* Get user name
SELECT * INTO ls_usr21 FROM usr21 WHERE bname = gt_user-bname.
SELECT * INTO ls_adrp FROM adrp
WHERE persnumber = ls_usr21-persnumber.

gt_output-name_text = ls_adrp-name_text.
ENDSELECT.
ENDSELECT.

LOOP AT gt_user_role WHERE uname = gt_user-bname.
* Only process roles that are currently active
CHECK gt_user_role-from_dat <= sy-datum
AND gt_user_role-to_dat >= sy-datum.

* Get role text
SELECT * INTO ls_texts FROM agr_texts
WHERE agr_name = gt_output-agr_name
AND spras = sy-langu
AND line = 0.

gt_output-text = ls_texts-text.
ENDSELECT.

gt_output-bname = gt_user-bname.
gt_output-agr_name = gt_user_role-agr_name.
APPEND gt_output.
ENDLOOP.
ENDLOOP.
ENDFORM. " combine_tables

*&---------------------------------------------------------------------*
*& Form get_addl_data
*&---------------------------------------------------------------------*
* Get extra fields
*----------------------------------------------------------------------*
FORM get_addl_data.
DATA: l_count TYPE i.

* Count number of roles per user
SORT gt_user_role BY uname.
LOOP AT gt_user_role.
** ADD 1 TO l_count. "old code commented

AT END OF uname.
** LOOP AT gt_output WHERE bname = gt_user_role-uname. "old code
READ TABLE gt_output WITH KEY bname = gt_user_role-uname.
"code change new code added here
ADD 1 TO l_count. "code change new code added here
gt_output-role_count = l_count.
MODIFY gt_output.
** CLEAR l_count. "old code commented
* ENDLOOP.
ENDAT.
CLEAR l_count. "code change new code added here
ENDLOOP.
ENDFORM. " get_addl_data

*&---------------------------------------------------------------------*
*& Form output
*&---------------------------------------------------------------------*
* Output report
*----------------------------------------------------------------------*
FORM output.
DATA: lt_fieldcat TYPE slis_t_fieldcat_alv,
ls_fieldcat TYPE slis_fieldcat_alv,
l_repid LIKE sy-repid,
ls_layout TYPE slis_layout_alv,
ls_variant TYPE disvariant.

l_repid = sy-repid.

CALL FUNCTION 'REUSE_ALV_FIELDCATALOG_MERGE'
EXPORTING
i_program_name = l_repid
i_internal_tabname = 'GT_OUTPUT'
i_inclname = l_repid
CHANGING
ct_fieldcat = lt_fieldcat
EXCEPTIONS
inconsistent_interface = 1
program_error = 2
OTHERS = 3.
IF sy-subrc > 0.
MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
ENDIF.

ls_fieldcat-seltext_l = 'Role Count'.
ls_fieldcat-seltext_m = 'Role Count'.
ls_fieldcat-seltext_s = 'Role Count'.
ls_fieldcat-reptext_ddic = 'Role Count'.
MODIFY lt_fieldcat FROM ls_fieldcat
TRANSPORTING
seltext_l
seltext_m
seltext_s
reptext_ddic
WHERE
fieldname = 'ROLE_COUNT'.

ls_layout-zebra = 'X'.
ls_layout-colwidth_optimize = 'X'.
CALL FUNCTION 'REUSE_ALV_GRID_DISPLAY'
EXPORTING
is_layout = ls_layout
it_fieldcat = lt_fieldcat
i_save = 'A'
is_variant = ls_variant
TABLES
t_outtab = gt_output
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.
*&---------------------------------------------------------------------*
*& Form REQUIRED_OUTPUT
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM required_output.
DATA:lt_output TYPE TABLE OF zhs_usr_inf,
wa_output LIKE LINE OF lt_output.

SELECT * FROM zhs_usr_inf UP TO 1000 ROWS
INTO TABLE lt_output.
LOOP AT lt_output INTO wa_output.
gt_output-bname = wa_output-bname.
gt_output-name_text = wa_output-name_text.
gt_output-text = wa_output-text.
gt_output-agr_name = wa_output-agr_name.
gt_output-role_count = wa_output-role_count.
APPEND gt_output.
ENDLOOP.
REFRESH lt_output.

ENDFORM. " REQUIRED_OUTPUT
*&---------------------------------------------------------------------*
*& Form ASIGNMENT_TEST
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM asignment_test.

********** DO NOT CHANGE THIS SECTION **********
GET RUN TIME FIELD g_begintime.
IF p_val_1 EQ 'X'.
l_number = 1000.
ELSEIF p_val_2 EQ 'X'.
l_number = 5000.
ENDIF.
********** END NO CHANGE **********

IF p_val_3 EQ 'X'.
SELECT * INTO TABLE gt_user FROM usr02
WHERE clas > 'E'.
ELSE.
SELECT * INTO TABLE gt_user FROM usr02 UP TO l_number ROWS
WHERE clas > 'E'.
ENDIF.

SELECT * INTO TABLE gt_user_role FROM agr_users
FOR ALL ENTRIES IN gt_user
WHERE uname = gt_user-bname.

PERFORM combine_tables.
PERFORM get_addl_data.

********** DO NOT CHANGE THIS SECTION **********
GET RUN TIME FIELD g_endtime.
g_tottime = ( g_endtime / 1000000 ) - ( g_begintime / 1000000 ).
MESSAGE s398(00) WITH g_tottime.
********** END NO CHANGE **********

ENDFORM. " ASIGNMENT_TEST
*&---------------------------------------------------------------------*
*& Form asignment_details
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* -->P_0129 text
*----------------------------------------------------------------------*
FORM asignment_details USING i_dokname.

CALL FUNCTION '/PSYNG/BASIS_F1_HELP'
EXPORTING
dokname = i_dokname.

ENDFORM. " asignment_details
*&---------------------------------------------------------------------*
*& Form set_info
*&---------------------------------------------------------------------*
* text
*----------------------------------------------------------------------*
* --> p1 text
* <-- p2 text
*----------------------------------------------------------------------*
FORM set_info.
CONCATENATE '@8P@' text-001 INTO as_f1.
ENDFORM. " set_info

Here one more thing I want to ask is at end event is used to display the count like 1 2 3 4 5 6 id 6 roles are authenticate to user 1 but it shows 1 1 1 1 1 1

Thanks in advance

Re: resolve performance issue / performance tuning

former_member210008 — Fri, 23 Dec 2016 10:18:18 GMT

0) Try transaction ST12

1) SELECT * INTO TABLE gt_user_role FROM agr_users

Add condition for active roles here. You will process less records instead of skip'em in the LOOP AT later.

2) if usr21 and adrp isnt too big, select all data into internal table (sorted, hashed or with secondary keys). More memory consumption but faster.

3) add key for field uname in table gt_user_role.

4) FORM required_output.

Select into corresponding fields should be better

5) sort gt_output by uname and use binary search or add key for that field

Re: resolve performance issue / performance tuning

Former Member — Fri, 23 Dec 2016 15:08:10 GMT

Hello Amit,

Does your program work? I honestly tried to correct it, but it's written in such way, that I would have to know how exactly it should work, before I could start correcting it.

Some tips for you:

- stop using global variables (gt_user/gt_users_role) - sure, you have to define them as globals in TOP, but please pass them between subroutines via USING/CHANGING. This will make your code more readable.

- stop using TABLES (WITH HEADER LINE), instead define local internal table

- for looping use FIELD-SYMBOL instead of table header line

- fetch only the fields you'll need later - don't use '*' in SELECT

- DON'T USE SELECT/ENDSELECT - instead select all entries necessary and combine them in loops (remember about field-symbols 🙂 ) - if you nest selects statements consider JOIN

Try to implement my advices and, if you'll still experience poor performance, post here your corrected code and I'll try to help you.

BR
Michał

topic resolve performance issue / performance tuning in Application Development and Automation Discussions

resolve performance issue / performance tuning

Re: resolve performance issue / performance tuning

Re: resolve performance issue / performance tuning