Goods receipt report

[Former Member]

Hello,

I wrote a report regarding materials that don't have a goods receipt in the last x days. It works , but I am not satisfied by the performance of the report.

First, I have selected all the materials that I consider as active in the sistem.

SELECT DISTINCT mara~matnr FROM mara

JOIN marc ON mara_{matnr = marc}matnr

JOIN mard ON mara_{matnr = mard}matnr

INTO CORRESPONDING FIELDS OF TABLE T_MATNR

WHERE mara~matnr IN so_matnr

AND marc~werks = p_plant

AND marc~ekgrp IN s_raion

AND mara~lvorm <> 'X'

AND mard~labst <> 0

ORDER BY mara~matnr.

After this selection I am verifiy each material selected before if it has a goods receipt in my conditions.

LOOP AT t_matnr INTO s_matnr.

SELECT single mseg_{matnr mkpf}budat FROM mseg

JOIN mkpf ON mseg_{mblnr = mkpf}mblnr

INTO CORRESPONDING FIELDS OF TABLE T_MATNRX

WHERE matnr = s_matnr-matnr AND bwart = '101'

AND mseg~werks = p_plant

AND mkpf~budat > my_date

ORDER BY budat DESCENDING.

DELETE t_matnrx FROM 3.

IF t_matnrx[] IS INITIAL.

APPEND s_matnr TO t_matnry.

ENDIF.

ENDLOOP.

This takes very long, because I have ~ 40000 items in MARA. What can I do to improove the performance?

[Former Member]

Hi,

Negative conditions in WHERE clause are very harmful for performance as it bypass the indexes on the table.

SELECT DISTINCT mara~matnr FROM mara

JOIN marc ON mara_{matnr = marc}matnr

JOIN mard ON mara_{matnr = mard}matnr

INTO CORRESPONDING FIELDS OF TABLE T_MATNR

WHERE mara~matnr IN so_matnr

AND marc~werks = p_plant

AND marc~ekgrp IN s_raion

AND mara~lvorm <> 'X'

AND mard~labst <> 0

ORDER BY mara~matnr.

remove AND mara~lvorm <> 'X'

AND mard~labst <> 0 from where clause.

select all the records and then use delete itab where....

Secondly dont call select single inside the loop.

Try using joins or FOR ALL ENTRIES.

Hope it helps.

Regards,

Shashank

[Former Member]

first try to arrenge the field in internal table in which way u fetch it .than u can remove into corresponding field.

SELECT DISTINCT mara~matnr FROM mara

JOIN marc ON mara_{matnr = marc}matnr

JOIN mard ON mara_{matnr = mard}matnr

INTO TABLE T_MATNR

WHERE mara~matnr IN so_matnr

AND marc~werks = p_plant

AND marc~ekgrp IN s_raion

AND mara~lvorm <> 'X'

AND mard~labst <> 0.

sort t_matnr by matnr.

if not t_matnr[] is inital.

SELECT single mseg_{matnr mkpf}budat FROM mseg

JOIN mkpf ON mseg_{mblnr = mkpf}mblnr

INTO TABLE T_MATNRX for all entries of t_matnr

WHERE matnr = s_matnr-matnr AND bwart = '101'

AND mseg~werks = p_plant

AND mkpf~budat > my_date

ORDER BY budat DESCENDING.

DELETE t_matnrx FROM 3.

IF t_matnrx[] IS INITIAL.

APPEND s_matnr TO t_matnry.

ENDIF.

[Former Member]

Hello George,

Couple of options..

1. If u Logistic information system operational u can try some reports already provided by SAP like MC.A. or u can create ur own to suit ur requriement.

2. If what u have put as code is part of bigger picture then I would suggest u access MKPF MSEG and MARA instead of MARD/MARC.

3. Another option is using views...u can create view of MKPF/MSEG and MARA/MARC/MARD and use them in the select stmt. This will be much more effective as views are internally manged by database and hence will optimise the aceess to them.