topic Re: MKPF - Performance Issue in Application Development and Automation Discussions

MKPF - Performance Issue

Former Member — Wed, 22 May 2013 11:59:31 GMT

Dear Gurus ,

I have a Z-Report which was working fine since one year now report started Hanging/Freezing on select query on MKPF table

Report is only freezing while extracting material documents from MKPF only in Production system system .

Code :

     SELECT
       BUDAT
       MBLNR
       MJAHR
     INTO TABLE IT_MAT_DOC_H
      FROM MKPF
    FOR ALL ENTRIES IN IT_MAT_DOC_TMP
      WHERE MBLNR   = IT_MAT_DOC_TMP-MBLNR
      AND MJAHR     IN S_MJAHR
      AND BUDAT IN S_FIN_D
      %_HINTS MSSQLNT 'INDEX("MKPF" "MKPF~Z02")'.

Background :

1. I have tried secondary Indexes on MBLNR(Mat doc), MJAHR(Year) & BUDAT (Posting date ) which is not working as the Input (MBLNR- MAT doc)which we have extracted from MSEG are approx 250000 .
.

2. I had also tried limiting the Package Size to 5000 via select endselect which also didn't work .

Appreciate if some one could guide me to fix this performance issue .

Regards

Naval

Re: MKPF - Performance Issue

RaymondGiuseppi — Wed, 22 May 2013 12:09:33 GMT

What are the keys of your Z02 index, can you remove the hint and analyze a SQL trace (S05)
Can you fill the first internal table with MJAHR too when selecting data from MSEG
Can you consider using a JOIN between MKPF and MSEG as FOR ALL ENTRIES get (very) bad performance when internal table size increases (So if Quality system is much smaller the problem will first arise in Production)
If MKPF only purpose is to check the posting date, can you consider implementing Note 1516684 - MKPF fields added to MSEG - Performance optimization so MSEG will contain a
BUDAT_MKPF (and other MKPF fields) and you are no longer required to read MKPF

Regards,

Raymond

Re: MKPF - Performance Issue

Former Member — Wed, 22 May 2013 12:12:04 GMT

Hi,

1- Correct the field sequence in select

2- Check table IT_MAT_DOC_TMP IS NOT INITIAL, before using this in for all entries.

3- Delete adjacent duplicates from table IT_MAT_DOC_TMP.

Re: MKPF - Performance Issue

Former Member — Wed, 22 May 2013 12:12:11 GMT

Why don't you try putting mjahr also in the tmp table.

and putting the select only on these two and later filtering the internal table on date.

Re: MKPF - Performance Issue

ThomasZloch — Wed, 22 May 2013 12:17:45 GMT

In addition to Raymon's reply, a secondary index with leading MBLNR is not effective, since this is a highly selective field (very many distinctive values) and already leading the primary key.

Index MKPF~BUD for BUDAT access (in case S_FIN_D contains only one or few posting dates) is already available.

Thomas

Re: MKPF - Performance Issue

Former Member — Wed, 22 May 2013 12:23:22 GMT

try this

Generally we select MKPF first and then select from MSEG but ur doing reverse.I dont know your requirement of doing so.So proposing alternative solutions.

IT_MAT_DOC_TMP1[] = IT_MAT_DOC_TMP[].

sort IT_MAT_DOC_TMP1 by mblnr mjahr.

delete adjacent duplicates from IT_MAT_DOC_TMP1 comparing mblnr mjahr.

Then write select as

     SELECT
       MBLNR
       MJAHR

       BUDAT
     INTO TABLE IT_MAT_DOC_H
      FROM MKPF
    FOR ALL ENTRIES IN IT_MAT_DOC_TMP1
      WHERE MBLNR   = IT_MAT_DOC_TMP1-MBLNR
      AND MJAHR       = IT_MAT_DOC_TMP1-mjahr
      AND BUDAT IN S_FIN_D
      %_HINTS MSSQLNT 'INDEX("MKPF" "MKPF~Z02")'.

You can also try using cursors

http://wiki.sdn.sap.com/wiki/display/ABAP/SELECT+Statements+and+CURSOR+statement+-+Performance+Analysis.

http://help.sap.com/saphelp_nw04/helpdata/EN/fc/eb3b23358411d1829f0000e829fbfe/content.htm

Re: MKPF - Performance Issue

Former Member — Thu, 23 May 2013 10:07:20 GMT

Hi,

1. please change the order of fields in first query from MSEG to MBLNR MJAHR ZEILE BWART MATNR WERKS SHKZG MENGE. Always ensure the order of the fields is in the same sequence as in database. Kindly change the order in the internal table as well.

2. While reading the entries from internal table it_mat . Take the entries into temp internal table of type it_mat, sort by matnr werks, delete adjacent duplicates to have unique entries.

3. You can remove the following logic as this is not needed.

LOOP AT it_mat_doc_tmp .

MOVE-CORRESPONDING it_mat_doc_tmp TO it_mat_doc .

AT NEW mjahr.

CLEAR s_mjahr.

s_mjahr-sign = 'I'.

s_mjahr-option = 'EQ'.

s_mjahr-low = it_mat_doc-mjahr.

APPEND s_mjahr.

CLEAR s_mjahr-low.

ENDAT.

ENDLOOP.

4. Change the order of fields in MKPF mblnr mjahr budat

5. Check if there are any entries in internal table it_mat_doc_tmp before querying on MKPF.

6. Check the order of fields in Z02 index and re-adjust the where clause accordingly.

Thanks and Regards,

Sriranjani Chimakurthy.

Re: MKPF - Performance Issue

ThomasZloch — Thu, 23 May 2013 10:44:37 GMT

Reg. 1., 4. and 6.: changing the sequence of the fields in the SQL-statement's field list or where-clause has no significant effect on the response time. Please do not spread such "performance tuning legends".

Thomas

Re: MKPF - Performance Issue

Former Member — Thu, 23 May 2013 11:08:37 GMT

Hi Thomas,

The order of the fields will have an impact on the performance as when the data is read from database it will be in one sequence and will putting into internal table it shall have a difference.

The order of the fields in where clause will have a difference as SAP tries to find a nearest index.

The primary index is based on the key fields in the table.

The points were recommended based on our experience.

Thanks and Regards,

Sriranjani Chimakurthy.

Re: MKPF - Performance Issue

ThomasZloch — Thu, 23 May 2013 11:24:35 GMT

If I find the time I will create a document with some measurements that will try to prove this wrong. Maybe it was an issue in earlier years, I wouldn't remember.

The best index or access path will be found irrespective of the field sequence in the where clause, otherwise this would be pretty poor after decades of CBO tweaking, in my opinion. What matters is if certain fields are actually present in the where clause or not, and of course the actual selection condition.

Also, the sequence of fields in indexes is very important. Maybe you are just mixing things up?

Thomas

Re: MKPF - Performance Issue

Former Member — Thu, 23 May 2013 11:27:42 GMT

Hi Thomas,

Please see below the code that i have written and run in my system.

REPORT ztest12467788.

DATA: v_time1 TYPE i.

DATA: v_time2 TYPE i.

DATA : BEGIN OF it_mat_doc OCCURS 0,

matnr TYPE matnr,

mblnr TYPE mblnr,

mjahr TYPE mjahr,

zeile TYPE mblpo,

werks TYPE werks_d,

shkzg TYPE shkzg,

bwart TYPE bwart,

menge TYPE menge_d,

END OF it_mat_doc,

BEGIN OF it_mat_doc2 OCCURS 0,

mblnr TYPE mblnr,

mjahr TYPE mjahr,

zeile TYPE mblpo,

bwart TYPE bwart,

matnr TYPE matnr,

werks TYPE werks_d,

shkzg TYPE shkzg,

menge TYPE menge_d,

END OF it_mat_doc2.

GET RUN TIME FIELD v_time1.

SELECT

matnr

mblnr

mjahr

zeile

werks

shkzg

bwart

menge

INTO TABLE it_mat_doc

FROM mseg

WHERE werks = 'SE70'.

GET RUN TIME FIELD v_time2.

v_time2 = v_time2 - v_time1.

WRITE : ' Query 1'.

WRITE v_time2.

CLEAR : v_time1, v_time2.

GET RUN TIME FIELD v_time1.

SELECT

mblnr

mjahr

zeile

bwart

matnr

werks

shkzg

menge

INTO TABLE it_mat_doc2

FROM mseg

WHERE werks = 'SE70'.

GET RUN TIME FIELD v_time2.

v_time2 = v_time2 - v_time1.

WRITE : 'Query2'.

WRITE v_time2.

Result :

Query 1 18.847 Query2 13.158

in my test system data in MSEG for werks = SE70 , i have only 3000 entries.

When i have run without that check i had 34000 entries.

the result for the same is :

Query 1 24.502.469 Query2 108.644

I would be waiting for your results.

Thanks and Regards,

Sriranjani Chimakurthy.

Re: MKPF - Performance Issue

ThomasZloch — Thu, 23 May 2013 11:36:26 GMT

Please run this several times in a row and only use the last result, to filter out effects of buffering on various levels. In my dev box there is only 700 MSEG rows, got to find something else...

Thomas

Edit: now ran your code for 770K MSEGs twice, second result: Query 1 6.400.816 Query2 6.338.416 -> no significant difference

(this is Oracle 11.2.0.2.0 by the way)

Re: MKPF - Performance Issue

Former Member — Thu, 23 May 2013 13:19:45 GMT

Hi Thomas,

Thanks for your inputs.

But i wanted to know why should we not consider the timings for the initial runs ?

As user in production would not try to run for multiple runs to see if the query is faster?

I could see a huge difference for the initial runs.

Thanks and Regards,

Sriranjani Chimakurthy.

Re: MKPF - Performance Issue

ThomasZloch — Thu, 23 May 2013 13:32:16 GMT

Usually there is more than one user active in a production system, so indeed the very first one to run a particular query might have to wait longer, but if it is a frequently run query, then the results will remain (partly or completely) inside caches/buffers on the database and/or application servers, so can be retrieved much faster (from memory as opposed to disks).

By having two similar queries in your program, the first one will retrieve the data from disk, while the second one will be accessing data buffered in memory, returning much quicker. So it is not "fair" to use the results of the first run. In fact, if you switch the queries in your program, the first result should again show longer response time (once the buffered data was cleared or pushed out by more recent results from other queries), although it is the formerly "faster" query.

By the way, there is much better experts here for these sort of "deep" topics (seen from ABAP level), and with HANA I bet everything is different anyway.

Thomas

Re: MKPF - Performance Issue

Former Member — Thu, 23 May 2013 14:00:22 GMT

Hi Thomas,

I think i have understood the reason you have mentioned on buffering.

I have tried keeping the queries in 2 different reports and have got the following timings. Please note that i have logged off after query 1 to clear the buffer.

first time

Query 1 : 327.364

Query 2 : 201.302

second time

Query 1 : 174.382

Query 2 : 103.252

My suggestion would still be to re-align fields based on the sequence in the database as we still see some difference.

Thanks and Regards,

Sriranjani Chimakurthy.

Re: MKPF - Performance Issue

ThomasZloch — Thu, 23 May 2013 14:29:48 GMT

Well, my results are different, so I'm not buying this. I will try to come up with a transparent test program that can be run in any ABAP box for comparison.

Seems that we have scared away the original poster anyway...

Thomas

Re: MKPF - Performance Issue

SuhaSaha — Thu, 23 May 2013 15:27:35 GMT

The order of the fields will have an impact on the performance as when the data is read from database it will be in one sequence and will putting into internal table it shall have a difference.

I agree with Thomas on this, this is just one of the ABAP "fads" doing rounds! The difference you're talking about is negligible to be of any consequence, the kernel optimizes it!

Another "fad" i have encountered is the usage of INTO CORRESPONDING FIELDS, MOVE-CORRESPONDING. The modern ABAP kernel is smart enough to deal with these statements. They may have been problematic in pre-historic ABAP releases, i don't know about it and can't comment!

My 2 cents!

BR,

Suhas