INDEX Creation for Query Optimization

[Former Member]

Hi Experts

I have the below query:

SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG
A~ERDAT A~ERZET A~PSTYV D~AUART E~ETTYP E~EDATU

INTO TABLE INT_RES

FROM VBAK AS D INNER JOIN VBAP AS A 

ON D~VBELN EQ A~VBELN

INNER JOIN VBEP AS E

ON E~VBELN EQ A~VBELN AND
E~POSNR EQ A~POSNR

WHERE D~VBELN IN S_VBELN AND
D~AUART IN S_AUART AND
D~VKORG IN S_VKORG AND
D~VBTYP EQ 'C' AND
( ( A~MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) OR
( MATNR LIKE C_PREFIX_FP AND KWMENG NE A~KBMENG ) ) AND
A~ABGRU EQ SPACE AND
A~MTVFP IN R_MTVFP AND
A~PRCTR IN R_PRCT AND
E~ETENR EQ '1'.

I tried many ways of re-writing the query - yet performance could not be improved. Now I need to create an INDEX and check if it works out?

Could you please help me suggesting upon which table and which fields in which order I can create an effective INDEX?

Please Help

[Former Member]

Hi Experts

I have the below query:

SELECT A~VBELN A~POSNR A~MATNR A~KWMENG A~KBMENG
A~ERDAT A~ERZET A~PSTYV D~AUART E~ETTYP E~EDATU

INTO TABLE INT_RES

FROM VBAK AS D INNER JOIN VBAP AS A 

ON D~VBELN EQ A~VBELN

INNER JOIN VBEP AS E

ON E~VBELN EQ A~VBELN AND
E~POSNR EQ A~POSNR

WHERE D~VBELN IN S_VBELN AND
D~AUART IN S_AUART AND
D~VKORG IN S_VKORG AND
D~VBTYP EQ 'C' AND
( ( A~MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) OR
( MATNR LIKE C_PREFIX_FP AND KWMENG NE A~KBMENG ) ) AND
A~ABGRU EQ SPACE AND
A~MTVFP IN R_MTVFP AND
A~PRCTR IN R_PRCT AND
E~ETENR EQ '1'.

I tried many ways of re-writing the query - yet performance could not be improved. Now I need to create an INDEX and check if it works out?

Could you please help me suggesting upon which table and which fields in which order I can create an effective INDEX?

Please Help

[Former Member]

hi bobby ,

where r u facing the performance problem wether in vbap or vbak , and how many entreis are fetched ur using join instead of it u can use for all entries , and if the fields are not key fields then u can create index on both tables also

hope solves ur prob

[Former Member]

Hi,

Check which selection criteria are polulated when you run the query.

Also one issue might be with using the LIKE keyword in the where clause.

( ( A~MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) OR
( MATNR LIKE C_PREFIX_FP AND KWMENG NE A~KBMENG ) ) AND

Instead of using this, I would suggest you get all the materials from material master using the like operator and pass them in a range to this query.

select matnr from mara into it_mara
where matnr like c_prefix_sp. 
select matnr from mara appending table it_mara where matnr like c_prefix_fp.
"Loop at it_mara and populate a range. Which will then be used in the select query

That might help improve the performance.

regards,

Advait

[Former Member]

Hi Guys

Thanks for suggestions.

Suppose, if I want to create an INDEX for this query, then what would be the fields which you will suggest to consider?

Please reply to this specifically.

Thx

[seshatalpasai_madala]

Hi,

You should put those fields in the INDEX which you will use in the WHERE clause of the select statement.

Also make sure that the fields in the INDEX are in the same order as they are in the WHERE clause.

Regards,

Sesh

[Former Member]

Firstly, I dont think you need to create any index for your case,the suggestion I provided should help improve the performance.

But just to answer your query, since your query is using the fields AUART,VKORG, AND VBTYP from VBAK and MATNR from VBAP, those would be the recommended fields to be used while creating the index for VBAK and VBAP respectively.

But remember that creating custom index is the last thing to consider due to the overhead to update the index for every database operation.

Even after creating indices, you need to make sure that you pass values to the selection criteria for which you created the index.

regards,

Advait

[Former Member]

Hi,

Even you create an index you query will not use the index, because you are using -Ve operator in where class.

remove the -Ve operator from your query it will work fine.

and if you can work around to avoid the LIKE statement it ill do fine.

Rgds.

[Former Member]

Hi Pancras

(-)ve Operator?

I did not get you? Is it ~ you mean? How it will affect performance? How can I remove that?

[Former Member]

-Ve operator use in u'r query is Not equal etc.,

ZZ_MSPOSNR NE 0

remove that from where class.

after select you can delete it from ITAB. or you can skip while processing.

use which ever suite you.

Rgds.

[Former Member]

if you use -Ve operators engine will have full scan on the particular table wether you use index or what ever.

[Former Member]

Thx for the suggestions. But, I feel a difficulty in between.

There is a field in checking ZZ_MSPOSNR in

( A~MATNR LIKE C_PREFIX_SP AND ZZ_MSPOSNR NE 0 AND KBMENG EQ 0 ) 

and one field KWMENG being checked against same as

( MATNR LIKE C_PREFIX_FP AND KWMENG NE A~KBMENG )

How can I write DELETE statement for these -ve operators?

Plz suggest..

[Former Member]

This question is an extension of your earlier one: Please assign poin ts to it and mark it as closed. When that is done, I will unlock this.

Rob

[matt]

Points assigned, marked as answered, so unlocked.

matt