MSEG Performance Issue

[Former Member]

Hi all,

I am trying to build up a table containing

MKPF-BUDAT and a number of fields from MSEG....

The user wants to select a range of 12 months worth of data. This is running extremely slow, the code is shown below:-

* Get all mat docs headers for date
* wa_firsdat wa_lasdat selected from user (calculated from function
  SELECT b~mblnr b~mjahr a~budat b~zeile b~bwart b~matnr
                                 b~werks b~menge b~meins b~kostl
    FROM mkpf AS a
   INNER JOIN mseg AS b
      ON a~mblnr = b~mblnr
     AND a~mjahr = b~mjahr
    INTO CORRESPONDING FIELDS OF TABLE wt_mseg
   WHERE a~budat BETWEEN wa_firdat AND wa_lasdat
     AND a~mblnr BETWEEN '4900000000' AND '5999999999'
     AND b~zeile BETWEEN 1 AND 9999
     AND b~bwart = wa_move.

  IF NOT p_werks IS INITIAL.                "Delete invalid plants
    DELETE wt_mseg WHERE NOT werks = p_werks.
  ENDIF.

  IF NOT s_kostl IS INITIAL.                "Delete invalid CostCentres
    DELETE wt_mseg WHERE NOT kostl IN s_kostl.
  ENDIF.

  IF wt_mseg IS INITIAL.
    MESSAGE e999 WITH 'No values for this selection'.
  ENDIF.

Is there any way I can improve the performance of this code?

Thanks

[former_member434229]

Hi,

Define the table same way as you retrieve respective fields like:

Data : begin of wt_mseg occurs 0,

mblnr TYPE MBLNR,

mjahr type MJAHR,

budat type BUDAT,

zeile type MBLPO,

bwart type bwart,

matnr type matnr,

werks type WERKS_D,

menge type MENGE_D,

meins type MEINS,

kostl type KOSTL,

end of wt_mseg.

& Write the Select statement in following manner:

SELECT b_{mblnr b}mjahr a_{budat b}zeile b_{bwart b}matnr

b_{werks b}menge b_{meins b}kostl

FROM mkpf AS a

INNER JOIN mseg AS b

ON a_{mblnr = b}mblnr

AND a_{mjahr = b}mjahr

INTO TABLE wt_mseg

WHERE a~mblnr BETWEEN '4900000000' AND '5999999999'

AND a~budat BETWEEN wa_firdat AND wa_lasdat

AND b~zeile BETWEEN 1 AND 9999

AND b~bwart = wa_move

AND b~werks = p_werks

AND b~KOSTL in s_KOSTL.

If wt_mseg is initial.

messgae

endif.

Regards,

Ni3

Edited by: Ni3 on Mar 11, 2010 5:30 PM

Edited by: Ni3 on Mar 11, 2010 5:34 PM

[Former Member]

since KOSTL is a requirement for the SELECT, I would first see if you can work table COEP into it using the cost center in the object number. This will add to the programming effort, but may speed up what you are trying to do.

Rob

[Former Member]

thanks guys - i will have a look at that - i think i am going to be stumped as the business wants to bring back such a large date range....

So you definitely think adding plant and cost center to the select query is better than deleting from the table afterwards?

Edited by: SpLuFF on Mar 11, 2010 3:44 PM

[Former Member]

If s_kostl is small, that should reduce the amount of data returned significantly.

Rob

[Former Member]

i definitely know that only 1 plant will be selected.

[Former Member]

KOSTL is the cost center, not the plant.

Rob

[Former Member]

yes i know that just the first answer suggested moving plant to the select query and i know that will definitely only be one.

[ThomasZloch]

I don't have a solution (yet) to speed this up, but it does not make sense to read data from the database only to delete it afterwards from memory. Include both select options in your select query as suggested by "Ni3" already.

Thomas

[HermannGahm]

Hi,

it can be really tough to optimize mkpf / mseg joins.

But lets start with the general join optimization approach:

WHERE a~mblnr BETWEEN '4900000000' AND '5999999999'

AND a~budat BETWEEN wa_firdat AND wa_lasdat

AND b~zeile BETWEEN 1 AND 9999

AND b~bwart = wa_move

AND b~werks = p_werks

AND b~KOSTL in s_KOSTL.

check in SE16 with the where conditions given for each table what

the result set is. Check (ST05 execution plan) if the optimizer starts with the table that has

the smaller result set.

If this is not the case you can work on that to convince the optimizer to

choose the smaller resultset first. This is basically done with providing more

information to the optimzer or use a hint.

If the optimizer starts already with the smaller result set it starts to

get really difficult....

But first check if you already start with the right table...

Kind regards,

Hermann

[Former Member]

Definitely follow Hermann's suggestion to check the result sets for each table individually via SE16(n). Count the number of entries returned and compare it against the total number of entries. Do a SQL trace on your join and see what execution plan is picked.

As Hermann mentioned, you want to make sure that a proper join type is selected (e.g. on Oracle nested loop, hash join, sort merge join). Also, if you're returning a large percentage of the table it is sometimes beneficial to skip any index access and do a full table scan instead (ideally the database makes the right choice, but it doesn't hurt to experiment). Use database hints to push the database to choose other execution plans and compare the results.

MPKF should have primary key and at least the secondary index on BUDAT/MBLNR. I'd toy also with the conditions and try omitting some (e.g. does the database choose different execution plan if you omit the catch-all condition on ZEILE). Since you want one year of data, I'd try adding a condition on MJAHR (derive via program from BUDAT condition); if the primary key on MKPF is beneficial and used then you'd skip some entries by specifying this. So this seems promising to me.

I checked the (default) indexes on MSEG in ECC6.0, but there seems to be nothing that supports your MSEG conditions well (might be different in your system). So I'd assume driving table should be MKPF and then data is read for those from MSEG - oops, did I really make such a stupid statement without testing it?! ....

Cheers, harald

p.s.: It's kind of surprising how often the business wants to see those huge datasets. Usually you'd expect that some aggregation is necessary, because who can look at those tons of rows. So this sounds more like the kind of query that ideally is done in BW/BI if present...

[Former Member]

I have tested using a smaller data set and get the following:-

For the original SQL:-

T_01 RANGE CONDITION FOR KEY 1197278

MANDT (USED KEY COLUMN)

MBLNR (USED KEY COLUMN)

T_00 JOIN VIA MULTIPLE KEY COLUMNS 39200

MANDT (USED KEY COLUMN)

MBLNR (USED KEY COLUMN)

MJAHR (USED KEY COLUMN)

NO TEMPORARY RESULTS CREATED

RESULT IS COPIED , COSTVALUE IS 1198155

For the modified with the plant and cost center:-

T_00 MKPF~BUD RANGE CONDITION FOR INDEX 39200

ONLY INDEX ACCESSED

MANDT (USED INDEX COLUMN)

BUDAT (USED INDEX COLUMN)

MBLNR (USED INDEX COLUMN)

MANDT (USED KEY COLUMN)

MBLNR (USED KEY COLUMN)

T_01 JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 1197278

MANDT (USED KEY COLUMN)

MBLNR (USED KEY COLUMN)

MJAHR (USED KEY COLUMN)

NO TEMPORARY RESULTS CREATED

SHOW RESULT IS COPIED , COSTVALUE IS 638

Also in prod I get the following counts:-

No of entries:-

zeile(1 - 9999) = 24,185,120

bwart(551) = 90,242

werks(1500) = 18,860,804

[HermannGahm]

Hi,

please post the number of entries for

MKPF:

WHERE a~mblnr BETWEEN '4900000000' AND '5999999999'

AND a~budat BETWEEN wa_firdat AND wa_lasdat

and for MSGE:

WHERE

AND b~zeile BETWEEN 1 AND 9999

AND b~bwart = wa_move

AND b~werks = p_werks

AND b~KOSTL in s_KOSTL.

not for the single fields but for the combination.

Looking at this plan:

T_00 MKPF~BUD RANGE CONDITION FOR INDEX 39200

ONLY INDEX ACCESSED

MANDT (USED INDEX COLUMN)

BUDAT (USED INDEX COLUMN)

MBLNR (USED INDEX COLUMN)

MANDT (USED KEY COLUMN)

MBLNR (USED KEY COLUMN)

T_01 JOIN VIA RANGE OF MULTIPLE KEY COLUMNS 1197278

MANDT (USED KEY COLUMN)

MBLNR (USED KEY COLUMN)

MJAHR (USED KEY COLUMN)

NO TEMPORARY RESULTS CREATED

SHOW RESULT IS COPIED , COSTVALUE IS 638

we start on MKPF using the BUDAT.

is the number of rows in MKPF for this condition:

WHERE a~mblnr BETWEEN '4900000000' AND '5999999999'

AND a~budat BETWEEN wa_firdat AND wa_lasdat

less than the number of rows in MSEG for this condition

WHERE

AND b~zeile BETWEEN 1 AND 9999

AND b~bwart = wa_move

AND b~werks = p_werks

AND b~KOSTL in s_KOSTL.

And in general: how much rows are selected in total and how long does it take?

Kind regards,

Hermann

[Former Member]

MKPF:

WHERE a~mblnr BETWEEN '4900000000' AND '5999999999'

AND a~budat BETWEEN wa_firdat AND wa_lasdat

This takes 5 minutes and returns 607954 records

and for MSGE:

WHERE

AND b~zeile BETWEEN 1 AND 9999

AND b~bwart = wa_move

AND b~werks = p_werks

AND b~KOSTL in s_KOSTL.

This runs for 30minutes and times out so dont have record count

[Former Member]

As I said before, have a look at COEP.

Rob

[Former Member]

Hi Rob,

I was just replying to the questions before...

I had a look at COEP but that table is huge with millions of records in it. I cant see how that would speed up the query. Can you please explain?

Thnx

[Former Member]

I may be that the view COVP which joins COEP and COBK may be better suited to your purposes.

The number of records in a table is essentially not relevant to performance. What matters is how you access the records. Table COEP is the main table for CO related line items. And can be accessed quickly using the index COEP~1 (LEDNR and OBJNR). The LEDNR is always "00' (you'll want to verify this in your system) and the OBJNR is made up of concatenating 'KS', the co area and the cost center. It can be made up of other things as well, but this should work in your case.

The problem will in relating the CO line item back to the material document that you want, and I am not sure this will be possible here. It may depend on the configuration in your system. You can select some records from COEP and see if you can find a relationship between the CO document and the material document - maybe the reference document REFBN in COBK.

Rob