Performance issues in EKKO, MKPF, MSEG

[Former Member]

Hi All

I am using tables EKKO, MSEG & MKPF in report.

But it is taking lot of time in Quality.

I have removed all the joins & using Secondary Indexes but then also its creating problem.

  SELECT ebeln lifnr bedat
  INTO CORRESPONDING FIELDS OF TABLE it_ekko
  FROM ekko
  WHERE bedat IN so_date.

  IF it_ekko[] IS NOT INITIAL.

    SELECT mblnr budat
    INTO CORRESPONDING FIELDS OF TABLE it_mkpf
    FROM mkpf
    WHERE budat IN so_date
    %_HINTS ORACLE 'INDEX("MKPF" "MKPF~ZVN")'.

    IF it_mkpf[] IS NOT INITIAL.
      SELECT lifnr mblnr
      INTO CORRESPONDING FIELDS OF TABLE it_mseg
      FROM mseg
      WHERE mblnr = it_mkpf-mblnr
      %_HINTS ORACLE 'INDEX("MSEG" "MSEG~ZVD")'.
    ENDIF.


  ELSE.

    SELECT mblnr budat
    INTO CORRESPONDING FIELDS OF TABLE it_mkpf
    FROM mkpf
    WHERE budat IN so_date
    %_HINTS ORACLE 'INDEX("MKPF" "MKPF~ZVN")'.

    IF it_mkpf[] IS NOT INITIAL.
      SELECT lifnr mblnr
      INTO CORRESPONDING FIELDS OF TABLE it_mseg
      FROM mseg
      for all entries in it_mkpf
      WHERE mblnr = it_mkpf-mblnr
      %_HINTS ORACLE 'INDEX("MSEG" "MSEG~ZVD")'.
    ENDIF.


  ENDIF.

Please help me.

Regards.

[GauthamV]

hi,

Try to use all primary keys in select statement as much as possible.

[SuhaSaha]

Hello Nikhil,

You are selecting data from EKKO, MKPF & MSEG w/o using the primary keys; but i see that you have created some custom indices for the same.

My suggestion will be to remove INTO CORRESPONDING FIELDS OF TABLE from the SELECT stmts.

I have a qn, in the stmt:

    IF it_mkpf[] IS NOT INITIAL.
      SELECT lifnr mblnr
      INTO CORRESPONDING FIELDS OF TABLE it_mseg
      FROM mseg
      WHERE mblnr = it_mkpf-mblnr
      %_HINTS ORACLE 'INDEX("MSEG" "MSEG~ZVD")'.
    ENDIF.

you have not used FOR ALL ENTRIES ) I think this is a typo )

BR,

Suhas

[Former Member]

Your coding seems to be fine with me. Since you have retrieved the values based on some date, you can't do anything....

Is that possible to add additional selection criteria, such as material, plant or something

If not possible you have to execute the report by background only that's the only option

Not much with performance.

Regards

Sasi

[Former Member]

Thanks All..

But in Quality its running so low.. I mean its not running at all...

Any more suggestions?

Regards.

[Former Member]

Schedule a background job.

Sasi

[Former Member]

Hi,

Replace into corresponding fields of by into table.....by redifining the Internal Table structures.

You can try creating Secondary Index on the non Primary Key fields used in the Where Clause.

[Former Member]

Hi

I have already replaced "into corr fields" & used "fo all entries".. but no use.

Regards.

[Former Member]

Hi..

Before using 'For All Entries' ... use 'Delete Adjacent Duplicates' also..

and

do not use into corresponding in select query.. better... to declare an internal table

with the fields you want and then use 'Into table t_itab'........

Also ensure that the order of the fields selected from the database tables are same as the order of the fields present in the database tables..

Cheeers..

[Former Member]

Split So_date into two/three and than execute, Let us say if So_date is 01/12/2008 to 30/12/2008 split into two/3 ranges and execute

Regards

Sasi

[Former Member]

Hi,

The selection will take time depending on the values you specify in the select options so_data ( assuming this is a select-option). If you leave this blank, the queries on EKKO and MKPF will run forever.

You should therefore make some more filters on EKKO. Perhaps EKKO with EKPO so that you get the material number as well. Then you can select data from MSEG first using the index M 'Material documents for material'. Once you have the data from MSEG, select corresponding entries from MKPF using the MSEG key.

P.S MSEG table also has the EBELN and EBELP fields, use them as well in the where clause while selecting the MSEG table along with the material number.

regards,

Advait

Edited by: Advait Gode on Dec 29, 2008 1:24 PM

[Former Member]

Hi Advait

Actually my requirement is to get those vendors who didn't have any PO/PR(EKKO) & GR(MSEG,MKPF).

I think there is no use of fetching material!!

Regards.

[Former Member]

Hi Nikhil,

Actually my requirement is to get those vendors who didn't have any PO/PR(EKKO) & GR(MSEG,MKPF).I think there is no use of fetching material!!

I understand that, but in order to select the data faster from MSEG, you will have to use some more filters in the where clause. I don't think specifying the join conidtion if sufficient for the secondary index to be used.

You have worked out on a join , try to analyze the trace in ST05 and find out if the indices that you mentioned are being used at all.

regards,

Advait

[ThomasZloch]

> Actually my requirement is to get those vendors who didn't have any PO/PR(EKKO) & GR(MSEG,MKPF).

I have a feeling that this could be solved in a better way altogether, but at least include BSTYP = "F" (POs) in the WHERE-conditions of your select statement for EKKO, so index "EKKO~D" can be used.

MKPF has an index "MKPF~BUD" for BUDAT already, why did you create another one?

Also include MJAHR when linking MKPF and MSEG.

Thomas

[Former Member]

Thanks Thomas

I will definately try this.

Regards.

[former_member194613]

> have removed all the joins & using Secondary Indexes but then also its creating problem.

Why did you do that ???

A last statement to this issue in the old year, there are about 5-10 people in this forum, who earn a lot of points answer a lot of question and all of them would never recommend a FOR ALL ENTRIES over a join. I would include myself to this group.

There are lots of other people who write 'Use FOR ALL ENTRIES instead of JOINS'. However, the earn alltogether not more than the poeple of the other group.

So whom would you trust?

If you use hints, then it should be possible to get a good working join. A good join is in most cases better then the FOR ALL ENTRIES, sometimes dramatically better.

Overall if you don't specify your indexes, then it does not make sense to ask, your twoi indexes are not standard, so how do they look like???

Siegfried

[Former Member]

Hi Siegfried

Thanks for replying.

Its not matter of trust, its about my report's performance!!

However I have used both 'joins' & 'for all entries', but no results.

I am using In secondary index for

EKKO - bedat

MSEG - mblnr

MKPF - budat, mblnr

Instead of those 3 select statements, (According to you) Should I use

  SELECT a~lifnr a~ebeln
         b~mblnr
         c~budat
  INTO CORRESPONDING FIELDS OF TABLE it_ekko
  FROM ekko AS a
  INNER JOIN mseg AS b
  ON a~lifnr = b~lifnr
  INNER JOIN mkpf AS c
  ON b~mblnr = c~mblnr
  WHERE a~bedat IN so_date
    AND c~budat IN so_date.

Regards.

[Former Member]

Hi,

1. Try to use all primary keys in select statement as much as possible.

2. Remove INTO CORRESPONDING FIELDS OF TABLE from the SELECT statements.Instead of using into corresponding in select statement use internal table with the fields.

3. Use for all entries statement. Before using 'For All Entries' ... use 'Delete Adjacent Duplicates' .

Regards,

Jyothi CH.

[Former Member]

Thanks All for the suggestions.

But nothing worked.

Regards.