cancel
Showing results for 
Search instead for 
Did you mean: 

Maximum Time out runtime error

Former Member
0 Kudos

Hi All,

The following query is giving the error message:

SELECT mkpfbudat msegbwart msegwerks msegmatnr mseg~shkzg

msegmenge msegmeins msegdmbtr maraextwg makt~maktx

INTO TABLE i_src

FROM mkpf

JOIN mseg ON mkpfmblnr = msegmblnr

AND mkpfmjahr = msegmjahr

JOIN mara ON msegmatnr = maramatnr

JOIN makt ON msegmatnr = maktmatnr

WHERE mkpf~budat IN s_budat AND

mseg~bwart IN s_bwart AND

mseg~matnr IN s_matnr AND

mseg~werks IN s_werks AND

mara~mtart = c_mtart AND

makt~spras = sy-langu.

The error message is :

The program ZMMPR_KEY_MOVEMENT has exceeded the maximum permitted runtime and therefore has been terminated.

Can you tell me how we can improve the performance?

Thanks,

Sobhan.

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Are any of the select option table empty? Particularly s_matnr?

Rob

Former Member
0 Kudos

Hi All,

Thanks for the answers.

Rob: S_matnr field is empty. This is the only field empty.

Thnaks,

Sobhan.

Former Member
0 Kudos

Well that's it then. I think all of the other fields are in the index, but if s_matnr is empty, it won't help much.

Rob

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

You might consider trying something like this. This greatly increase the performance in my system.



report zrich_0002 .


data: begin of i_src occurs 0,
      budat type mkpf-budat,
      bwart type mseg-bwart,
      werks type mseg-werks,
      matnr type mseg-matnr,
      shkzg type mseg-shkzg,
      menge type mseg-menge,
      meins type mseg-meins,
      dmbtr type mseg-dmbtr,
      extwg type mara-extwg,
      maktx type makt-maktx,
      end of i_src.

select-options: s_budat for i_src-budat.
select-options: s_bwart for i_src-bwart.
select-options: s_matnr for i_src-matnr.
select-options: s_werks for i_src-werks.


select mkpf~budat mseg~bwart mseg~werks mseg~matnr mseg~shkzg
       mseg~menge mseg~meins mseg~dmbtr
*       mara~extwg makt~maktx
           into table i_src
             from mkpf
                inner join mseg
                  on mkpf~mblnr = mseg~mblnr
                 and mkpf~mjahr = mseg~mjahr
*join mara on mseg~matnr = mara~matnr
*join makt on mseg~matnr = makt~matnr
where mkpf~budat in s_budat and
     mseg~bwart in s_bwart and
     mseg~matnr in s_matnr and
     mseg~werks in s_werks.
*     and
*     mara~mtart = c_mtart and
*     makt~spras = sy-langu.

if not i_src[] is initial.
  sort i_src ascending by matnr.
  data: imara type table of mara with header line.
  select matnr mtart extwg into corresponding fields of table imara
             from mara for all entries in i_src
                     where matnr = i_src-matnr
                       and mtart = c_mtart.
  sort imara ascending by matnr.
  delete adjacent duplicates from imara comparing matnr.
endif.

if not imara[] is initial.
  sort imara ascending by matnr.
  data: imakt type table of makt with header line.
  select * into corresponding fields of table imakt
             from makt for all entries in imara
                     where matnr = imara-matnr
                       and spras = sy-langu.
  sort imakt ascending by matnr.
  delete adjacent duplicates from imakt comparing matnr.
endif.

loop at i_src.

  read table imara with key matnr = i_src-matnr.
  if sy-subrc <> 0.
    delete i_src.
    continue.
  else.
    i_src-extwg = imara-extwg.
  endif.

  read table imakt with key matnr = i_src-matnr.
  if sy-subrc <> 0.
    delete i_src.
    continue.
  else.
    i_src-maktx = imakt-maktx.
  endif.

  modify i_src.

endloop.



check sy-subrc  = 0.

Regards,

Rich Heilman

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

Did any of these answers help you. Please remember to award points for any/all helpful answers. Also, if your problem is solved, please make sure to mark this post as "solved". Thanks.

Regards,

Rich Heilman

Former Member
0 Kudos

Rich,

Thanks for the response. I am testing all these solutions one by one.

As transport moving takes a lot of time at my site, I will tell you which one worked for me and reward the points accordingly.

Thanks,

Sobhan.

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

Let us know!! Thanks. I very interested to know which technique worked out.

Regards,

Rich Heilman

Former Member
0 Kudos

Hello Rich,

I couldn't test your solution. I used hash table solution and it is working considerable better performance. If I get the same problem, I will implement your solution and update you on this.

Thanks for the great help

Thanks,

Sobhan.

Answers (4)

Answers (4)

Former Member
0 Kudos

Try this, changed the join conditions

SELECT mkpf~budat

mseg~bwart

mseg~werks

mseg~matnr

mseg~shkzg

mseg~menge

mseg~meins

mseg~dmbtr

mara~extwg

makt~maktx

INTO TABLE i_src

FROM ( ( mkpf

JOIN mseg ON msegmblnr = mkpfmblnr and

msegmjahr = mkpfmjahr )

JOIN mara ON maramatnr = msegmatnr )

JOIN makt ON maktmatnr = maramatnr )

WHERE mkpf~budat IN s_budat AND

mseg~bwart IN s_bwart AND

mseg~matnr IN s_matnr AND

mseg~werks IN s_werks AND

mara~mtart = c_mtart AND

makt~spras = sy-langu.

Even then if it times out, then schedule the program in background.

former_member225448
Participant
0 Kudos

Hi Sobhan,

I remember this problem, getting MBELN's for a date range and material range and BWART range, from the early 90's on an R/3 1.2.

Try it this way:

TABLES: mkpf, mseg, mara.

SELECT-OPTIONS:
  s_budat FOR mkpf-budat,
  s_bwart FOR mseg-bwart,
  s_matnr FOR mseg-matnr,
  s_werks FOR mseg-werks.
PARAMETERS:
  c_mtart TYPE mtart.

TYPES:
  BEGIN OF i_scr_t,
    mblnr LIKE mkpf-mblnr,
    mjahr LIKE mkpf-mjahr,
    budat LIKE mkpf-budat,
    bwart LIKE mseg-bwart,
    werks LIKE mseg-werks,
    matnr LIKE mseg-matnr,
    shkzg LIKE mseg-shkzg,
    menge LIKE mseg-menge,
    meins LIKE mseg-meins,
    dmbtr LIKE mseg-dmbtr,
    extwg LIKE mara-extwg,
    maktx LIKE makt-maktx,
  END OF i_scr_t,

  BEGIN OF mblnr_t,
    mblnr LIKE mkpf-mblnr,
    mjahr LIKE mkpf-mjahr,
    budat LIKE mkpf-budat,
  END OF mblnr_t,

  BEGIN OF mat_t,
    matnr LIKE mara-matnr,
    extwg LIKE mara-extwg,
    mtart LIKE mara-mtart,
    maktx LIKE makt-maktx,
  END OF mat_t.


DATA:
  lt_mblnr TYPE TABLE OF mblnr_t WITH HEADER LINE,
  lt_mat   TYPE HASHED TABLE OF mat_t WITH UNIQUE KEY matnr,
  ls_mat   LIKE LINE OF lt_mat,
  i_scr    TYPE TABLE OF i_scr_t WITH HEADER LINE.

SELECT budat mjahr mblnr FROM mkpf
  INTO CORRESPONDING FIELDS OF TABLE lt_mblnr
  WHERE mkpf~budat IN s_budat.

LOOP AT lt_mblnr.

  SELECT bwart werks matnr shkzg menge meins dmbtr
    FROM mseg
    INTO CORRESPONDING FIELDS OF i_scr
    WHERE mblnr = lt_mblnr-mblnr
      AND mjahr = lt_mblnr-mjahr
      AND bwart IN s_bwart
      AND matnr IN s_matnr
      AND werks IN s_werks.

    READ TABLE lt_mat INTO ls_mat
      WITH TABLE KEY matnr = i_scr-matnr.
    IF sy-subrc <> 0.
      ls_mat-matnr = i_scr-matnr.
      SELECT SINGLE extwg mtart FROM mara
        INTO (ls_mat-extwg, ls_mat-mtart)
        WHERE matnr = ls_mat-matnr.

      SELECT SINGLE maktx FROM makt INTO ls_mat-maktx
        WHERE matnr = ls_mat-matnr
          AND spras = sy-langu.

      INSERT ls_mat INTO TABLE lt_mat.
    ENDIF.

    IF ls_mat-mtart = c_mtart.
      i_scr-extwg = ls_mat-extwg.
      i_scr-maktx = ls_mat-maktx.

      APPEND i_scr.
    ENDIF.
    
  ENDSELECT.

ENDLOOP.

It does not look very high performing, but in fact, it used only 1/2 an hour instead of 3 days of the version, that corresponds to yours. Yes, I know, a lot changed since 1.2 in the database interface, but just try it and you will see. And forget joining MKPF and MSEG. No database will handle this in a real system.

If you find my answer useful, please don't forget the reward.

Regards,

Juergen

Message was edited by: Juergen Wurth

Former Member
0 Kudos

Hi

I think the problem is the fields you're using for the query, they aren't key fields or used in the index.

For example: you should write:

mara~matnr IN s_matnr AND

.....

instead of

mseg~matnr IN s_matnr AND

Max

RichHeilman
Developer Advocate
Developer Advocate
0 Kudos

These are a couple of the biggest tables in the system. Hence, any query against them will take time. You may have to run this program in background. I will be looking at your SQL statement a little closer.

I may help if you were to not join to MAKT.

Regards,

Rich Heilman

Message was edited by: Rich Heilman

former_member723628
Active Participant
0 Kudos

Yes that's right these are some of the biggest tables in SAP. As far as your query is concerned SAP does not recommend to Join more then 3 tables at a time. I'd suggest you break the query in smaller parts. First fetch data from MKPF & then from MSEG using FOR ALL ENTRIES IN MKPF addition. Similarly you can fetch data from MARA & MAKT also.

Regards,

Gajendra Bhatt

Former Member
0 Kudos

Hi Gajendra,

MSEG has 13 million records. So select for all entries solution is going to degrade the preformance.

Thanks,

Sobhan.