Skip to Content
avatar image
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 comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

7 Answers

  • Best Answer
    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 comment
    10|10000 characters needed characters exceeded

  • 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 comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    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 comment
    10|10000 characters needed characters exceeded

    • Former Member

      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

  • 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 comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    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 comment
    10|10000 characters needed characters exceeded

  • avatar image
    Former Member
    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 comment
    10|10000 characters needed characters exceeded

  • Jan 14, 2015 at 07:44 AM

    Hi Selva,

    Try using VAPMA table.

    Best Regards,

    Harsh

    Add comment
    10|10000 characters needed characters exceeded