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

Performance issue in SELECT query

Former Member — Fri, 05 Feb 2010 05:32:20 GMT

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.

Re: Performance issue in SELECT query

Former Member — Fri, 05 Feb 2010 05:35:37 GMT

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.

Re: Performance issue in SELECT query

Former Member — Fri, 05 Feb 2010 06:45:37 GMT

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

Re: Performance issue in SELECT query

sravanibellana — Fri, 05 Feb 2010 06:56:25 GMT

Hi,

Please refer the below link.

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

Thanks,

Sravani.

Re: Performance issue in SELECT query

Former Member — Fri, 05 Feb 2010 06:59:39 GMT

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.

Re: Performance issue in SELECT query

Former Member — Fri, 05 Feb 2010 07:13:03 GMT

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

Re: Performance issue in SELECT query

kesavadas_thekkillath — Fri, 05 Feb 2010 09:12:23 GMT

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

Re: Performance issue in SELECT query

Former Member — Fri, 05 Feb 2010 10:24:27 GMT

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.