Improving query performance

[Former Member]

Hi,

This piece of code is taking a long time and many times gives Abap runtime errors with SYSTEM_IMODE_TOO_LARGE. (Basically the performance of the query needs to improve. I have done ST05 and also ran the query and I would appreciate any input on improving performance. I have put my comments in brackets after every query.

SELECT vbeln auart audat kunnr qmnum vkbur

FROM vbak

INTO CORRESPONDING FIELDS OF TABLE t_vbak

WHERE vbak~vkorg IN s_vkorg

AND vbak~audat IN s_erdat

AND vbak~kunnr IN s_kunnr.

(The above query is fine. Returns about 80,000 rows)

CHECK sy-subrc EQ 0.

• Select VBAP

SELECT erdat matnr posnr pstyv vbeln arktx werks abgru

FROM vbap

INTO CORRESPONDING FIELDS OF TABLE t_vbap

FOR ALL ENTRIES IN t_vbak

WHERE vbeln = t_vbak-vbeln.

(The above query takes a long time to execute)

t_vbap_ra[] = t_vbap[].

(The above statement takes a long time to execute)

DELETE t_vbap_ra

WHERE pstyv <> 'ZRRA' AND

pstyv <> 'ZWRA'.

(This statement takes a long time to execute)

Thanks

Ram

[valter_oliveira]

Create an itab with all the fields from both tables, and then:

SELECT a~vbeln a~auart a~audat a~kunnr a~qmnum a~vkbur
        b~erdat b~matnr b~posnr b~pstyv b~arktx b~werks b~abgru
  INTO TABLE new_itab
  FROM vbak AS a INNER JOIN vbap AS b
    ON a~vbeln = b~vbeln
 WHERE a~kunnr IN s_kunnr
   AND a~vkorg IN s_vkorg
   AND a~audat IN s_erdat
   AND NOT b~pstyv IN ('ZRRA','ZWRA').

Hope that s_erdat OR (s_kunnr and s_vkorg) are always filled to use table indexes.

Also, can't you restrict by some AUART to use:

   AND a~auart IN ('OT','ZBC',...)

Regards,

Valter Oliveira.

[Former Member]

hi,

may i know your requirement? 80k record is huge. try to avoid unwanted records fetching. there is limitation for internal table size in SAP. try to use hash table for large number of records.

[Former Member]

Hi Ram,

Do not use INTO CORRESPONDING FIELDS OF TABLE.

Instead go for INTO TABLE t_vbak. For this the table t_vbak should contain all the fields being selected. Same with t_vbap.

Regards,

Chandra Sekhar

[Former Member]

Since you are saying there are 80,000 records, I would suggest creating Views on two tables and retrieve your entries at once.

Or you can think of secondary index.

[Former Member]

Hi,

Try this.

1. let the internal table t_vbak have the fields with the same name & order in which the fields are fetched from the database table so that you can replace the 'CORRESPONDING FIELDS OF' stmt with 'INTO TABLE'.

2. Whenever u make use of for all entries check whether the table has records in it or not and also modify the below select query as

if t_vbak[] is not intial.

SELECT erdat matnr posnr pstyv vbeln arktx werks abgru

FROM vbap

INTO CORRESPONDING FIELDS OF TABLE t_vbap

FOR ALL ENTRIES IN t_vbak

WHERE vbeln = t_vbak-vbeln

and (( pstyv ne 'ZRRA' ) or

(pstyv ne 'ZWRA' )).

endif.

Sharin.