There's a bit of a science to deciding which is the driving table. Normally in a header/item situation you would expect the header to be the driving table, however, in this SQL you have neither of the tables primary keys and therefore unless you have a secondary index created you will probably be performing a full-table scan of either the header or item table. Needless to say it's better to full-table scan against a header table than item. :]
It's very uncommon not to have any secondary indicies against either tables VBAK or VBAP, or both. We have 5 against VBAK (2 SAP and 3 custom) and 1 against VBAP, for example. If you have any of these secondary indicies it would be helpful to know what fields they are against. Without knowing the details we can only make a best guess at the most effecient way of creating your SQL statement.
Assuming that you have the two standard SAP secondary indicies (AUD & ERD) against table VBAK then we might be able to use these.
Can s_date refer to the header date rather than item date?. If so we could use this format for the SQL, maybe:
Code: Select all
SELECT vbap~vbeln vbap~spart vbap~prctr vbap~kwmeng vbap~vrkme
INTO TABLE i_vbap
INNER JOIN vbap ON vbap~vbeln EQ vbak~vbeln
WHERE vbak~erdat IN s_date "Secondary index ERD
AND vbak~auart IN s_stype
AND vbak~spart IN s_spart
AND vbap~werks IN s_werks
AND vbap~prctr IN s_prctr
AND vbap~ernam IN s_user
AND vbap~abgru EQ space.
Worth a try I guess. This particular query depends a great deal on how many sales docs you create per day. If it's millions, then it's back to the drawing board I'm afraid. On average it's probably going to be just a few hundred, so it should be reasonably quick. Check the code produced using ST05 and look at the explain path to ensure it's using the ERD index for the VBAK access.