MKPF - Performance Issue

[Former Member]

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

.

[RaymondGiuseppi]

• 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

[Former Member]

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.

[Former Member]

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.

[ThomasZloch]

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

[Former Member]

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

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

[Former Member]

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.

[ThomasZloch]

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

[Former Member]

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.

[ThomasZloch]

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

[Former Member]

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.

[ThomasZloch]

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)

[Former Member]

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.

[ThomasZloch]

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

[Former Member]

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.

[ThomasZloch]

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

[SuhaSaha]

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