01-02-2014 1:06 PM
Dear Experts,
I have the following SELECT statement.
SELECT *
FROM wrf_matgrp_sku
WHERE matnr IN r_matnr
AND date_from <= sy-datum
AND date_to >= sy-datum
r_matnr contains approximately 37k or records. With that statement, I get a shortdump saying DBIF_RSQL_INVALID_RSQL. This, I found because that the r_matnr contains too many records than I change from using a range table to an internal table like the following:
SELECT *
FROM wrf_matgrp_sku
FOR ALL ENTRIES IN it_matnr
WHERE matnr = it_matnr-matnr
AND date_from <= sy-datum
AND date_to >= sy-datum
With the new statement I get no shortdump but a performance issue.
My question is that, is there anything I can do to solve the performance issue?
I'm thinking of using the parallel processing and also taking out the date_from and date_to conditions from the SELECT statement then filter out the data by deleting them from the internal table. Will this help?
Thank you,
Suwandi C.
01-02-2014 1:17 PM
Hi,
SELECT *
FROM wrf_matgrp_sku
WHERE matnr IN r_matnr
AND date_from <= sy-datum
AND date_to >= sy-datum due to this i think you program goes to dump instead of '>=' please put 'EQ'.
01-02-2014 1:17 PM
Hi,
SELECT *
FROM wrf_matgrp_sku
WHERE matnr IN r_matnr
AND date_from <= sy-datum
AND date_to >= sy-datum due to this i think you program goes to dump instead of '>=' please put 'EQ'.
01-02-2014 1:25 PM
Hi Suwanandi,
I don't think due to IN there could be any problem . But in case of LE and GE it takes time.
If you create range for both date , there could be good performance optimization
eg.
lr_date-low = sy-datum
lr_date-high = 31.12.9999
append lr_date.
and in select query use " date_to IN lr_date.
You can do same procedure for from date.
Try with this . Might be it can help you .
Thanks
Deependra
01-02-2014 1:33 PM
Hi Suwandi,
It seems that you are reading all the records in your r_matnr that means all 37k Records , So can u Please enlighten more regarding why you are reading all the records. Can you Post the Error with Exception also..?
Regards
Arpit Gupta
01-02-2014 1:40 PM
The FOR ALL ENTRIES often (almost always...) perform badly when internal table is to big, SQL statements are too long if too many records in a range selection.
So how did you get those 37K values of MATNR, did they come for another SQL SELECT statement, if yes you could use this one in a subquery clause like in following sample :
SELECT * INTO TABLE itab
FROM wrf_matgrp_sku AS wrf
WHERE EXISTS ( SELECT matnr
FROM marc
WHERE matnr = wrf~matnr
AND ekgrp IN so_ekgrp )
AND date_from LE sy-datum
AND date_to GE sy-datum.
Regards,
Raymond
01-09-2014 1:29 PM
Hi all,
I've change the code to use INDEX when selecting and filter the rest data in the Application Server by manipulating the internal table.
I've also changed from FOR ALL ENTRIES to ranges again but now I limit the maximum number of ranges that will be processed at once.
Thank you,
Suwandi C.