Sometimes we experience bad performance while accessing the following SAP MM tables:
• MSEG – Document Segment: Material
• EBAN – Purchase Requisition
• RSEG – Document Item: Incoming Invoice
Consider the following NOTES mentioned in SAP Note 191492 while accessing SAP MM tables in order to improve the performance:
1. Access to material documents 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. 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.
2. Access to material documents 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. 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.Thefields 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 via the reservation number
Incorrect:
SELECT FROM EBAN WHERE EBELN = ....
AND EBELP = .... Correct:
SELECT FROM EKET WHERE EBELN = ....
AND EBELP = ....
SELECT FROM EBAN WHERE BANFN = EKET-BANFN
AND BANFPO = EKET-BANFPO. 4. Access to incoming invoices 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. 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.
Refer to SAP Note 191492 for more information.