topic Re: select statement from vbak - optimization technique in Application Development and Automation Discussions

select statement from vbak - optimization technique

Former Member — Mon, 29 Jun 2009 09:42:51 GMT

Hello,

I need to modify the select statement due to the performance issue, related to the SQL Trace Performance sheet

SELECT vbeln

vkorg

vtweg

spart

kunnr

erdat

FROM vbak

INTO TABLE t_vbak

WHERE vkorg IN s_vkorg

AND vtweg IN s_vtweg

AND spart IN s_spart

AND kunnr IN s_kunnr

AND erdat IN s_erdat

AND erzet IN s_erzet

AND vsbed IN s_vsbed

AND bsark IN s_bsark.

This is the select statement, I need to modify.

I tried with the index, which is available in our dev system. Still the issue is not resolved.

Select in accordance with the Index,

SELECT vbeln

erdat

erzet

vkorg

vtweg

spart

vsbed

bsark

FROM vbak

INTO CORRESPONDING FIELDS OF TABLE t_vbak

WHERE vkorg IN s_vkorg

AND vtweg IN s_vtweg

AND erdat IN s_erdat.

Please note that in these select statements, I cannot use VBELN in the where clause, since I need to fetch the orders based on the selection screen criteria.

Kindly sugest me, is there any FM, which I can use to fetch the orders.

Regards,

dinesh

Re: select statement from vbak - optimization technique

Former Member — Mon, 29 Jun 2009 09:51:58 GMT

Hi,

You can craete the secondary indexes for table VBAK as per your selection entry. It will reduce a lot of time while fetching the data from database.

Regards,

Nrusingha

Re: select statement from vbak - optimization technique

Former Member — Mon, 29 Jun 2009 09:56:57 GMT

Hi,

The secondary index is already available as it is, I mentioned in the second select statement. Still the performance issue is not resolved .

Regards,

dinesh

Re: select statement from vbak - optimization technique

Former Member — Mon, 29 Jun 2009 10:23:57 GMT

Hi,

There is a standard transaction VA05 where you can get the all your require details. It may some how help you to get the tables from where it is picking. Check this....

Regards,

Nrusingha

Re: select statement from vbak - optimization technique

Former Member — Mon, 29 Jun 2009 10:46:05 GMT

Hi,

Either you can use secondary index or you can create new index as per your query.

Other than this you can minimise the where clause conditions like creation date and time.

use DELETE Command for that after the query as follows:

DELETE T_VBAK where not erdat in s_erdat.

This will pas load on ABAP and not on DB.

Regds,

Anil

Re: select statement from vbak - optimization technique

Former Member — Mon, 29 Jun 2009 12:12:38 GMT

Hey Dinesh,

Probably what you can do is declare a variable of type vbeln and write your select query as shown below.

This will eventually decrease the load on the VBAK table.

First declare a variable like below

data:v_vbeln TYPE vbeln VALUE IS INITIAL.

Now write the select query and see the performance.

SELECT 

vbeln
vkorg
vtweg
spart
kunnr
erdat

FROM vbak

INTO TABLE t_vbak

WHERE 

vbeln ge v_vbeln

AND vkorg IN s_vkorg
AND vtweg IN s_vtweg
AND spart IN s_spart
AND kunnr IN s_kunnr
AND erdat IN s_erdat
AND erzet IN s_erzet
AND vsbed IN s_vsbed
AND bsark IN s_bsark.

Hope you got it.

Thanks,

Babu Kilari