Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance issue in SELECT query

Former Member
0 Kudos

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.

1 ACCEPTED SOLUTION

ThomasZloch
Active Contributor
0 Kudos

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

8 REPLIES 8

former_member203305
Active Contributor
0 Kudos

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

Former Member
0 Kudos

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

0 Kudos

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

Former Member
0 Kudos

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

former_member200338
Active Contributor
0 Kudos

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

harshsisodia31
Participant
0 Kudos

Hi Selva,

Try using VAPMA table.

Best Regards,

Harsh

raymond_giuseppi
Active Contributor
0 Kudos

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

ThomasZloch
Active Contributor
0 Kudos

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