topic Re: SELECT Performance problem with IN, GE, LE condition in Application Development and Automation Discussions

SELECT Performance problem with IN, GE, LE condition

suwandi_cahyadi — Thu, 02 Jan 2014 13:06:26 GMT

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.

Re: SELECT Performance problem with IN, GE, LE condition

Former Member — Thu, 02 Jan 2014 13:17:09 GMT

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'.

Re: SELECT Performance problem with IN, GE, LE condition

deependra_shekhawat3 — Thu, 02 Jan 2014 13:25:10 GMT

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

Re: SELECT Performance problem with IN, GE, LE condition

Former Member — Thu, 02 Jan 2014 13:33:46 GMT

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

Re: SELECT Performance problem with IN, GE, LE condition

RaymondGiuseppi — Thu, 02 Jan 2014 13:40:28 GMT

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

Re: SELECT Performance problem with IN, GE, LE condition

suwandi_cahyadi — Thu, 09 Jan 2014 13:29:23 GMT

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.