HOWTO go about reducing indexes?

Bgestel

Hi,

I have been reading up on Sql nav performance, because the solution i'm building has some legacy code, keys and sift's. For example the cost of the item ledger entry is 104 :oops: and te sales line has a cost of about 90.

From what i understand from the sql resource kit and the performance trouble shooting guide , ... , i will have to dramaticly reduce these keys en sift levels.

The suggested method for doing this is to take a customer database en monitor the usage and then change/enable/disable the keys accordingly.

Now here is the problem:
How do i go about reducing the keys by design. What is the method of determining if a key should be reduced in leght, disabled, reordered ...

-Do is disable alle of them in the top 10 tables , and then start the client monitor and enable them 1 by 1, the trial and error way.

Or is there a intelligent way of determining the usefullnes and correctnes of a key, when you know what processes/ business logic they where intended for in the first place (oldstyle .fdb style). Do this , then this and then this?

I'm struggeling with this because all our customer db's have there own specific customizations and usage. And now i would like the base (our vertical solution) object to have good keys (a good starting point). I see the problem, but dont have a method of solving it.

thanx in advance

Marije_Brummel

Bert,

For add-on's the best approach is to determine keygroups per subfunction so that consultants can easily activate and deactivate them.

Use the development toolkit to see where the indexes are used and why.

Making them more selective is a bit difficult in an add-on database since selectivity is a customer specific thing.

Try to use the benchmark toolkit for simulating load. Define reproducable testscripts so you can measure and retest again and again.

Your testdatabase should be at least 10 gb to have performance issues at all.

DenSter

In an ideal world, you'd think about these things when developing a customer solution. Design the new key for the purpose that you create it for, and only enabling the SIFT levels that are actually used, and try to design the SQLIndex with selectivity in mind.

Unfortunately this is not exact science. Sometimes what works in one database will completely screw up another database. I've seen disabling the same key speed one database up and slow another one down. You can apply some common sense logic to it, a methodology if you will, but you will always have to monitor, measure and adjust during actual usage of the system.