Skip to Content

SELECT Performance problem with IN, GE, LE condition

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • Best Answer
    Posted on Jan 02, 2014 at 01: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'.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 09, 2014 at 01: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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 02, 2014 at 01: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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 02, 2014 at 01: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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 02, 2014 at 01: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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.