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
- 307 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
Steps on doing Date Compression
couberpu
Member Posts: 317
Hi All,
I need advices on proper way of doing date compressions.
I am running Navision 3.70b run time, converted from 3.6, with SQL option. I only have one global dimension, which I like to retain. The target is to compress 2003 entries to "Period Length" = ::Year.
This is my plan: ( Retain global dimension "COMPANY")
1. Make a database backup using Navision Backup Utility.
2. Compress G/L Entry then make a 2003 close income statement by global dimension.
3. Compress VAT Entry, Bank Accounts Ledger and delete Check Ledger.
4. Compress Cust. Ledger
5. Compress Item Leger, BOM Ledger and delete Phys. Inventory Ledger.
6. Compress Vendor Ledger
7. Remove Empty Item Register and Empty G/L Register.
Is this a OK plan? When I first did date compression last year by length = ::Month, I screwed up so bad and I have to spend a lot of time "fixing" G/L. Any advice is appreciated! :oops:
Best Regards,
Couber Pu
I need advices on proper way of doing date compressions.
I am running Navision 3.70b run time, converted from 3.6, with SQL option. I only have one global dimension, which I like to retain. The target is to compress 2003 entries to "Period Length" = ::Year.
This is my plan: ( Retain global dimension "COMPANY")
1. Make a database backup using Navision Backup Utility.
2. Compress G/L Entry then make a 2003 close income statement by global dimension.
3. Compress VAT Entry, Bank Accounts Ledger and delete Check Ledger.
4. Compress Cust. Ledger
5. Compress Item Leger, BOM Ledger and delete Phys. Inventory Ledger.
6. Compress Vendor Ledger
7. Remove Empty Item Register and Empty G/L Register.
Is this a OK plan? When I first did date compression last year by length = ::Month, I screwed up so bad and I have to spend a lot of time "fixing" G/L. Any advice is appreciated! :oops:
Best Regards,
Couber Pu
0
Answers
1/ Why are you doing date compression?
2/ Do you really need to do it?
3/ Is there a better option than date compression?
4/ Are you really sure you want to do date compression?
5/ Are you doing it just to try and save a few bucks on new hard drives?
6/ Are you really really sure you really must do this?
7/ Sit back and ask yourself if you really need to do this?
8/ Have you ever used "Navigate" and is it fine that it may no longer work?
9/ What are the consequences of screwing this up.
10/ Make sure that everyone is aware that there is no going back!
PS: You may guess that I do not like Date Compression.
PPS: The only industry that I have ever seen date compression is in Retail, and then its better to develop a custom compression and not use the standard.
PPPS: Hard Disks are cheap.
I am with you 100% and if it was up to me I would not do this. It was caused by a sequence of misleading information when we first moved to Navision 5 years ago. MS Vendor did not realize the size and growth rate of our business and by the time I took over the in-house support, we already over spend, way over, and the owner did not want to replace our current application server, 3 1.6GHZ CPU with 3GB memory, 8 disk array 50GB database. ](*,)
So I made a read only backup server with 2nd Navision running, any compressed data will be in this 2nd Navision Server, which support up to 5 current users fine.
I think that I put the "missing" data issue at rest for now.
Best Regards,
CouberPu
If in fact you are saving a lot of space, then maybe you need to look at what you are actually doing, and maybe you are storing too much. Have you looked at deleting unnecessary information like old invoices, shipping and receiving document, maybe you just odnt need as many dimensions as you are using.
Also on the Hardware side, do you mean 8 RAID 1 arrays of 16 drives? If so you should be able to expand your db beyond 50 Gig.
I am looking into removing sales shipments, sales invoices...etc as well! But in the mean time I would like to have a date compression plan handy, better yet to use it!
We have a dell poweredge 6600, with 8 disk array, I have 2 18GB raid 1 for OS and programs, 2 72GB raid 1 for log file and 4 72GB raid 10 for database. It is not about the space right now, the owner like to see everything lighting fast, he is a salesperson himself and he took live order with his customer, the problem is performance. When we first live, with initial data moved from our old unix system at 12GB, we were lighting fast. As data grow, system getting slower and slower.
What I am trying to do right now is to check different ways of removing data out of production database, with backup and backup servers, to make the query and write back faster.
Am I at the right track? :?:
Thanks,
CouberPu
I really can't see though that compressing data is going to give you a big boost in performance. I think that to get that, then you need a better design for you system, and you probably need a tune up.
Is the system extennsively modified, or close to standard?
What are the areas that are "slow"? Is this in the area of Data Entry / Posting / Reporting / Or data lookup and searching?
These can be deleted without date compression.
If you're really going to do this save a backup beforehand just in case you need to access them at a later time.
It's just like warrantees - something always breaks the day after it's expired and you'll probably need you invoices & shipments right after you delete them.
I date compressed only once. We were a distribution center for an internet company - they tanked but we had 100,000 5$ to 25$ orders in our system. Since we weren't doing business again with them I crunched just that customers ledger entries but I waited a year just to make sure nothing more needed to be seen.
http://www.BiloBeauty.com
http://www.autismspeaks.org
What do you mean David? It makes perfect logical sense in that less records the database has to go through the faster it results will pop up.
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
I'd recommend just compressing the G/L entries and leave the sub ledgers as is (item ledger, customer ledger, etc).
It's especially important to never compress the item ledger or value entry. I'm not sure if MSFT fixed the compression problems dealing with inventory values, but I just had bad experiences with item ledger compression...
AP Commerce, Inc. = where I work
Getting Started with Dynamics NAV 2013 Application Development = my book
Implementing Microsoft Dynamics NAV - 3rd Edition = my 2nd book
Well what is going to get faster, and why? Before you can determine how to make the system go faster, you need to work out what is making it slower. Less records will mean that inserting records with a large number of Sift Fields will insert faster. And reports that scan the entire record base will be faster, so the Inventory valuation will be faster, and the Adjust cost will be most likely run faster, and posting inventory transactions will be faster. But you agree that compressing the Item Ledger is bad news, so we wont do that anyway.
Yes the global statement that "less records will make the system faster" is true, but how much faster, and at what cost. It would be better to look at the maintenance of SQL keys and reduce the size of keys, than to reduce the number of records.
But again before even starting to look at making the system faster, you need to look at which areas are slow. It would be pretty disappointing to do all that work, and then see a 10% increase in the speed to open a form that takes 2 minutes. since no one will notice that it is 12 seconds faster.
I have been doing performance tuning on Navision for many years now, and always, there is never a magic bullet its always a case of a few percent here, and a few percent there and making the system incrementally better.
Just retraining users on have to set keys and filters when searching can make a huge difference to performance with minimal effort.
What I am saying, is that yes you are correct, but there is always a lot more to performance tuning than just the size of the database.
Anyway there is not much more to add, until we get some answers to key questions about where the system is currently slow.
Hi david,
as u said a few percent here and a few percent there. reckon that the few percent that would result from compressing the entries would help.
in addition, downsizing the ledger entry dimension and g/l entry tables which along with item ledger entry and value entry tables tend to swell might help
Actually, I did G/L entry, Cust. Ledger entry and Item Ledger entry date compression on 2003 and 2004 last Feb and I did pay some price for that, few days and some hair. I have 3 Windows 2000 Servers running year end as of 2003, 2004 and 2005. So user can always got history data when they needed.
I guess I should not to do date compression for now and I need to remove old sales shipments, sales invoice, sales credit memo and sales returns. I will start to work on it right away, please give me some suggestions.
I created our Unix in-house system, based on APPGEN, and we kind of used it as the model to modify Navision and we paid over half M for modifications. It is not standard Navision anymore and I am taking over to keep it going now, not a good thing for me!
The system has slow posting and list performance. My guess is the number of records vs key design caused the problem!
Can anyone willing to help me, I understand it won't be free, tuning the system? :oops:
Best Regards,
CouberPu
Yes it will help, but at what cost? What I am saying is that it might cause more problems that it solves. As Alex says Compressing the Item and Value entries is always risky. My point is that in the end it may make sense to compress some other ledgers, but the performance gain may not be as gret as it would be by tuning other areas.
Sure I am interested