topic Re: Indexes in Application Development and Automation Discussions

Indexes

Former Member — Wed, 15 Nov 2006 14:15:00 GMT

Will creating an index on vbap table with the fields in where clause improve the performance of this code ?

SELECT vbeln posnr matnr arktx pstyv vkaus werks

vstel erdat netpr waerk fkrel kpein kpein

exart

FROM vbap

INTO CORRESPONDING FIELDS OF TABLE t_vbap

for all entries in itab_vbak

WHERE vbeln = itab_vbak-vbeln

AND werks IN plant

AND matnr IN o_matnr

AND pstyv IN pstyv

AND abgru = ' '

AND vstel IN o_vstel.

Re: Indexes

Former Member — Wed, 15 Nov 2006 14:18:27 GMT

it will definetely increase the performance of the program...indexes are created for better performance of the programs.

Re: Indexes

RichHeilman — Wed, 15 Nov 2006 14:18:40 GMT

Possibly, but lets try other things first. Make sure that you are only doing this select when there are records in ITAB_VBAK, and then make sure that it is sorted by VBELN.

check not ITAB_VBAK[] is initial.
sort itab_vbak ascending by VBELN.

Performance any better?

Regards,

Rich Heilman

Re: Indexes

Former Member — Wed, 15 Nov 2006 14:20:03 GMT

Before you create an index check if you change the order of the where clause, if there is any improvement in performance.

also, sort the itab_vbak.

sort itab_vbak.

SELECT vbeln posnr matnr arktx pstyv vkaus werks

vstel erdat netpr waerk fkrel kpein kpein

exart

FROM vbap

INTO CORRESPONDING FIELDS OF TABLE t_vbap

for all entries in itab_vbak

WHERE vbeln = itab_vbak-vbeln

AND matnr IN o_matnr

AND pstyv IN pstyv

AND abgru = ' '

AND werks IN plant

AND vstel IN o_vstel.

Re: Indexes

Former Member — Wed, 15 Nov 2006 14:23:39 GMT

A couple of things:

It's generally not a good idea to create an index on a standard SAP table that is heavily updated in the foreground. The index has to be updated every time a user inserts a record. So while it may speed up your select, it will slow down other users. You particularly don't want to do this to speed up a single select in a single program.

The main purpose of a secondary index is to be selective. In other words, to return as little data as possible. Since VBELN is already the first key field in the primary index, it's already highly selective.

So I wouldn't create the index.

Rob

Re: Indexes

Former Member — Wed, 15 Nov 2006 18:28:49 GMT

I would suggest you just check your code to make sure

1) you are not trying to select from VBAP with empty itab_vbak

2) if you have too many entries in itab_vbak - it may be better to use some of index tables like - VAPMA (order items by material) and then - select all additional info from VBAK & VBAP if required. btw, VAPMA has some fields from header level - like sales org. & so on... so it may really help if you select based on material.

Re: Indexes

Former Member — Thu, 16 Nov 2006 08:25:15 GMT

Hello,

To ans your Q, yes it will certainly improve the performance provided database is selecting the alternative index during selection otherwise it will be of no use.

Now coming back to if you should create an index is something which needs to be really look at. As one of the response has indicated that this is definatly a load on the database as VBAP is a table which gets updated heavily and everytime this secondary index gets rebuilt.

So it is your call based on your req. u can look for option.

Re: Indexes

Former Member — Fri, 17 Nov 2006 14:40:36 GMT

Hi,

You can replace your "INTO CORRESPONDING FIELDS" clause with a "INTO" clause.

It's so much better !

Regards,

Dieu

Re: Indexes

abdul_hakim — Sat, 18 Nov 2006 05:52:17 GMT

Hi,

Below are some performance tips to u...

1.Replace INTO CORRESPONDING with INTO.

2.Before using FOR ALL ENTRIES addtion jus the the respective table is empty or not.for instance..IF NOT ITAB_VBAK IS INITIAL.

Cheers,

Abdul Hakim

Re: Indexes

Former Member — Thu, 23 Nov 2006 21:20:18 GMT

Hello to everybody

I have been appearing east problem with a FI report I put more than 1000 centers of costs, for this initially prove with for all entries and preformed was terrible, later I change the for all by IN and performance was far better, but if the number of cost centers it exceeded the 1000 SQL crash I did was

DATA VL_COUNTER TYPE P.

RANGES R_KOSTL FOR CSKS-KOSTL

VL_COUNTER = 0.

R_KOSTL-OPTION = 'EQ'.

R_KOSTL-SIGN = 'I'.

LOOP AT T_COSTLS.

R_KOSTL-LOW = T_KOSTLS-KOSTL.

VL_COUNTER = VL_COUNTER + 1 .

APPEND R_KOSTL.

IF VL_COUNTER = 1000.

VL_COUNTER = 0.

SELECT * FROM GLPCA

APPENDING TABLE T_DATA

WHERE

KOSTL IN R_KOSTL.

CLEAR R_KOSTL.

REFRESH R_KOSTL.

R_KOSTL-OPTION = 'EQ'.

R_KOSTL-SIGN = 'I'.

ENDIF.

ENDLOOP.

IF VL_COUNTER ne 0.

VL_COUNTER = 0.

SELECT * FROM GLPCA

APPENDING TABLE T_DATA

WHERE

KOSTL IN R_KOSTL.

CLEAR R_KOSTL.

REFRESH R_KOSTL.

R_KOSTL-OPTION = 'EQ'.

R_KOSTL-SIGN = 'I'.

ENDIF.

clear the data that have one 'E' in SIGN

Message was edited by:

Gibran Roberto Martínez Santos

Re: Indexes

Former Member — Thu, 23 Nov 2006 21:54:03 GMT

Performance may have been better because of buffering. If you ran this program soon after the original one, records may have been taken from the buffer rather than the database.

Rob

Re: Indexes

Former Member — Fri, 24 Nov 2006 02:51:45 GMT

Hiiiii

I think index will improve the performance .............

but following steps can also improve performance of ur query....

1. Ensure first, that the internal table is not initial

2. You should sort internal table itab_vbak on the field vbeln

3. Remove the into 'corresponding fields of ' ..u should declare own internal table ..

4. Select the field in the sequence in which they are maintained in the table and in where conditions the sequence of fields should be the same.