performance

[Former Member]

SELECT VBELN POSNR WMENG LDDAT FROM VBEP INTO TABLE I_VBEP WHERE LDDAT IN S_LDDAT

%_HINTS ORACLE 'index("ZM1" "ZM1_{VBELN" "ZM1}POSNR" )'.

where zm1 is a index of vbep table containing vbeln posnr.

in development it is not giving any problem.

in quality it is giving me a problem

I had done the runtime analysis in both the system.

db is development is 0.4

where as in quality it is 72.1

can you please help me out.

my quality system contains 2 crores of data in vbep.

[Former Member]

1.) try putting LDDAT to your index.

2.) make sure your db-statistics are up to date, VBEP tends to get very big over the time.

3.) consider using LIS to avoid the runtime of the abap - you can create your own structure and update the data (asynchronous) whilst posting the delivery.

[Former Member]

Hi Koushik,

I am not very conversant with BASIS however I know for a fact that the forcing of explicit index using %_HINT ORACLE statement only works on ORACLE databases. If at all possible, it could be your development server uses a Oracle database and hence the index is hit proper, however, your QA server might be using another database like IBM or MS SQL.

Even otherwise, a index works best when all the fields in the index are used in the WHERE clause. As I can see, you most likely are not using all fields (since the index is a Z index, I cannot be sure). Hence the buffer/cache created by the index is useless when used with LDDAT as a query.

With 2 crore records, I think you need to consider archiving older records (if possible) and if not, then you need to make your WHERE clause more "meaty", add more fields .....

Cheers,

Aditya

[Former Member]

At least two problems with this:

The creation of the index ZM1 is a waste of time and resources - VBELN and POSNR are the first two fields of the primary key. When the index was activated it probably gave a message to this effect.

Hints should not be used in general in a production environment. The table will grow and what seemed like a good index at the time may prove to be the wrong one in the future. But in this case it make no sense at all because the HINT is pointing to an index that won't help at all. What good is using an index based on the ducument number and item when the WHERE only looks at the loading date (LDDAT).

My advice is to run this in the background.

Rob