Symptom
Customer-specific programs and program enhancements ("user exits") have a poor performance.The poor performance was observed when the following SAP MM/WM tables were accessed: MSEG, LTAP, EBAN, RSEG.
Reason and Prerequisites
However, note the following warnings:
- Before you productively use the alternatives proposed here, you must carefully check your program with respect to functional correctness.
Pay attention to the following note:
In the following examples you can often find accesses to several tables
Example:
SELECT * FROM LEIN WHERE = ...
SELECT * FROM LTAP WHERE LGNUM = LEIN-LGNUM and TANUM = LEIN-BTANR
...
It might be more favorable, with regard to the performance, to define a selection view on the corresponding tables (here: LEIN and LTAP ) in order to combine accesses to these two tables in one access.
In the following examples you can often find accesses to several tables
Example:
SELECT * FROM LEIN WHERE = ...
SELECT * FROM LTAP WHERE LGNUM = LEIN-LGNUM and TANUM = LEIN-BTANR
...
It might be more favorable, with regard to the performance, to define a selection view on the corresponding tables (here: LEIN and LTAP ) in order to combine accesses to these two tables in one access.
Solution
1. Accesses to transport requests
a) via the storage unit number
Incorrect: SELECT FROM LTAP WHERE VLENR = .... oder
SELECT FROM LTAP WHERE NLENR = ...
Correct:
SELECT FROM LEIN WHERE LENUM = ...
SELECT FROM LTAP WHERE LGNUM = LEIN-LGNUM and
AND TANUM = LEIN-BTANR
AND TAPOS = LEIN-BTAPS.
Incorrect: SELECT FROM LTAP WHERE VLENR = .... oder
SELECT FROM LTAP WHERE NLENR = ...
Correct:
SELECT FROM LEIN WHERE LENUM = ...
SELECT FROM LTAP WHERE LGNUM = LEIN-LGNUM and
AND TANUM = LEIN-BTANR
AND TAPOS = LEIN-BTAPS.
b) via the SD delivery note
Incorrect: SELECT FROM LTAP WHERE NLPLA = LIPS-VBELN
AND POSNR = LIPS-POSNR.Correct:
SELECT FROM VBFA WHERE VBELV = LIPS-VBELN
AND POSNV = LIPS- POSNR
AND VBTYP_N = 'Q'.
SELECT FROM LTAP WHERE LGNUM = LIPS-LGNUM
AND TANUM = VBFA-VBELN
AND TAPOS = VBFA-POSNN.
Incorrect: SELECT FROM LTAP WHERE NLPLA = LIPS-VBELN
AND POSNR = LIPS-POSNR.Correct:
SELECT FROM VBFA WHERE VBELV = LIPS-VBELN
AND POSNV = LIPS- POSNR
AND VBTYP_N = 'Q'.
SELECT FROM LTAP WHERE LGNUM = LIPS-LGNUM
AND TANUM = VBFA-VBELN
AND TAPOS = VBFA-POSNN.
Remark:
This access mode only exists if the transport orders were created on account of deliveries in Sales and Distribution. In this case the respective transport orders can be determined via the document flow table VBFA.
The field LTAP-NLPLA is filled with the delivery note number via the dynamic storage bin assignment. This automatic assignment can be deactivated in WM Customizing (see movement types in WMS). This side effect must be considered when field NLPLA is used for data selection.
This access mode only exists if the transport orders were created on account of deliveries in Sales and Distribution. In this case the respective transport orders can be determined via the document flow table VBFA.
The field LTAP-NLPLA is filled with the delivery note number via the dynamic storage bin assignment. This automatic assignment can be deactivated in WM Customizing (see movement types in WMS). This side effect must be considered when field NLPLA is used for data selection.
2. Access to material documents
a) via the purchase order number
Incorrect: SELECT FROM MSEG WHERE EBELN = ...
and EBELP = ...Correct:
SELECT FROM EKBE WHERE EBELN = ..
AND EBELP = ...
AND VGABE IN (1,6,7,8,9).
SELECT FROM MSEG WHERE MBLNR = EKBE-BELNR
AND MJAHR = EKBE-GJAHR
AND ZEILE = EKBE-BUZEI.
Incorrect: SELECT FROM MSEG WHERE EBELN = ...
and EBELP = ...Correct:
SELECT FROM EKBE WHERE EBELN = ..
AND EBELP = ...
AND VGABE IN (1,6,7,8,9).
SELECT FROM MSEG WHERE MBLNR = EKBE-BELNR
AND MJAHR = EKBE-GJAHR
AND ZEILE = EKBE-BUZEI.
Remark:
The fiscal year must be specified so that the system has effective access possibilities via the primary index.If the fiscal year is missing, the database can no longer effectively use the item number for the search (this is a problem, especially for material documents with many items).If the operation type VGABE is specified, the values can be additionally restricted to the corresponding goods movements that are relevant.
The fiscal year must be specified so that the system has effective access possibilities via the primary index.If the fiscal year is missing, the database can no longer effectively use the item number for the search (this is a problem, especially for material documents with many items).If the operation type VGABE is specified, the values can be additionally restricted to the corresponding goods movements that are relevant.
b) via the transport order number
Incorrect: SELECT FROM MSEG WHERE TANUM = ...Correct:
SELECT FROM LTAP WHERE TANUM = ...
SELECT FROM MSEG WHERE MBELN = LTAP-WENUM
AND MJAHR = <requested year>
AND ZEILE = LTAP-WEPOS.
Incorrect: SELECT FROM MSEG WHERE TANUM = ...Correct:
SELECT FROM LTAP WHERE TANUM = ...
SELECT FROM MSEG WHERE MBELN = LTAP-WENUM
AND MJAHR = <requested year>
AND ZEILE = LTAP-WEPOS.
c) via transfer requirement
Incorrect: SELECT MSEG WHERE TBNUM = ...Correct:
SELECT LTBK WHERE TBNUM = ....
SELECT MSEG WHERE MBLNR = LTBK-MBLNR
AND MJAHR = LTBK-MJAHR.
Incorrect: SELECT MSEG WHERE TBNUM = ...Correct:
SELECT LTBK WHERE TBNUM = ....
SELECT MSEG WHERE MBLNR = LTBK-MBLNR
AND MJAHR = LTBK-MJAHR.
Remark:
All items of the material document are returned. It is possible to have a restriction to one individual item of the material document by specifying the material number.
All items of the material document are returned. It is possible to have a restriction to one individual item of the material document by specifying the material number.
d) Via the vendor number
Incorrect: SELECT FROM MSEG WHERE LIFNR = ...Correct:
SELECT EKKO WHERE LIFNR = ....
SELECT EKBE WHERE EBELN = EKKO-EBELN
AND VGABE ='1'.
SELECT MSEG WHERE MBLNR = EKBE-BELNR
AND MJAHR = EKBE-GJAHR
AND ZEILE = EKBE-BUZEI.
Incorrect: SELECT FROM MSEG WHERE LIFNR = ...Correct:
SELECT EKKO WHERE LIFNR = ....
SELECT EKBE WHERE EBELN = EKKO-EBELN
AND VGABE ='1'.
SELECT MSEG WHERE MBLNR = EKBE-BELNR
AND MJAHR = EKBE-GJAHR
AND ZEILE = EKBE-BUZEI.
Remark:
Accesses to EKKO and EKBE return several datasets under certain circumstances. This must be taken into account in the program logic.With the operation type VGABE = 1, only goods movements for purchase orders are selected.
As an alternative you can use matchcode object M_MEKKL in place of table EKKO (for example SELECT FROM M_EKKL WHERE LIFNR = ...).Access can be improved by specifying additional restrictions.The fields purchasing organization EKORG, purchasing group EKGRP, document date BEDAT, purchasing document category BSTYP, order type BSART can make the access more selective.
Accesses to EKKO and EKBE return several datasets under certain circumstances. This must be taken into account in the program logic.With the operation type VGABE = 1, only goods movements for purchase orders are selected.
As an alternative you can use matchcode object M_MEKKL in place of table EKKO (for example SELECT FROM M_EKKL WHERE LIFNR = ...).Access can be improved by specifying additional restrictions.The fields purchasing organization EKORG, purchasing group EKGRP, document date BEDAT, purchasing document category BSTYP, order type BSART can make the access more selective.
3. Accesses to purchase requisitions
a) via the reservation number
Incorrect: SELECT FROM EBAN WHERE EBELN = ....
AND EBELP = ....
Correct:
SELECT FROM EKET WHERE EBELN = ....
EBELP = ....
SELET FROM EBAN WHERE BANFN = EKET-BANFN
AND BANFPO = EKET-BANFPO.
Incorrect: SELECT FROM EBAN WHERE EBELN = ....
AND EBELP = ....
Correct:
SELECT FROM EKET WHERE EBELN = ....
EBELP = ....
SELET FROM EBAN WHERE BANFN = EKET-BANFN
AND BANFPO = EKET-BANFPO.
4. Access to incoming invoices
a) via the purchase order number
Incorrect: SELECT FROM RSEG WHERE EBELN = ...
and EBELP = ...Correct: SELECT FROM EKBE WHERE EBELN = ...
AND EBELP = ...
AND VGABE IN (2,3,P).
SELECT FROM RSEG WHERE BELNR = EKBE-BELNR
AND GJAHR = EKBE-GJAHR
AND BUZEI = EKBE-BUZEI.
Incorrect: SELECT FROM RSEG WHERE EBELN = ...
and EBELP = ...Correct: SELECT FROM EKBE WHERE EBELN = ...
AND EBELP = ...
AND VGABE IN (2,3,P).
SELECT FROM RSEG WHERE BELNR = EKBE-BELNR
AND GJAHR = EKBE-GJAHR
AND BUZEI = EKBE-BUZEI.
Remark:
By specifying transaction type VGABE, the values are restricted to the relevant goods movements. With GJAHR and BUZEI, the primary index is completely utilized by RSEG.
By specifying transaction type VGABE, the values are restricted to the relevant goods movements. With GJAHR and BUZEI, the primary index is completely utilized by RSEG.