topic Re: Increase performance when using MKPF in Application Development and Automation Discussions

Increase performance when using MKPF

Pavithra_madhu — Thu, 26 Feb 2015 06:09:43 GMT

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.

Re: Increase performance when using MKPF

SandySingh — Thu, 26 Feb 2015 06:16:18 GMT

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

Re: Increase performance when using MKPF

Pavithra_madhu — Thu, 26 Feb 2015 06:44:20 GMT

Hi Sandy,

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

Re: Increase performance when using MKPF

atul_mohanty — Thu, 26 Feb 2015 07:01:03 GMT

Is the index 'BUD' active in MKPF ?

Re: Increase performance when using MKPF

Pavithra_madhu — Thu, 26 Feb 2015 08:25:55 GMT

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.

Re: Increase performance when using MKPF

Former Member — Thu, 26 Feb 2015 09:20:26 GMT

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

Re: Increase performance when using MKPF

LukaszPegiel — Thu, 26 Feb 2015 09:47:14 GMT

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.

Re: Increase performance when using MKPF

sabirshah1 — Thu, 26 Feb 2015 10:09:04 GMT

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.

Re: Increase performance when using MKPF

RaymondGiuseppi — Thu, 26 Feb 2015 10:30:42 GMT

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

Re: Increase performance when using MKPF

former_member218424 — Thu, 26 Feb 2015 10:44:15 GMT

Hi,

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

Regards,

Re: Increase performance when using MKPF

atul_mohanty — Thu, 26 Feb 2015 12:49:46 GMT

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.

Re: Increase performance when using MKPF

former_member305179 — Thu, 26 Feb 2015 12:57:13 GMT

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>.

Re: Increase performance when using MKPF

Pavithra_madhu — Fri, 27 Feb 2015 05:59:48 GMT

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???

Re: Increase performance when using MKPF

matt — Fri, 27 Feb 2015 06:43:18 GMT

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.

Re: Increase performance when using MKPF

muralikrishnan_vijayanp — Fri, 27 Feb 2015 06:49:37 GMT

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

Re: Increase performance when using MKPF

RaymondGiuseppi — Fri, 27 Feb 2015 06:53:34 GMT

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

Re: Increase performance when using MKPF

Pavithra_madhu — Fri, 27 Feb 2015 09:15:55 GMT

Hi,

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

Re: Increase performance when using MKPF

matt — Fri, 27 Feb 2015 11:29:44 GMT

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?

Re: Increase performance when using MKPF

Pavithra_madhu — Mon, 02 Mar 2015 11:05:04 GMT

Hi,

SELECT-OPTIONS :s_datum FOR sy-datum OBLIGATORY.

this is how i used s_datum in my program

Re: Increase performance when using MKPF

ThomasZloch — Mon, 02 Mar 2015 11:46:01 GMT

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