topic Performance improvement for the select statement in Application Development and Automation Discussions

Performance improvement for the select statement

Former Member — Thu, 21 Jun 2007 06:11:24 GMT

Hi Experts,

Below SELECT statement is taking too much of time.How can I modify this statement to improve the performance?

TYPES: BEGIN OF customer,

vbeln LIKE vbfa-vbeln,

kunnr LIKE kna1-kunnr,

END OF customer.

DATA: srch_kunnr TYPE customer OCCURS 0 WITH HEADER LINE .

SELECT vbeln INTO CORRESPONDING FIELDS OF TABLE

srch_kunnr

FROM vbpa

WHERE parvw = l_parvw AND

land1 IN so_co_ag.

Thanks in advance.

Raj

Re: Performance improvement for the select statement

Former Member — Thu, 21 Jun 2007 06:57:57 GMT

Hi Basavraj,

I beleive your fetching only vbeln into table, then why are you using into coresponding fields.

SELCT VBELN

INTO TABLE srch_kunnr

FROM VBAP

WHERE parvw = l_parvw AND

land1 IN so_co_ag.

Hope this helps you. Reply for queries, shall post the updates.

Regards.

Kumar

Re: Performance improvement for the select statement

Former Member — Thu, 21 Jun 2007 07:20:24 GMT

hi,

try like this,

TYPES: BEGIN OF customer,

vbeln type vbeln,

kunnr type kunnr,

END OF customer.

DATA: srch_kunnr TYPE table of customer.

SELECT vbeln kunnr INTO TABLE srch_kunnr

FROM vbpa.

WHERE parvw = l_parvw AND

land1 IN so_co_ag.

" wat is your where condition? it should be some ralation with field like

kunnr = vbpa-kunnr and vbeln = vbpa-vbeln, something like that.

reward points if useful,

regards,

seshu.

Re: Performance improvement for the select statement

Former Member — Thu, 21 Jun 2007 08:44:29 GMT

Hi,

I agree with Seshu.

Your where condition don't use fields neither of your table key nor of standard secondary index.

Consequently, that means that this statement will implies a <b>table scan</b> on VBPA which is certainly the source of performances problem.

Whether you can add key field in your where condition, or eventually create a specific index.

Regards,

Alex

Re: Performance improvement for the select statement

Former Member — Fri, 22 Jun 2007 03:20:45 GMT

Hi experts,

Thanks for your time.

Seshu ans alex,Could you please explain how can avoid this table scan.

how to create a specific index..

If you can modify the existing code ..that would be very helpful.

Regards

basavaraj

Re: Performance improvement for the select statement

Former Member — Sat, 23 Jun 2007 21:32:46 GMT

I too agree that you need to give any of primary key for filtering in where condition. As all vbeln needs to be considered, then just check the below code.

This should not take much time but just check the records in vbfa is not so huge that it leads for memory short dump.

TYPES: BEGIN OF customer,

vbeln LIKE vbfa-vbeln,

parvw like vbfa-parvw,

land1 like vbfa-land1,

kunnr LIKE kna1-kunnr,

END OF customer.

DATA: srch_kunnr TYPE customer OCCURS 0 WITH HEADER LINE .

SELECT vbeln parvw land1 INTO TABLE

srch_kunnr

FROM vbpa.

DELETE srch_kunnr NOT parvw = l_parvw AND

NOT land1 IN so_co_ag.

Regards

Navneet