GL compression (for fun)

spider1269

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.