Skip to Content
author's profile photo Former Member
Former Member

Performance issue in SELECT query

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.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

7 Answers

  • Best Answer
    Posted on Jan 14, 2015 at 08: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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 13, 2015 at 06: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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 13, 2015 at 07: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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 14, 2015 at 08: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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 14, 2015 at 06: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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 14, 2015 at 07: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).

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 14, 2015 at 07:44 AM

    Hi Selva,

    Try using VAPMA table.

    Best Regards,

    Harsh

    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.