topic Re: Performance issue on SELECT from VBAP in Application Development and Automation Discussions

Performance issue on SELECT from VBAP

Former Member — Wed, 09 Apr 2008 12:45:51 GMT

Hi experts,

SELECT vbeln

posnr

matnr

matkl

kwmeng

vrkme

werks

FROM vbap

INTO TABLE i_vbap

FOR ALL ENTRIES IN i_mara

WHERE matnr EQ i_mara-matnr

AND matkl IN s_matkl

AND werks IN s_werks.

I need to compute for all open sales order quantity of a material in that select above but I'm having problem because the select is taking way too long resulting into a runtime error. Can you suggest other solution? Thanks in advance.

Edited by: Marc Winston Ng on Apr 9, 2008 2:46 PM

Re: Performance issue on SELECT from VBAP

Former Member — Wed, 09 Apr 2008 12:53:22 GMT

Hi,

If there is a parent child relationship between two tables it is better to go for INNER JOIN.

If there are any indexes available, search them whether any thing is available with fields in the where condition.

If any thing is there follow the order of the fields.

If u can't find any thing, try to create a secondary index.

Regards,

Angi.

Re: Performance issue on SELECT from VBAP

Former Member — Wed, 09 Apr 2008 13:02:35 GMT

There is already index on MATNR in VBAP, this query shouldn't take much time unless i_mara is empty.

check i_mara[] is not initial before select stmt.

Re: Performance issue on SELECT from VBAP

ThomasZloch — Wed, 09 Apr 2008 13:06:03 GMT

I don't see an index on MATNR for VBAP (at least in 4.7), however there is table VAPMA which allows for quick access to sales document items via material number.

Cheers

Thomas

Re: Performance issue on SELECT from VBAP

Former Member — Wed, 09 Apr 2008 13:29:16 GMT

few suggestion....

1. Check if there is an index in VBAP for MATNR. And if possible then create and use that.

2. Try following Code

DATA: s_cursor TYPE cursor.

if not t_mara[] is initial.

OPEN CURSOR WITH HOLD s_cursor FOR

SELECT vbeln

posnr

matnr

matkl

kwmeng

vrkme

werks

FROM vbap

FOR ALL ENTRIES IN i_mara

WHERE matnr EQ i_mara-matnr

AND matkl IN s_matkl

AND werks IN s_werks.

DO.

FETCH NEXT CURSOR s_cursor

APPENDING

TABLE i_vbap

PACKAGE SIZE '2000'.

IF sy-subrc <> 0.

CLOSE CURSOR s_cursor.

EXIT.

ENDIF.

ENDDO.

endif.

Re: Performance issue on SELECT from VBAP

Former Member — Wed, 09 Apr 2008 14:27:59 GMT

Hi Marc,

First of all below are my comments on your SELECT statement.

1. There is no check as to whether

i_mara[] is initial or not.

When ever we're using FOR ALL ENTRIES considering the performance it is MANDATORY to have the check as

IF I_MARA[] IS NOT INITIAL.

Then write the SELECT statement.

2. All the KEY FIELDS of the table VBAP (VBELN, POSNR) are not included in this SELECT statement. It is suggestable to use the Key fields in the where condition to increase the performance.

3. If you cannot include the key fields in your select statement then try creating a secondary index with the fields what you have used in the where condition.

4. In the Selection Screen instead of leaving blank make the fields as MANDATORY fields for input.

5. Also we can use inner joins between tables (not more than 2 or 3 tables).

By taking the above precautions we can ensure that the performance is increased.

Hope these steps solves your problem. Reward accordingly.

Thanks and Regards,

Maddineni Bharath.

Re: Performance issue on SELECT from VBAP

ThomasZloch — Wed, 09 Apr 2008 14:38:41 GMT

no need for a new secondary index...use table VAPMA as I suggested already.