Symptom
Customer programs and program enhancements ("user exits") have a bad performance. The bad performance can be seen when accessing the following SAP logistic tables:
AFRU, AFKO, AUFK, CAUFV, RESB
AFRU, AFKO, AUFK, CAUFV, RESB
Additional key words
Cause and prerequisites
The R/3 System does not contain secondary indexes to the most important PP / PM flow tables.
To access them effectively you need a basic knowledge of the SAP data model. This notes contains information on the most important errors and provides alternatives.
However, note the following WARNINGS:
- Before you use the suggested alternatives check your program for functional correctness.
Pay attention to the following NOTES:
In the following examples, you often find accesses to several
e.g..:
SELECT aufnr FROM resb WHERE rsnum = <afko-rsnum>
SELECT ..... FROM afko WHERE aufnr = <resb-aufnr>
To increase the performance you should define a selection view on the corresponding tables (here: resb and afko) to combine the accesses to both table into one.
To access them effectively you need a basic knowledge of the SAP data model. This notes contains information on the most important errors and provides alternatives.
However, note the following WARNINGS:
- Before you use the suggested alternatives check your program for functional correctness.
Pay attention to the following NOTES:
In the following examples, you often find accesses to several
e.g..:
SELECT aufnr FROM resb WHERE rsnum = <afko-rsnum>
SELECT ..... FROM afko WHERE aufnr = <resb-aufnr>
To increase the performance you should define a selection view on the corresponding tables (here: resb and afko) to combine the accesses to both table into one.
Solution
1. Access to order confirmation (table AFRU)
a) Search for confirmation of orders (field AFRU-AUFNR):
Incorrect:
SELECT FROM afru WHERE aufnr = ...
Correct:
SELECT aufpl FROM afko WHERE aufnr = <afru-aufnr>
SELECT rueck FROM afvc WHERE aufpl = <afko-aufpl>
SELECT ..... FROM afru WHERE rueck = <afvc-rueck>
Caution:
From table AFVC, you receive one value per operation for "RUECK".
Incorrect:
SELECT FROM afru WHERE aufnr = ...
Correct:
SELECT aufpl FROM afko WHERE aufnr = <afru-aufnr>
SELECT rueck FROM afvc WHERE aufpl = <afko-aufpl>
SELECT ..... FROM afru WHERE rueck = <afvc-rueck>
Caution:
From table AFVC, you receive one value per operation for "RUECK".
2. Access to PPS orders (tables AFKO, CAUFV, AUFK)
a) Find orders for reservation (field AFKO-RSNUM; CAUFV-RSNUM):
Incorrect:
SELECT FROM afko WHERE rsnum = ...
Correct:
SELECT aufnr FROM resb WHERE rsnum = <afko-rsnum>
SELECT ..... FROM afko WHERE aufnr = <resb-aufnr>
Note:
The same access path applies to CAUFV and AUFK.
Search for sales orders (tables VBAP-VBELN, VBAP-POSNR; VBFA-VBELN, VBFA-POSNN; and so on.)
Incorrect:
SELECT FROM AUFK WHERE KDAUF = <VBAP-VBELN>
AND KDPOS = <VBAP-POSNR>
Correct:
SELECT FROM AFPO WHERE KDAUF = <VBAP-VBELN>
AND KDPOS = <VBAP-POSNR>
SELECT FROM AUFK WHERE AUFNR = AFPO-AUFNR.
Note:
The tables AFKO and CAUFV can be selected analogously to the AUFK access using order number AUFNR.
Incorrect:
SELECT FROM afko WHERE rsnum = ...
Correct:
SELECT aufnr FROM resb WHERE rsnum = <afko-rsnum>
SELECT ..... FROM afko WHERE aufnr = <resb-aufnr>
Note:
The same access path applies to CAUFV and AUFK.
Search for sales orders (tables VBAP-VBELN, VBAP-POSNR; VBFA-VBELN, VBFA-POSNN; and so on.)
Incorrect:
SELECT FROM AUFK WHERE KDAUF = <VBAP-VBELN>
AND KDPOS = <VBAP-POSNR>
Correct:
SELECT FROM AFPO WHERE KDAUF = <VBAP-VBELN>
AND KDPOS = <VBAP-POSNR>
SELECT FROM AUFK WHERE AUFNR = AFPO-AUFNR.
Note:
The tables AFKO and CAUFV can be selected analogously to the AUFK access using order number AUFNR.
3. Access to reservations and dependent requirement (table: RESB, MDRS ATP _RESB)
a) Search for reservation for PPS orders (field RESB-AUFNR):
Incorrect:
SELECT FROM resb WHERE AUFNR = ...
Correct:
SELECT rsnum FROM afko WHERE aufnr = <resb-aufnr>
SELECT ..... FROM resb WHERE rsnum = <afko-rsnum>
Note:
The same access path applies to MDRS and ATP_RESB.
Incorrect:
SELECT FROM resb WHERE AUFNR = ...
Correct:
SELECT rsnum FROM afko WHERE aufnr = <resb-aufnr>
SELECT ..... FROM resb WHERE rsnum = <afko-rsnum>
Note:
The same access path applies to MDRS and ATP_RESB.
b) Search for reservation for Planned orders (field RESB-PLNUM):
Incorrect:
SELECT FROM resb WHERE PLNUM = ...
Correct:
SELECT rsnum FROM plaf WHERE plnum = <resb-plnum>
SELECT ..... FROM resb WHERE rsnum = <plaf-rsnum>
Note:
The same access path applies to MDRS and ATP_RESB.
Incorrect:
SELECT FROM resb WHERE PLNUM = ...
Correct:
SELECT rsnum FROM plaf WHERE plnum = <resb-plnum>
SELECT ..... FROM resb WHERE rsnum = <plaf-rsnum>
Note:
The same access path applies to MDRS and ATP_RESB.