topic Re: Performance issue in SELECT query in Application Development and Automation Discussions

Performance issue in SELECT query

Former Member — Tue, 13 Jan 2015 18:17:44 GMT

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.

Re: Performance issue in SELECT query

Former Member — Tue, 13 Jan 2015 18:52:20 GMT

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

Re: Performance issue in SELECT query

Former Member — Tue, 13 Jan 2015 19:00:16 GMT

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

Re: Performance issue in SELECT query

Former Member — Tue, 13 Jan 2015 19:35:19 GMT

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

Re: Performance issue in SELECT query

Former Member — Wed, 14 Jan 2015 06:29:42 GMT

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

Re: Performance issue in SELECT query

Former Member — Wed, 14 Jan 2015 07:26:57 GMT

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

Re: Performance issue in SELECT query

harshsisodia31 — Wed, 14 Jan 2015 07:44:28 GMT

Hi Selva,

Try using VAPMA table.

Best Regards,

Harsh

Re: Performance issue in SELECT query

RaymondGiuseppi — Wed, 14 Jan 2015 08:01:26 GMT

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

Re: Performance issue in SELECT query

ThomasZloch — Wed, 14 Jan 2015 08:01:37 GMT

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