Rebuilding BSAD~Z01 index

[Former Member]

Hi,

Finally I have decided to add some more fields in the existing BSAD_{z01 index to improve performance. Currently BSAD}Z01 index is picking from select statements. What are the precautions to take for adding some more fields to this index. Whether it will effect any other programs about data load or not. How can i proceed further. Its giving problems in PRD because of taking more time.

SELECT specific fields FROM bsad

into table gt_bsad

WHERE bukrs = docs-bukrs

AND gjahr = docs-gjahr

AND augbl > docs-low

AND augbl <= docs-high

AND zlsch = docs-meth.

[brad_bohn]

I'm not sure why you would search BSAD in that manner but you didn't specify the fields in your BSAD~Z01 index (that's a custom index). With that SELECT though, it's unlikely that adding any more fields would help. Have you traced the selection and reviewed the relative costs of each index?

[Former Member]

Currently as per BA, we are not going to change the program. So looking for modifying the existing index (BSAD_{Z01) with the fields as BUKRS, GJAHR, AUGBL & ZLSCH. Presently the fields in BSAD}Z01 index are BUKRS & AUGBL. Let me know if any.

[Former Member]

If you are passing both BUKRS and AUGBL to the SELECT, then it should be able to use your index. Adding GJAHR might help a bit, but the index is already pretty highly selective.

But if you are selecting a large range of clearing documents, then there won't be much use in changing the index anyway.

Have you run ST05 to see which (if any) index is used?

Rob

[former_member192616]

Hi,

> But if you are selecting a large range of clearing documents, then there won't be much use in changing the index anyway.

only if we would add the fields from the selection as well and the client.

That would lead to an index only access... For huge result sets this could make sense... but is not a very typical 'tuning method'

Kind regards,

Hermann

Edited by: Hermann Gahm on Sep 23, 2010 8:49 PM

[brad_bohn]

Currently as per BA, we are not going to change the program. 

Well, if you perform a good analysis and your analysis shows that a code change is the best approach, then you need to state your case strongly enough to convince the decision makers. This is the sign of a good developer.

So looking for modifying the existing index (BSADZ01) with the fields as BUKRS, GJAHR, AUGBL & ZLSCH. Presently the fields in BSADZ01 index are BUKRS & AUGBL. Let me know if any.

I agree with Hermann and Rob - and again, you need to trace and analyzed the costs of the existing indexes as well as the adjusted index. However, it's unlikely that you gain enough (or any) selectivity with any additions to the index to change the performance by an order of magnitude.

[Former Member]

I have checked in ST04 & ST05 transactions. Its picking Z01 index. So whether i can change that index as per selection order or any other way to improve performane? Now BA has accepted that to change the program also. Let me know if any?

[former_member192616]

HI,

> I have checked in ST04 & ST05 transactions. Its picking Z01 index.

and? how efficient is it? how many blocks / pages are touched per record?

how much time is needed per record?

>So whether i can change that index as per selection order or any other way to improve performane?

maybe only a litte bit as stated before....

>Now BA has accepted that to change the program also. Let me know if any?

How does the program look like now and how are your plans to change it?

Kind regards,

Hermann

[Former Member]

If you are thinking of changing an index to improve the performance of a single program, it would be safer to see if you can find a programming solution instead.

Rob

[former_member192616]

Hi,

> Finally I have decided to add some more fields in the existing BSAD~z01 index to improve performance.

but your are not sure about if it will really help or it is really necessary, right?

>What are the precautions to take for adding some more fields to this index.

>Whether it will effect any other programs about data load or not. How can i proceed further.

changing an index might sql statements accessing this tables (your specified one and others):

- run faster

- run slower

- run the same as before (no change)

In order to give a more helpful answer we need to know how your index currently looks

like and how it will look like after your planned changes. Only then we might give you an

answer for the posted SQL statement (not for others).

Kind regards,

Hermann