Skip to Content
0
Former Member
Sep 21, 2011 at 11:53 AM

Optimization of Join statement between MKPF and MSEG table for improving pe

776 Views

Hi All,

I had a issue where we are executing one custom report and it is getting timed out after 45 minutes. We further executed with trace on and got to the conclusion with the help of BASIS that about 42 minutes of the 45 minutes tracetime are spent by a join over the tables MKPF en MSEG.

This join is done by the abap statement:

SELECT mkpf~mblnr

mkpf~mjahr

mkpf~bldat

mkpf~blart

mseg~matnr

mseg~werks

INTO CORRESPONDING FIELDS OF TABLE i_matdoc_we

FROM mkpf INNER JOIN mseg ON mkpfmblnr = msegmblnr

AND mkpfmjahr = msegmjahr

FOR ALL entries IN i_list

WHERE mkpf~mblnr BETWEEN lv_interval-fromnumber

AND lv_interval-tonumber

AND mkpf~blart = 'WE'

AND mkpf~bldat LE gv_last_day_fisc_period

AND mseg~matnr = i_list-matnr

AND mseg~werks IN s_werks.

Here, I_LIST comprises of stock for specific period as entered in the selection screen for that particular Material and plant

LV_INTERVAL is range of all goods receipts for material and gv_last_day_fisc_period is current date.

During the tracetime this statement was executed more than 20.000 times, until the transaction timed out.

The individual executions of this select stmnt varied (roughly) between 50 and 1200 miliseconds. (depends wheter the requested database block is in the buffercache or must be read from disk).

The acesspath used to execute the query ( see below) is optimal, given the present indexes.

Index MSEG~M is covering al the specified selection criteria for MSEG.

Index MKPF-0 is used to search the specified MBLNR criterium in MKPF.

The remaining selection criteria MKPF-BLART and MKPF-BLDAT are not indexed, and imho this would also not make sense for this query.

BLART has only 3 different values n the MKPF table, and the selected value 'WE' has more than 1 million occurences.

BLDAT is selected with =<20110903, so this is not distinctive as well.

Can any one suggest some better way to write this query.

Regards,

Subhajit