Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.5K Microsoft Dynamics NAV
- 18.5K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 115 Navision DOS
- 854 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 615 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 264 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 992 SQL General
- 384 SQL Performance
- 33 SQL Tips & Tricks
- 34 Design Patterns (General & Best Practices)
- Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.7K General
- 1.1K General Chat
- 1.6K Website
- 79 Testing
- 1.2K Download section
- 23 How Tos section
- 259 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
HOWTO go about reducing indexes?
Bgestel
Member Posts: 136
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
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
**********************
** SI ** Bert Van Gestel **
**********************
** SI ** Bert Van Gestel **
**********************
0
Comments
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.
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.
RIS Plus, LLC
MVP - Business Apps