Performance issue with MSEG table in Production

[Former Member]

Hi,

I have written a report with 4 select queries.

First i am selecting data from VBRK table in i_vbrk. Then for all entries in i_vbrk, i am fetching records from VBRP into i_vbrp table. Then for all entries in i_vbrp, records are fetched from MKPF into i_mkpf. Then, finally for all entries in i_mkpf, records are fetched from MSEG into i_mseg table.

Performance of this report is good in Quality system, but it is very poor in Production systems. It is taking more than 20 mins to get executed. MSEG table query is taking most of the time.

I have done indexing and packet sizing on MSEG table, but still performace issue persists. So, cqan you please let me know if there is any way by which performace of the program can be improved???

Please help.

Thanks,

Archana

[Former Member]

Hi,

Before doing so check the SQL trace and Database trace in SE30 and ST05.

After than try this below:

1) Since you are using FOR ALL ENTRIES, before that try to hit the table with unique entries(sort and then delete adjacent duplicates with the fields in where condition)

This will reduce the number of records in the for all entries TABLE and reduce the database performance.

Note: There will be no change in the data output as FOR ALL ENTRIES will anyway get the unique entries from the database tables although you hit with unique or duplicate key entries..

Hope this helps

Regards

Shiva

[Former Member]

Hi,

Check consistency of Database Indexes maintained in Quality and Prod systems. Whether they are same or not?

Sometimes Indexes do get corrupted during incorrect transports.

Anoop

[Former Member]

Hi

few tips..might help you.

1. Duplicates are automatically removed from the resulting data set. Hence care should be taken that the unique key of the detail line items should be given in the select statement.

2. If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

3. If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level.

regards,

Prashant

[Former Member]

Hi,

Instead of using Joins/For all enteries in ur Code use same join conditions on a Database View (Create it from SE11)

This can improve the performance .

Regards,

Prakash

[Former Member]

Hi,

Its a good idea to use DB view. you can give all four table joins (VBKP, VBRK, MKPF, MSEG) in that and fetch data from view directly.It will definetely improve the performance.

regards,

Prashant

[Former Member]

Problem solved.

[Former Member]

Hi Dear ,

the same issue i am facing .

may i know ,how did u solve this problem either by DB view or by other concept?

Thansk & Best Regards:

Hans

[Former Member]

Hi Archana,

I was having the same issue for MKPF and MSEG , I am using INNER JOIN Condition .

SELECT

mkpf~mblnr

mkpf~mjahr

mkpf~budat

mkpf~usnam

mkpf~bktxt

mseg~zeile

mseg~bwart

mseg~prctr

mseg~matnr

mseg~werks

mseg~lgort

mseg~menge

mseg~meins

mseg~ebeln

mseg~sgtxt

mseg~shkzg

mseg~dmbtr

mseg~waers

mseg~sobkz

mkpf~xblnr

mkpf~frbnr

mseg~lifnr

INTO TABLE xmseg

FROM mkpf

INNER JOIN mseg

ON mkpf_{mandt EQ mseg}mandt AND

mkpf_{mblnr EQ mseg}mblnr AND

mkpf_{mjahr EQ mseg}mjahr

WHERE mkpf~vgart IN se_vgart

AND mkpf~budat IN se_budat

AND mkpf~usnam IN se_usnam

AND mkpf~bktxt IN se_bktxt

AND mseg~bwart IN se_bwart

AND mseg~matnr IN se_matnr

AND mseg~werks IN se_werks

AND mseg~lgort IN se_lgort

AND mseg~sobkz IN se_sobkz

AND mseg~lifnr IN se_lifnr

%_HINTS ORACLE '&SUBSTITUTE VALUES&'.

But still I have a issue in performance , Can anybody give some suggestions , please .

Regards,

Shiv

[Former Member]

What a bad way to end this thread :" problem Solved" Does it help others Nooooooo..!!