Hi, I am trying to improve the performance of a long-running report which accesses Sales Orders over the last four years.
Originally the select was an Inner Join on VBAK & VBAP FOR ALL ENTRIES in a Material Table of 100+ materials using the following criteria:
VKORG - just one selected always
SPART - always same four selected
ERDAT - greater than a set date four years ago
MATNR - FOR ALL ENTRIES in a table of 100+ materials
VBELN - certain sales orders excluded where sales team have screwed up!
ABGRU - set to blank
KUNNR - NOT LIKE 'HUK%'
Now I had heard that using "LIKE" was death to SAP's SQL optimizer, and this report was only interested in sales orders for a small part of the overall business beyond a certain date so I created a view for the following restrictions:
Tables: VBAK & VBAP & VBUP - Sales Item status is used later in the program...
VKORG for the req'd Sales Org
SPART for the req'd Divisions
ERDAT beyond a certain date
This, I thought, would cut down on a lot of data and so it proved....
My view has just 85,000 entries as opposed to 4,125,000.
After retrieving the data into the internal table I then delete where KUNNR NOT LIKE 'HUK%'....
This works okay and looks as follows:
SELECT vbeln erdat auart vdatu kunnr
posnr matnr kwmeng lfsta spart
INTO CORRESPONDING FIELDS OF TABLE v_order_tab
FROM yv162
WHERE spart IN s_spart AND
auart IN s_auart AND Excluding 5 Types
vbeln IN s_vbeln AND Excluding 4 values
erdat IN s_erdat AND
erdat LE v_date_to AND
matnr IN s_matnr AND
abgru = ' '.
This still results in a sequential search....
Now I have used Hints before with joins but I'm sure you can't use them with Views?
Does any have any ideas other than removing the CORRESPONDING which I'm already aware of.
I have considered just taking the whole table into an internal table and then just deleting from that internal table according to Sales Order type, creation date, etc
What does everyone think?
Thanks in advance,
Jonathan Smith
Bluesky
