Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K 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
- 304 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
- 260 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
GL compression (for fun)
spider1269
Member Posts: 73
Okay, I'm weird but I had heard that compression of G/L entries wasn't always worth the effort. I just re-implemented to NAV2015 on new hardware so I had a copy of our old production database sitting alone on our old production box. I decided to go extreme so here's what happened. Before G/L Entry Date compression: 288,352,518 records at 111 GB of space (yes, that's 1 table). I compressed 10 years of data by year and after compression had 52,153,230 records and 21 GB of space. 90 GB space saving including indexes.
Now here's the interesting part. Even with 288 million records we never had performance issues with the G/L Entry table. Space was also never an issue so I'm not sure in our case that we would have ever done this. Now the real reason I wanted to try this was to find out how long it took to complete. I compressed 10 years of data and was running this process exclusively on our old production box. It has 2 quad core processors running at 2.4 GHz with 100 GB of RAM on Windows 2008 R2. One of the limiting factors was the database was still on SQL 2005. The total time to complete was 8.5 days. Yes, it took almost 9 days to run the compression. We were only down less than half a day during our upgrade so I'm guessing in real world I would have had to compress in very small date chunks.
I'm curious to hear if anyone has done this on their production boxes and what the outcome was.
Now here's the interesting part. Even with 288 million records we never had performance issues with the G/L Entry table. Space was also never an issue so I'm not sure in our case that we would have ever done this. Now the real reason I wanted to try this was to find out how long it took to complete. I compressed 10 years of data and was running this process exclusively on our old production box. It has 2 quad core processors running at 2.4 GHz with 100 GB of RAM on Windows 2008 R2. One of the limiting factors was the database was still on SQL 2005. The total time to complete was 8.5 days. Yes, it took almost 9 days to run the compression. We were only down less than half a day during our upgrade so I'm guessing in real world I would have had to compress in very small date chunks.
I'm curious to hear if anyone has done this on their production boxes and what the outcome was.
0