Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance issue with the view WB2_V_MKPF_MSEG2

Former Member
0 Kudos

Hi folks,

here i am fetching the data by using the view WB2_V_MKPF_MSEG2. But am not using the posting date (BUDAT) at where condition in the select stetement as per my requirement. So it needs to fetch the entire records available. For this execution it is almost taking 3 - 4 hours time. Please suggest me how to tune this up based on irrespective posting date at where condition in the select statement. below is the statement where getting struck for long time. below is the code.

SELECT  xblnr werks_i matnr_i bwart_i menge_i dmbtr_i  xauto_i budat umwrk_i mblnr mjahr

   FROM wb2_v_mkpf_mseg2

   INTO  TABLE it_view1

   WHERE MBLNR BETWEEN 4900000000 AND 4999999999

   AND MBLNR_I BETWEEN 4900000000 AND 4999999999

   AND ZEILE_I BETWEEN 1 AND 9999

   AND MJAHR BETWEEN 2010 and 9999

   AND MJAHR_I BETWEEN 2010 and 9999

*  AND budat IN s_date

   AND matnr_i IN s_matnr

   AND werks_i IN s_werks

   AND bwart_i  IN ('ZE5','ZX5','ZD5','ZS5')

  %_HINTS ORACLE '&max_in_blocking_factor 100&'.

5 REPLIES 5

karun_prabhu
Active Contributor
0 Kudos

Hello Naresh.

1) If primary key(s) is available, use that to fetch the data into internal table.

     Perform your filtering,deleting and other operations in the internal table.

2) Else if secondary index(s) is available, use that to fetch the data into internal table.

    Perform your filtering,deleting and other operations in the internal table.

Regards.

former_member129652
Active Participant
0 Kudos

Hi Naineni,

  There are some redundant where conditions in the query.  For example, the definition of the view says "MKPF~MBLNR = MSEG~MBLNR"; hence, the condition "MBLNR_I BETWEEN 4900000000 AND 4999999999" is redundant. 

  The conditions of "ZEILE_I" and "MJAHR_I" are also redundant. 

  Maybe the query optimizer gets confused about those where clauses and chooses a bad execution plan.

raymond_giuseppi
Active Contributor
0 Kudos

I don't think the optimizer will be able to do many optimization for a request on such a view.

Could you use a JOIN between MEG and MKPF and remove redundant and trivial criteria. Then as it will find many indexes on MSEG, optimizer may get better result than reading the whole view sequentially.

Regards,

Raymond

Former Member
0 Kudos

Hi friends,

Yes I commented those redundant fields and even i tried with joining the MSEG and MKPF instead of this VIEW but still no use. it is taking some hours to get executed the query unless we give the BUDAT at where condition. There are around 90 lakhs of records available in the view. So Obviously it is taking time to fetch all of them. Can we optimize this, Please help me.

Regards

Naresh

Former Member
0 Kudos

Hi naresh ,

Writing select statement for database view and using join statement are the same . so you will not find any difference .

try to select MKPF and MSEG separately using for all entries and then you can join that using read statement in new internal table .

try out :this seems to be like between

RANGES : S_MBLNR  FOR MKPF-MBLNR.

        S_MBLNR-sign = 'I'.

        S_MBLNR-option = 'EQ'.

        S_MBLNR-low = '4900000000'.

        APPEND S_MBLNR.

        S_ MBLNR-sign = 'I'.

        S_MBLNR-option = 'EQ'.

        S_MBLNR-high = '4999999999'.

        APPEND S_MBLNR.

  like this  try for  ZEILE_I, MJAHR . Mostly we try not to use join or query for database view .

  feel free to convey your view on this .

Regards,

Sanjith N