topic select mchb performance improvement in Application Development and Automation Discussions

select mchb performance improvement

Former Member — Tue, 18 Aug 2009 06:08:27 GMT

Could you please tell me how can I improve the performance because it is taking much time while fetching from mchb table.

(note:There are no indexes to mchb table)

=====================

1 st part

TYPES : BEGIN OF tt_mchb,

matnr TYPE matnr,

werks TYPE werks_d,

lgort TYPE LGORT_D,

charg TYPE charg_d,

clabs TYPE labst,

cinsm TYPE insme,

cspem TYPE speme,

KZICL TYPE KZILL,

END OF tt_mchb.

Data : i_mchb TYPE TABLE OF tt_mchb.

SELECT matnr

werks

lgort

charg

clabs

cinsm

cspem

kzicl

FROM mchb

INTO TABLE i_mchb

WHERE matnr IN s_matnr

AND werks IN s_plant

AND charg IN s_charg

AND ersda IN s_date

==================

2nd part :

FORM output_itab2_mtart .

FIELD-SYMBOLS : <lwa_mch1> TYPE tt_mch1,

<lwa_makt_maktx> TYPE tt_makt_maktx,

<lwa_mvke> TYPE tt_mvke,

<lwa_mara> TYPE tt_mara.

LOOP AT i_mcha INTO wa_mcha.

READ TABLE i_mch1 WITH KEY matnr = wa_mcha-matnr

charg = wa_mcha-charg

ASSIGNING <lwa_mch1>.

IF sy-subrc = 0.

READ TABLE i_mara WITH KEY matnr = wa_mcha-matnr

ASSIGNING <lwa_mara> .

IF sy-subrc = 0.

READ TABLE i_mvke WITH KEY matnr = wa_mcha-matnr

ASSIGNING <lwa_mvke>.

IF sy-subrc = 0.

wa_final_itab-mvgr1 = <lwa_mvke>-mvgr1.

ENDIF.

wa_final_itab-matnr = wa_mcha-matnr.

wa_final_itab-werks = wa_mcha-werks.

wa_final_itab-lifnr = wa_mcha-lifnr.

wa_final_itab-charg = <lwa_mch1>-charg.

wa_final_itab-lvorm = <lwa_mch1>-lvorm.

wa_final_itab-zustd = <lwa_mch1>-zustd.

wa_final_itab-licha = <lwa_mch1>-licha.

wa_final_itab-vfdat = <lwa_mch1>-vfdat.

READ TABLE i_makt_maktx WITH KEY matnr = wa_mcha-matnr

ASSIGNING <lwa_makt_maktx>.

IF sy-subrc = 0.

wa_final_itab-maktx = <lwa_makt_maktx>-maktx.

ENDIF.

APPEND wa_final_itab TO i_final_itab.

CLEAR : wa_final_itab, wa_mcha, wa_stext.

ENDIF.

ENDLOOP.

ENDFORM.

Thanks in ADV.

Re: select mchb performance improvement

Former Member — Tue, 18 Aug 2009 08:51:04 GMT

Hi,

As you mentioned that there are no Index created on that table, it would really improve the performance if you can create an index with the following on mind. Have a look at the following link:

http://help.sap.com/saphelp_nw70/helpdata/en/aa/47349d0f1c11d295380000e8353423/content.htm

You can have a look at the performance time by using the SQL Trace (ST05). If you still find that the database access is not improved you can consider the use of Native SQL statement to access the database. But there are some drawbacks to it.

http://help.sap.com/saphelp_nw70/helpdata/en/aa/4734a00f1c11d295380000e8353423/frameset.htm.

From you code, it's better if you can use a internal table of field symbol instead of a standard internal table. Also, before calling the select statement, check all the select option that are part of it. This will keep the amount of data to be transfered very less and will also enhance the where clause thus will improve the search criteria.

Have a look at the following link for a complete overview of the Performance Factors of ABAP Code:

http://help.sap.com/saphelp_nw70/helpdata/en/aa/4734a00f1c11d295380000e8353423/frameset.htm

Hope this will help.

Thanks,

Samantak.

Re: select mchb performance improvement

SG141 — Tue, 18 Aug 2009 14:23:05 GMT

Since you selecting the MCHB by it's key fields.. the select should fetch records quickly.... In 2nd part of your code whenever you are reading internal table sort them and use Binary search addition. It will improve runtime performance of the program.



LOOP AT i_mcha INTO wa_mcha.

READ TABLE i_mch1 WITH KEY matnr = wa_mcha-matnr " Sort I_MCH1 and add Binary search
charg = wa_mcha-charg
ASSIGNING <lwa_mch1>.
IF sy-subrc = 0.

READ TABLE i_mara WITH KEY matnr = wa_mcha-matnr  " Sort I_MARA and add Binary search
ASSIGNING <lwa_mara> .

IF sy-subrc = 0.
READ TABLE i_mvke WITH KEY matnr = wa_mcha-matnr  " Sort I_MVKE and add Binary search
ASSIGNING <lwa_mvke>.
IF sy-subrc = 0.
wa_final_itab-mvgr1 = <lwa_mvke>-mvgr1.
ENDIF.
wa_final_itab-matnr = wa_mcha-matnr.
wa_final_itab-werks = wa_mcha-werks.
wa_final_itab-lifnr = wa_mcha-lifnr.
wa_final_itab-charg = <lwa_mch1>-charg.
wa_final_itab-lvorm = <lwa_mch1>-lvorm.
wa_final_itab-zustd = <lwa_mch1>-zustd.
wa_final_itab-licha = <lwa_mch1>-licha.
wa_final_itab-vfdat = <lwa_mch1>-vfdat.

READ TABLE i_makt_maktx WITH KEY matnr = wa_mcha-matnr " Sort I_MAKT and add Binary search
ASSIGNING <lwa_makt_maktx>.
IF sy-subrc = 0.
wa_final_itab-maktx = <lwa_makt_maktx>-maktx.
ENDIF.

APPEND wa_final_itab TO i_final_itab.
CLEAR : wa_final_itab, wa_mcha, wa_stext.
ENDIF.
ENDIF.
ENDLOOP

.

Re: select mchb performance improvement

Former Member — Tue, 18 Aug 2009 15:56:13 GMT

HI,

The primary key of the table is

MATNR

WERKS

LGORT

CHARG.

In you select query

SELECT matnr 
werks 
lgort 
charg 
clabs 
cinsm 
cspem 
kzicl 
FROM mchb
INTO TABLE i_mchb
WHERE matnr IN s_matnr
AND werks IN s_plant
AND charg IN s_charg
AND ersda IN s_date.

you are passing only part of the key; so the selection will take time.

Pass the entire primary key fields in the where clause.

Incase you do not have LGORT on your selection screen; then create a range table and get all the storage location values from table T001L into this range table and use the same in the select query.

In you second perform FORM output_itab2_mtart;

You are reading on the internal tables i_mch1, i_mvke, i_mara and i_makt_maktx.

Use binary search in these read statements but efore using binary search make sure the tables are sorted by the fields which u specify in the with key clause.

regards,

Ankur Parab

Re: select mchb performance improvement

Former Member — Fri, 21 Aug 2009 00:26:20 GMT

It is a good reminder that the PK of a table is also an index - people forget that.


you are passing only part of the key; so the selection will take time.
Pass the entire primary key fields in the where clause.
In case you do not have LGORT on your selection screen; then create a range table and get all the storage location values from table T001L into this range table and use the same in the select query.

That will not help. Including all possible values gives the same index selectivity as supplying nothing.

Matnr and Werks are the leading fields of the PK, so the number of filtered entries here are what drive the selectivity of the PK based fetch.

How many records are being returned? How did you confirm that it is the original fetch that is taking a long time and not Part 2?

The need for your other i_* tables is hard to determine. You may be better served to join on all of them in a single fetch, rather than fetch all records from each table yourself and then have your ABAP perform the join. This is potentially bad for two reasons:

1. You fetch more data from the other tables than you need. Not sure if this is the case, you may be using the same filters.

2. As other replies have suggested you need to know how to set up the internal tables to perform this logic efficiently.

DB layer is excellent for doing this for you. You may have your other i_* tables for good reason, but if not, consider using a single select with joins.

Re: select mchb performance improvement

Former Member — Sun, 23 Aug 2009 13:31:57 GMT

Hi Sam,

Is any of the select-option mandatory? If all are optional and all are blank, the select will fetch entire data from table.

Please make sure that the any of the primary key among the fields in where condition is populated.

Regards,

Anand