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:
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 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.