01-13-2015 6:17 PM
Hi All,
I have been facing a performance issue in the below query . Please let me know your suggestions. The number of records in ITAB is around 7500.
SELECT k~vbeln k~auart k~vkorg p_posnr
FROM vbak AS k
INNER JOIN vbap AS p ON p~vbeln = k~vbeln
INTO TABLE it_output
FOR ALL ENTRIES IN ITAB
WHERE K~vbeln ne itab-vbeln
AND k~kunnr = itab-kunag
AND k~vkorg = itab-vkorg
AND k~bstnk = itab-bstnk. =>Timeout error
I have tried to put ITAB-VBELN values in a range table and tried with the following query. Still the same issue.
SELECT k~vbeln k~auart k~vkorg p_posnr
FROM vbak AS k
INNER JOIN vbap AS p ON p~vbeln = k~vbeln
INTO TABLE it_output
FOR ALL ENTRIES IN ITAB
WHERE K~vbeln not in ITAB_VBELN_RANGE_TABLE
AND k~kunnr = itab-kunag
AND k~vkorg = itab-vkorg
AND k~bstnk = itab-bstnk.
I have tried the following variation. But no improvement.
SELECT k~vbeln k~auart k~vkorg p_posnr
FROM vbak AS k
INNER JOIN vbap AS p ON p~vbeln = k~vbeln
INTO TABLE it_output
FOR ALL ENTRIES IN ITAB
WHERE k~kunnr = itab-kunag
AND k~vkorg = itab-vkorg
AND k~bstnk = itab-bstnk.
DELETE it_output WHERE vbeln in ITAB_VBELN_RANGE_TABLE.
Thanks in advance.
01-14-2015 8:01 AM
Can you describe the required selection logic? Having an ITAB with coherent rows of selection values, of which three represent an EQ and a forth one an NE condition seems unusual to me.
VAPMA is not the right one, you can look at table VAKPA to replace VBAK in your selection, this one is sorted by partner numbers.
Thomas
01-13-2015 6:52 PM
hi.
the sentence "For all entries in..." does not work with tables overs 1.000 data...the performance goes down a lot and dump.
Try to avoid it, maybe make another join using the itab...or loop
Regards
Miguel
01-13-2015 7:00 PM
FOR ALL ENTRIES with large tables is fine. The real problem is with the NE ITAB-VBELN. The NE means that you probably won't use an index.
You can leave that condition off the SELECT and than process the internal table to get rid of entries you don't want.
Rob
01-13-2015 7:35 PM
I spoke too soon. I see your option 3 is approximately what I am suggesting. There is an index on KUNNR, but it is not active.
Rob
01-14-2015 6:29 AM
SELECT k~vbeln k~auart k~vkorg p_posnr
FROM vbak AS k
INNER JOIN vbap AS p ON p~vbeln = k~vbeln
INTO TABLE it_output
FOR ALL ENTRIES IN ITAB
WHERE K~vbeln ne itab-vbeln
AND k~kunnr = itab-kunag
AND k~vkorg = itab-vkorg
AND k~bstnk = itab-bstnk. =>Timeout error
do not use “NE”
1st, get all the data
2nd, delete data according to itab
01-14-2015 7:26 AM
You can try for sub query. But not sure of the performance. Sample code below.
Select * into table lt_agr_users
from agr_users
where agr_name = any ( select distinct role from ZTABLE).
01-14-2015 7:44 AM
01-14-2015 8:01 AM
When using FOR ALL ENTRIES with big volumn of data you MUST insure to provide some leading keys (primary or from index) else performance will get very poor as you already know now...
Did you execute a SQL trace thru ST05 to analyze system behavior, execute your report with trace on, then stop trace and display trace, execute explain on the OPEN step : If there is no customer index on VBAK in your system with keys kunag, vkorg or bstnk, system will almost certainly perform a full-scan of the table... for each generated subset of values (and those subset usually only group a small handfull of values)
Also there is a FAQ note on OSS : 185530 - Performance: Customer developments in SD, did you read it, this note suggest to use VAKPA to identify sales orders with customer/partner information (and VKPMA to identify deliveries, VAPMA for order by material... )
Regards,
Raymond
01-14-2015 8:01 AM
Can you describe the required selection logic? Having an ITAB with coherent rows of selection values, of which three represent an EQ and a forth one an NE condition seems unusual to me.
VAPMA is not the right one, you can look at table VAKPA to replace VBAK in your selection, this one is sorted by partner numbers.
Thomas