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
- 298 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
Date Compression
nvg
Member Posts: 25
Hi,
I have to reduce the size of my Production Database running on Native Navision 3.70 DB.
The current size of my Database is about 130 GB and the system response is getting slower day by day causing delays in locking and searching.
I am reading all the papers and forums about the Date Compression utility and got scared because many places it is mentioned that Date-compression actually screwed up the data.
I wanted to try doing this myself and so I tested the Date Compression on my TEST DB (which is a copy of the LIVE system).
The Date Compression completed successfully. But when I saw the Item Ledger Entry and G/L Entry, the old records still remained.
The Date Compression Registers show that a lot of lines were deleted, but why are those deleted lines still in the table?
Can anyone please advise me what went wrong? Can I trust Navision's Date Compression utility to be safe and effective? All the papers I am reading argue against using it.
I have to reduce the size of my Production Database running on Native Navision 3.70 DB.
The current size of my Database is about 130 GB and the system response is getting slower day by day causing delays in locking and searching.
I am reading all the papers and forums about the Date Compression utility and got scared because many places it is mentioned that Date-compression actually screwed up the data.
I wanted to try doing this myself and so I tested the Date Compression on my TEST DB (which is a copy of the LIVE system).
The Date Compression completed successfully. But when I saw the Item Ledger Entry and G/L Entry, the old records still remained.
The Date Compression Registers show that a lot of lines were deleted, but why are those deleted lines still in the table?
Can anyone please advise me what went wrong? Can I trust Navision's Date Compression utility to be safe and effective? All the papers I am reading argue against using it.
0
Comments
The question is "have you modified your system"? If not, and you have not touched any objects, and everything is out of the box, then compression can work well. BUT if you have changed anything then you will probably screw things up.
If you are going to use compression, then DO NOT do it alone, have your partner do it, and only after they have shown you a couple of refrence sites that have also done it successfully.
My database is heavily customized and most of the important transactional tables have customization.
I ran the Date Compression on a Copy of the LIVE DB for G/L, Customer Ledger Entry, Vendor Ledger Entry, Bank Ledger, VAT Entry, Item Ledger Entry, etc and the batch ran without showing any errors. It also created the Date Compression Registers which showed a huge number of lines deleted.
Then when I checked the actual tables, the old data is still there. They are actually not at all deleted. Rather new lines with consolidated values are added. The whole balance is wrong now. Old lines are not deleted, new lines are added and god knows what else could be wrong.
Can I delete those old transactional data manually? What can be other ways of retreiving space on the Navision Server to make it work faster?
Hi nvg
you first need to understand your system, and make some definitions. Once you know what you want from your system and what is wrong with it, you can work on resolving the issues.
But right now you are so far on the wrong track that you need to take a big step back. Currently you think that if you reduce the size of your database then like waving a magic wand your system will magically be faster. well sorry to burst the bubble, but almost certainly IT WONT!
If your system is slow you need to find out why and fix it.
I have been doing Navision performance tune-ups since 1994, and I cannot remember a case where the performance issues were resolved simply by reducing the database size through ledger compression.
Start with your hardware. Without knowing anything, I would gladly bet good money that it is configured wrongly. Then you need to look at your business logic, and I will bet more money that the customizations were done to meet immediate business needs, and that you didn't really consider long term performance issues as a part of the initial design.
But anyway, before doing anything, this is NOT a job you should be doing, you need to contact your NAV partner, and they do need to be involved in the process.
Oh and in answer to your question; NO do not go into SQL and just delete records, that is a recipe for disaster.
Get an expert involved to take a closer look at your system setup. Have them verify that your server specs, processors, storage subsystem, RAID configuration, network speed,Citrix setup, SQL Server setup, etcetera, is all set up correctly. You'd be surprised at how many 'state of the art' setups we've seen that were not set up properly for NAV databases.
The size of your database is most likely NOT the problem for your performance issues. More likely is severe overindexing, a database that has never been maintained, possible statistics that are outdated, fragmentation in your indexes, empty SIFT, and on top of that potentially some coding issues.
Stop thinking about date compression as a method to solve performance problems and get some help.
RIS Plus, LLC
Understood what you guys are explaining me. Thanks for the advice.
Now my next option would be reindexing the tables and cleaning the Unused-indexes from the database.
I was under the impression that Date Compression is a good tool to remove archieval data and make processing and searching faster.
Thanks anyway.
Regards,
Ninan Vineet George
No No No Please, this is not a bashing. The thing here is that someone has put you on the wrong track, and we are trying to help. It really does look like you alraedy knew what you wanted to hear, but unfortunately someone has told you wrong.
There are two issues here. 1/ is that the compression routine MUST be modified if you have a modified system, you can't just run it as is. 2/ You feel that running this routine will solve your performance issues, and it wont.
Forget the compression routine and concentrate on the issue at hand. Sorry if it seemed that I was being tough, but its sometimes necessary before you seriously damage your system. PLEASE contact your NAV certified partner, and have them do this, this is NOT something you should od for your self.
RIS Plus, LLC