Performance issue in SELECT query

[Former Member]

Hello Experts,

I have added piece of code below where i am facing performance issue.

In the first query select .... from VBKD taking lot of time to execute it causing the performance issue.

Could anyone suggest the best possible way to use the first query to improve the performance.

Your immediate help would be great help to me.

split g_customer_po at '/' into ld_pknr ld_purch_no.

r_bstkd_line-sign = 'I'.

r_bstkd_line-option = 'CP'.

concatenate '*/' ld_purch_no into r_bstkd_line-low.

APPEND r_bstkd_line to r_bstkd.

*For performance reasons, we will change the JOIN to 2 separate SELECTS

• First we will check VBKD and if we don't retrieve any records, we

• don't need to retrieve any data from VBAK.

select vbeln into table lt_vbeln

from vbkd

where bstkd_m in r_bstkd

and posnr eq '000000'.

if sy-subrc eq 0.

select vbeln into me->g_sales_order

UP TO 1 ROWS

from vbak

FOR ALL ENTRIES IN lt_vbeln

where vbeln eq lt_vbeln-vbeln

and kunnr eq g_partner_id.

endselect.

[Former Member]

Hi,

Use of primary key in the WHERE clause can reduce the performance issue.

While selecting from table VBKD, use VBELN in the WHERE condition.

[Former Member]

Hi,

you should fill r_bstkd with exact values if possible. So instead of CP option use only EQ. There is a secondary index in table VBKD with fields MANDT and BSTKD_M.

Regards,

Adrian

[sravanibellana]

Hi,

Please refer the below link.

http://help.sap.com/saphelp_NW70EHP1/helpdata/en/79/ccb4db852f11d3957400a0c930df15/frameset.htm

Thanks,

Sravani.

[Former Member]

Few recommendations, that will help

- Sort r_bstkd, before selecting from VBKD,

check .. if r_bstkd[] is not initial, Else it will fetch the entire records with POSNR '00000'.

- In where clause use the fields "in sequence" as they are present in table as :

select vbeln into table lt_vbeln

from vbkd

where posnr eq '000000' and

bstkd_m in r_bstkd

- likewise check if lt_vbeln[] is not intial before selecting from VBAK.

[Former Member]

Hi,

The best way is to use the primiary key. But sometimes as per the requirement you may not be able to use the primary key in the query, there can be two solutions but i would prefer the first one.

Create a index at table leve i.e if the approval provided by the basis and your development team. It will improve the performance.

The second option is: Fetch the entire the data into internal table and then filter that table based on the select option.

Thanks

Sunil Kumar

[kesavadas_thekkillath]

I think you can go for a single query using the view MASSSDPOSVBKD

Also try to replace UPTO 1 row because VBELN is the key field of table VBAK

[Former Member]

Hi,

in the vbkd query use both primary keys '' VBELN " and " POSNR " in the where condition it will improve the performance of your query and also do one more thing add an index to the field of vbkd that you going to select in the first query.