topic Re: PERFORMANCE TUNING in Application Development and Automation Discussions

PERFORMANCE TUNING

Former Member — Wed, 05 Sep 2007 13:10:40 GMT

Hi,

Req ..

if p_comp eq 'X'.

select * from ZTABLE into table gt_cms006

where werks in s_werks

and line in s_line

and aufnr in s_aufnr

and matnr in s_matnr

and charg in s_charg

and block in s_block.

else.

select * from ZTABLE into table gt_cms006

where werks in s_werks

and line in s_line

and aufnr in s_aufnr

and matnr in s_matnr

and charg in s_charg

and block in s_block

and archive ne 'X'.

endif.

Tables : ZTABLE has ‘aufnr’ as key element

MSEG key elements are ‘mblnr’ , ‘mjahr ‘ , ‘zeile’.

Note : The below statement is taking time to retrieve data is there any other way to get it tuned ??

select lgort charg aufnr bwart erfmg matnr from mseg into

corresponding fields of table itab_mseg for all entries

in gt_cms006 where aufnr = gt_cms006-aufnr

and werks = gt_cms006-werks

and ( lgort = 'COIL' or lgort = 'FG01' )

and ( bwart = '261' or bwart = '101' or bwart = '102' ).

Thanks,

Vind

Re: PERFORMANCE TUNING

Former Member — Wed, 05 Sep 2007 13:16:39 GMT

Check gt_cms006 is populated.

Use into TABLE instead of INTO CORRESPONDING...

The 'or' statements will not help either.

Re: PERFORMANCE TUNING

Former Member — Wed, 05 Sep 2007 13:23:59 GMT

Hi Vind,

You just declare the table itab_mseg with fields in the same order as like in the select statement. like lgort charg aufnr bwart erfmg matnr . change the order of fields in where condition like in table order.

Change the code as below.

if not gt_cms006[] is  initial.

select bwart
         matnr
          lgort 
         charg
         erfmg
         aufnr           
         from mseg
 into table itab_mseg 
for all entries in gt_cms006 
where ( bwart = '261' or bwart = '101' or bwart = '102' )
and werks = gt_cms006-werks
and ( lgort = 'COIL' or lgort = 'FG01' )
and aufnr = gt_cms006-aufnr.

endif.

Please reward points if helpful.

Thanks,

Suma.

Re: PERFORMANCE TUNING

Former Member — Wed, 05 Sep 2007 13:45:30 GMT

Hi Vind ,

Avoid using 'into corresponding' instead use into table.

Keep where condition simple as -

select lgort charg aufnr bwart erfmg matnr from mseg into

corresponding fields of table itab_mseg for all entries

in gt_cms006 where aufnr = gt_cms006-aufnr

and werks = gt_cms006-werks.

Define ranges for lgort as r_lgort

You can delete the internal table once data is fetched as -

DELETE ITAB_MSEG WHERE LGORT NOT IN R_LGORT .

similarly define ranges for bwart and delete itab_mseg using where condition as above. This will reduce load on database while fetching data and performance will improve.

Hope this will help.

Re: PERFORMANCE TUNING

Former Member — Wed, 05 Sep 2007 13:54:58 GMT

hi,

1.when using for all entries , always check if table gt_cms006 is not initial.

2. Avoid into corresponding fields instead declare the internal table with the same structure of database table