Performance issue on SELECT from VBAP

[Former Member]

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

[Former Member]

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.

[Former Member]

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.

[ThomasZloch]

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

[Former Member]

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.

[Former Member]

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.

[ThomasZloch]

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