Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.6K 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
- 296 Dynamics CRM
- 109 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 993 SQL General
- 384 SQL Performance
- 34 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
Managing Huge Change Log Table
najja
Member Posts: 67
Hello Experts,
We have enabled change log for each and every fields of the database to meet some statutory reporting requirement. The result is a huge Change Log Table of more than 100 gig for that table alone (this is my approximation). I was wondering whether moving the Change Log Table alone to a different, exclusive file group and placing that particular file group in a seperate physical volume will allow me to optimise the performance of the entire system. There is no way we can purge the change log data. Please advise. Is there any other method to manage huge tables in NAV.
Najja
We have enabled change log for each and every fields of the database to meet some statutory reporting requirement. The result is a huge Change Log Table of more than 100 gig for that table alone (this is my approximation). I was wondering whether moving the Change Log Table alone to a different, exclusive file group and placing that particular file group in a seperate physical volume will allow me to optimise the performance of the entire system. There is no way we can purge the change log data. Please advise. Is there any other method to manage huge tables in NAV.
Najja
0
Comments
In addition to the large size, we experienced frequent table locking delays as every transaction required multiple additional history table records. This was with the native db.
Lanham's add-on uses multiple tables rather than one. There is one history record table for each table for which history is tracked. So we no longer have one huge table and the locking problem has disappeared. Users also like the interface for viewing history records much better.
Lanham markets its product as History Management Basic, and then sells additional tables in blocks of 50. Rather than use a history table for tables that are seldom changed, such as set up tables, we use the Change Log for those.
One drawback with Lanham's add-on is the additional programming overhead when you add fields to tables. If you can to maintain history on those new fields, you must go through the process of creating new history objects. I modified this process so I can create new history objects only for selected tables, instead of all tables for which history is tracked.
www.lanhamassoc.com has more information.