Use Hashed Tables to Improve
Performance
Tips by: Horacio Zapettini report zuseofhashedtables. ************************************************************************ ** Program: ZUseOfHashedTables ** ************************************************************************ ** Author: Horacio Zapettini ** ** ** ** Versions: 4.6b - 4.6c ** ************************************************************************ ** Notes: ** ** this program shows how we can use hashed tables to improve ** ** the responce time. ** ** It shows, ** ** 1. how to declare hashed tables ** ** 2. a cache-like technique to improve access to master data ** ** 3. how to collect data using hashed tables ** ** 4. how to avoid deletions of unwanted data ** ************************************************************************ ** Results: the test we run read about 31000 rows from mkpf, 150000 ** ** rows from mseg, 500 rows from makt and 400 from lfa1. ** ** it filled ht_lst with 24500 rows and displayed them in ** ** alv grid format. ** ** ** ** It needed about 65 seconds to perform this task (with ** ** all the db buffers empty) ** ** ** ** The same program with standard tables needed 140 seconds ** ** to run with the same recordset and with buffers filled in ** ************************************************************************ ** Objetive: show a list that consists of all the material movements ** ** '101' - '901' for a certain range of dates in mkpf-budat. ** ** the columns to be displayed are: ** ** mkpf-budat, ** ** mkpf-mblnr, ** ** mseg-lifnr, ** ** lfa1-name1, ** ** mkpf-xblnr, ** ** mseg-zeile ** ** mseg-charg, ** ** mseg-matnr, ** ** makt-maktx, ** ** mseg-erfmg, ** ** mseg-erfme. ** ** or show a sumary list by matnr - menge ** ** ** ** You'll have to create a pf-status called vista - ** ** See form set_pf_status for details ** ************************************************************************ ** tables used - tables: mkpf, mseg, lfa1, makt. ** global hashed tables used * data: begin of wa_mkpf, "header mblnr like mkpf-mblnr, mjahr like mkpf-mjahr, budat like mkpf-budat, xblnr like mkpf-xblnr, end of wa_mkpf. data: ht_mkpf like hashed table of wa_mkpf with unique key mblnr mjahr with header line. * data: begin of wa_mseg, " line items mblnr like mseg-mblnr, mjahr like mseg-mjahr, zeile like mseg-zeile, bwart like mseg-bwart, charg like mseg-charg, matnr like mseg-matnr, lifnr like mseg-lifnr, erfmg like mseg-erfmg, erfme like mseg-erfme, end of wa_mseg. data ht_mseg like hashed table of wa_mseg with unique key mblnr mjahr zeile with header line. ** cache structure for lfa1 records data: begin of wa_lfa1, lifnr like lfa1-lifnr, name1 like lfa1-name1, end of wa_lfa1. data ht_lfa1 like hashed table of wa_lfa1 with unique key lifnr with header line. ** cache structure for material related data data: begin of wa_material, matnr like makt-matnr, maktx like makt-maktx, end of wa_material. data: ht_material like hashed table of wa_material with unique key matnr with header line. ** result table data: begin of wa_lst, " budat like mkpf-budat, mblnr like mseg-mblnr, lifnr like mseg-lifnr, name1 like lfa1-name1, xblnr like mkpf-xblnr, zeile like mseg-zeile, charg like mseg-charg, matnr like mseg-matnr, maktx like makt-maktx, erfmg like mseg-erfmg, erfme like mseg-erfme, mjahr like mseg-mjahr, end of wa_lst. data: ht_lst like hashed table of wa_lst with unique key mblnr mjahr zeile with header line. data: begin of wa_lst1, " sumary by material matnr like mseg-matnr, maktx like makt-maktx, erfmg like mseg-erfmg, erfme like mseg-erfme, end of wa_lst1. data: ht_lst1 like hashed table of wa_lst1 with unique key matnr with header line. ** structures for alv grid display. ** itabs type-pools: slis. data: it_lst like standard table of wa_lst with header line, it_fieldcat_lst type slis_t_fieldcat_alv with header line, it_sort_lst type slis_t_sortinfo_alv, it_lst1 like standard table of wa_lst1 with header line, it_fieldcat_lst1 type slis_t_fieldcat_alv with header line, it_sort_lst1 type slis_t_sortinfo_alv. ** structures data: wa_sort type slis_sortinfo_alv, ls_layout type slis_layout_alv. ** global varialbes data: g_lines type i. data: g_repid like sy-repid, ok_code like sy-ucomm. ** selection-screen "text: Dates: select-options: so_budat for mkpf-budat default sy-datum. "text: Material numbers. select-options: so_matnr for mseg-matnr. selection-screen uline. selection-screen skip 1. "Text: show summary by material. parameters: gp_bymat as checkbox default ''. start-of-selection. perform get_data. perform show_data. end-of-selection. *---------------------------------------------------------------------* * FORM get_data * *---------------------------------------------------------------------* * ........ * *---------------------------------------------------------------------* form get_data. select mblnr mjahr budat xblnr into table ht_mkpf from mkpf where budat in so_budat. " make use of std index. ** have we retrieved data from mkpf? describe table ht_mkpf lines g_lines. if g_lines > 0. ** if true then retrieve all related records from mseg. ** Doing this way we make sure that the access is by primary key ** of mseg. ** The reason is that is faster to filter them in memory ** than to allow the db server to do it. select mblnr mjahr zeile bwart charg matnr lifnr erfmg erfme into table ht_mseg from mseg for all entries in ht_mkpf where mblnr = ht_mkpf-mblnr and mjahr = ht_mkpf-mjahr. endif. ** fill t_lst or t_lst1 according to user's choice. if gp_bymat = ' '. perform fill_ht_lst. else. perform fill_ht_lst1. endif. endform. form fill_ht_lst. refresh ht_lst. ** Example: how to discard unwanted data in an efficient way. loop at ht_mseg. * filter unwanted data check ht_mseg-bwart = '101' or ht_mseg-bwart = '901'. check ht_mseg-matnr in so_matnr. * read header line. read table ht_mkpf with table key mblnr = ht_mseg-mblnr mjahr = ht_mseg-mjahr. clear ht_lst. * * note : this may be faster if you specify field by field. move-corresponding ht_mkpf to ht_lst. move-corresponding ht_mseg to ht_lst. perform read_lfa1 using ht_mseg-lifnr changing ht_lst-name1. perform read_material using ht_mseg-matnr changing ht_lst-maktx. insert table ht_lst. endloop. endform. form fill_ht_lst1. refresh ht_lst1. ** Example: how to discard unwanted data in an efficient way. ** hot to simulate a collect in a faster way loop at ht_mseg. * filter unwanted data check ht_mseg-bwart = '101' or ht_mseg-bwart = '901'. check ht_mseg-matnr in so_matnr. * * note : this may be faster if you specify field by field. read table ht_lst1 with table key matnr = ht_mseg-matnr transporting erfmg. if sy-subrc <> 0. " if matnr doesn't exist in sumary table " insert a new record ht_lst1-matnr = ht_mseg-matnr. perform read_material using ht_mseg-matnr changing ht_lst1-maktx. ht_lst1-erfmg = ht_mseg-erfmg. ht_lst1-erfme = ht_mseg-erfme. insert table ht_lst1. else." a record was found. " collect erfmg. To do so, fill in the unique key and add " the numeric fields. ht_lst1-matnr = ht_mseg-matnr. add ht_mseg-erfmg to ht_lst1-erfmg. modify table ht_lst1 transporting erfmg. endif. endloop. endform. ** implementation of cache for lfa1. form read_lfa1 using p_lifnr changing p_name1. read table ht_lfa1 with table key lifnr = p_lifnr transporting name1. if sy-subrc <> 0. clear ht_lfa1. ht_lfa1-lifnr = p_lifnr. select single name1 into ht_lfa1-name1 from lfa1 where lifnr = p_lifnr. if sy-subrc <> 0. ht_lfa1-name1 = 'n/a in lfa1'. endif. insert table ht_lfa1. endif. p_name1 = ht_lfa1-name1. endform. ** implementation of cache for material data form read_material using p_matnr changing p_maktx. read table ht_material with table key matnr = p_matnr transporting maktx. if sy-subrc <> 0. ht_material-matnr = p_matnr. select single maktx into ht_material-maktx from makt where spras = sy-langu and matnr = p_matnr. if sy-subrc <> 0. ht_material-maktx = 'n/a in makt'. endif. insert table ht_material. endif. p_maktx = ht_material-maktx. endform. form show_data. if gp_bymat = ' '. perform show_ht_lst. else. perform show_ht_lst1. endif. endform. form show_ht_lst. "needed because the FM can't use a hashed table. it_lst[] = ht_lst[]. perform fill_layout using 'full display' changing ls_layout. perform fill_columns_lst. * perform sort_lst. g_repid = sy-repid. call function 'REUSE_ALV_GRID_DISPLAY' exporting i_callback_program = g_repid i_callback_pf_status_set = 'SET_PF_STATUS' is_layout = ls_layout it_fieldcat = it_fieldcat_lst[] * it_sort = it_sort_lst tables t_outtab = it_lst exceptions program_error = 1 others = 2. endform. form show_ht_lst1. "needed because the FM can't use a hashed table. it_lst1[] = ht_lst1[]. perform fill_layout using 'Sumary by matnr' changing ls_layout. perform fill_columns_lst1. * perform sort_lst. g_repid = sy-repid. call function 'REUSE_ALV_GRID_DISPLAY' exporting i_callback_program = g_repid i_callback_pf_status_set = 'SET_PF_STATUS' is_layout = ls_layout it_fieldcat = it_fieldcat_lst1[] * it_sort = it_sort_lst tables t_outtab = it_lst1 exceptions program_error = 1 others = 2. endform. form fill_layout using p_window_titlebar changing cs_layo type slis_layout_alv. clear cs_layo. cs_layo-window_titlebar = p_window_titlebar. cs_layo-edit = 'X'. cs_layo-edit_mode = space. endform. " armar_layout_stock form set_pf_status using rt_extab type slis_t_extab. ** create a new status ** and then select extras -> adjust template -> listviewer set pf-status 'VISTA'. endform. "set_pf_status define add_lst. clear it_fieldcat_lst. it_fieldcat_lst-fieldname = &1. it_fieldcat_lst-outputlen = &2. it_fieldcat_lst-ddictxt = 'L'. it_fieldcat_lst-seltext_l = &1. it_fieldcat_lst-seltext_m = &1. it_fieldcat_lst-seltext_m = &1. if &1 = 'MATNR'. it_fieldcat_lst-emphasize = 'C111'. endif. append it_fieldcat_lst. end-of-definition. define add_lst1. clear it_fieldcat_lst. it_fieldcat_lst1-fieldname = &1. it_fieldcat_lst1-outputlen = &2. it_fieldcat_lst1-ddictxt = 'L'. it_fieldcat_lst1-seltext_l = &1. it_fieldcat_lst1-seltext_m = &1. it_fieldcat_lst1-seltext_m = &1. append it_fieldcat_lst1. end-of-definition. form fill_columns_lst. * set columns for output. refresh it_fieldcat_lst. * add_lst 'BUDAT' 10. add_lst 'MBLNR' 10. add_lst 'LIFNR' 10. add_lst 'NAME1' 35. add_lst 'XBLNR' 15. add_lst 'ZEILE' 5. add_lst 'CHARG' 10. add_lst 'MATNR' 18. add_lst 'MAKTX' 30. add_lst 'ERFMG' 17. add_lst 'ERFME' 5. add_lst 'MJAHR' 4. endform. form fill_columns_lst1. * set columns for output. refresh it_fieldcat_lst1. add_lst1 'MATNR' 18. add_lst1 'MAKTX' 30. add_lst1 'ERFMG' 17. add_lst1 'ERFME' 5.. endform. Related: ABAP Books ListABAP/4 Certification, Programming, Smartforms, Sapscripts and Object Oriented Programming Books Smart Forms
Back to ABAP Menu:
Return to :-
(c) www.gotothings.com All material on this site is Copyright.
|