Increase performance when using MKPF

[Pavithra_madhu]

Hi all,

My requirement is when input posting date range have to get material document numbers(Good Issue document numbers of movement type '201') in that date range..

For that i use MKPF table.. In production fetching data get lot of time because it is a huge table..

Are there any suggestion to increase the performance??

Thanks,

Pavithra.

[SandySingh]

You can use the PACKAGE SIZE statement to address the performance issue

Sample code

SELECT *

FROM   spfli

INTO   CORRESPONDING FIELDS OF TABLE IT_SPFLI

        PACKAGE SIZE 500.

   LOOP AT IT_SPFLI INTO wa.

   * Do processing

   ENDLOOP.

ENDSELECT.

Regards

Sandy

[Pavithra_madhu]

Hi Sandy,

Thnx. I try it but not increase the performance..

[atul_mohanty]

Hi

Is the index 'BUD' active in MKPF ?

[Pavithra_madhu]

Hi Atul,

Yes BUD active..

this is my query

SELECT mblnr mjahr blart budat

   FROM mkpf

   INTO TABLE it_mkpf

   WHERE budat IN s_datum.

[Former Member]

You can check with the BASIS team to find if the issues is from oracle database side.

[ŁukaszPęgiel]

Also check if these notes can be implemented at your server:

• 1516684 MKPF fields added to MSEG - Performance optimization
• 1550000 MB51: Redesign of selection for performance optimization
• 1558298 MB5B: Redesign of selection to optimize performance
• 1567602 DB dependent steps to support the redesign of MB51
• 1598760 FAQ: MSEG Enhancement & Redesign MB51/MB5B

If yes or if they are implemented then you can use directly MSEG for your select statement. Also use hashed tables to store selected data.

[sabirshah1]

If you want to get bwart i.e. movement type from Mseg by using mblnr-mkpf better way you should use joins in this case .

coz data is too much and to run the query faster we should use uniq key in where condition , check if join works or not if this doesnt u r left with the another option i.e. secondary index .

Thanks & Regards ,

Sabir Shah.

[atul_mohanty]

Hi -

If the BUD index is active, then accessing through the index you have the input 'BUDAT' already in your where clause. In this only try to use parallel processing though task on smaller date range of S_DATUM to improve the performance . You can try that.

[matt]

And what does s_datum contain?

When you use select options in a select, the performance can vary greatly according to what the select option actually contains. If s_datum is empty, then you will be selecting everything, for example.

[Pavithra_madhu]

Hi,

s_datum is mandatory field which contains date range. that range is the posting date range.

[matt]

Yes. But what does it actually contain? You know it's an internal table, right? Of structure sign option low high?

When you get bad performance

a) Is it empty?

b) Does it contain a single value?

c) Does it contain I BT lowdate highdate?

d) Does it contain something more complex?

The performance can vary dramatically according to each case. The SQL executed on the database server can be radically different according to what s_datum contains. Therefore no sensible answer can be given without this fairly basic piece of information.

Do you understand now?

[Pavithra_madhu]

Hi,

SELECT-OPTIONS  :s_datum FOR sy-datum OBLIGATORY.

this is how i used s_datum in my program

[ThomasZloch]

What happens when you enter a single date in S_DATUM and run it, does it finish any faster?

Now try a monthly then a yearly range and compare.

Thomas

[matt]

Do you have an idea why I'm asking the question? You've eliminated possibility a)

There still remains:

b) Does it contain a single value?

c) Does it contain I BT lowdate highdate?

d) Does it contain something more complex?

Do you understand that what is entered by the user in your select option can make a dramatic difference to run time?

[Pavithra_madhu]

Hi,

It contain date range with lowdate and highdate.. Need to get Good Issue summary of a user and need to get data of several months.

[matt]

So finally we get the answer: I BT lowdate highdate

[raymond_giuseppi]

If you have no other selection criteria than posting date, there is not much option, once you check index (BUD) activation.

You could

• Only extract required fields (not *)
• Try to parallelize extraction, as it is the first table extracted, you can only split date range in smaller ranges and execute those in parallel tasks thru RFC enabled FM (doc link)

Other solutions in the field of Basis, Database management or IT architecture, not Abap...

If you also require selection on MSEG movement type (BWART) either

• Use a JOIN between both tables
• Implement (if required) note 1516684 - MKPF fields added to MSEG - Performance optimization, and now only select data from MSEG (as MKPF-BUDAT = MSEG-BUDAT_MKPF)
• Use the two options if some MKPF fields are missing in new MSEG, but you can now test with criteria on any of the two tables for common fields
• Please, don't use a FOR ALL ENTRIES on MSEG with result of MKPF extraction (if performance were already bad for single MKPF, I refuse to consider this case...

Regards,

Raymond

[former_member218424]

Hi,

You can try for open cursor technique, just remember to close cursor.

Regards,

[former_member305179]

Hey Pavithra Jayasinghe,

call db to get data is bound with high cost (time). it`s better to load data into Memory if it`s possible.

Use ranges in your SQL Statement will rise Performance of your SQL Statement. Work with Loop <tab> Assigning <field-symbol>.

[Pavithra_madhu]

HI,

I use INNER JOIN with MKPF and MSEG.. it get little bit faster than earlier...

I have to know when we use index if there are 3 fields in index, do we need to add all three fields in where condition to use index???

[muralikrishnan_vijayanp]

Hi Pavithra,

It is always good to have all the three fields in the where clause. Incase you do not have all three fields, make sure that the first field in the index is in your where clause. Also please take an SQL trace and make sure that the query is hitting the right index.

Thanks,

Muralikrishnan

[raymond_giuseppi]

Optimizer will consider using an index if the first keys of the index are provided in selection criteria, so can use a 3 keys index when 2 first keys are provided, but not if the first one is not provided. You can check this with the SQL trace (Test and Analysis Tools in ABAP, Performance Analysis, Analyzing Performance Data, Analyzing Trace Records, SQL Trace Analysis) -> ST05

Regards,

Raymond