Skip to Content
author's profile photo Former Member
Former Member

MKPF and MSEG join based upon entries in an internal table

Hello all,

I have spent alot of today searching different forums and have finally had to ask for help. Any advice you can give will be greatly appreciated as I can find many conflicting posts on this subject. I am not an expert so please bear this in mind on any advice that you can give.

I have an internal table containg around 0.8 million records detailing site, article, date based information. From this, I need to find any movements that happened to that article, at that site on that date.

The execution time is long and sm66 shows it being in sequential read mode on mkpf. I will leave it running overnight and see if the program finishes or crashes.

Here is the code fragment:

select t1~budat t2~matnr t2~werks t2~MBLNR t2~SHKZG t2~menge t2~DMBTR t2~VKWRT t2~VKWRA
  into CORRESPONDING FIELDS OF table it_movements_tmp
  from mkpf as t1
  inner join mseg as t2
  on t1~MBLNR = t2~MBLNR
  and t1~MJAHR = t2~MJAHR
  for ALL ENTRIES IN it_ZXSITEHISSTK_all
  where t1~budat = it_ZXSITEHISSTK_all-udate
  and   t2~matnr = it_ZXSITEHISSTK_all-matnr
  and   t2~werks = it_ZXSITEHISSTK_all-bwkey.

I am not sure whether it is better to :

1) Use for all entries from my internal table to the mkpf / mseg join or to put the whole thing into a loop around the internal table and for each pass do a select on mkpf joined with mseg.

2) Also, after reading other posts, I came to the conclusion that I was better off using a simple inner join between mkpf and mseg and not another for all entries.

Anything that you can suggest to make this code more efficient would be much appreciated.

Thanks...Cathy

Edited by: Thomas Zloch on Oct 25, 2010 5:49 PM - code formatting added

Add a comment
10|10000 characters needed characters exceeded

Related questions

4 Answers

  • Best Answer
    Posted on Oct 25, 2010 at 03:57 PM

    Because of the join statement, SM66 will show MKPF even though MSEG might be the bottle neck. Is index MSEG~M active as well? Are the database statistics of these tables up to date (check with a system admin)?

    Do you have a chance to run this query with less entries in your internal FAE driver table, so you can run it through an ST05 SQL trace and post the execution plan here?

    We have some experts visiting regularly who I'm sure will ask you more questions.

    Thomas

    P.S. one was already quicker than me 😊

    Add a comment
    10|10000 characters needed characters exceeded

    • >

      > I take on board the comment about my join maybe causing sm66 to show a sequential read and not the direct read I would have expected from (hopefully) using Mkpfbud and msegm indexes.

      Sequential read basically means that more than one record will be returned (as opposed to one record only with direct read), and so for accesses using non-unique indexes (like yours) sequential read is expected.

      There are "good" sequential reads and "bad" sequential reads, as there are "good" direct reads and "bad" direct reads, so direct read is not a goal in itself.

      Is this a one-off query, or something to be executed on a regular basis?

      I think your requirement is pretty common: matnr + werks + budat is very selective, but unfortunatelly the 3 fields are not in the same table. So matnr + werks (MSEGM) might not be very selective, and budat only (MKPFBUD) is not too selective either.

      For these cases SAP sometimes creates redundant "index" tables. For example you often need all sales orders from a given sales organizaton (VBAK-VKORG) and for one material (VBAP-MATNR), but these two fields are in two different tables, so there is a redundant table VAPMA with both fields (and others).

      I mention this because actually in our system we had the same need and we ended up creating a Z index table with fields BUDAT

      + MATNR + WERKS ( + MBLNR + MJAHR + ZEILE ). This is of course a decision you don't take easily, because you can easily have inconsistencies if you are not careful, but for us it has proven quite useful.

      Regards,

      Rui

  • Posted on Oct 25, 2010 at 03:55 PM

    Hi Cathy,

    1) You say that you have 800,000 records in it_ZXSITEHISSTK_all, right? How did you fill that table in the first place?

    2) If you did not have the date in that table, how many records do you think it would have? (that is, how many combinations of material and plant?)

    3) Make a trace in ST05 and post here the explain plan (ask if you need help on that). That is to check if MKPFBUDAT is being used, or MSEGM, or something else.

    Regards,

    Rui Dantas

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 25, 2010 at 03:40 PM

    I should also add that we have the BUD secondary index set up on MKPF

    Cathy

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 27, 2010 at 03:22 PM

    Thankyou both,

    using all of the points that you asked me to consider, the runtime of this code segment was dramatically reduced. I basically minimised the use of FAE to only those where it was the only option for the driver table and for all other records I did a seperate inner join on my original DB table and mseg and mkpf. It still produced sequential reads but GOOD ones this time 😊

    Thankou for making your responses in a very clearly understandable manner

    ...Cathy

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.