I am reposting this topic from Developer forum, as I could not get any reply there. — Hi! We are running Navision Attain 3.01 SQL option for a year and almost 3 months now. Our database size at the end of February was a bit over 47Gb (with 220000 orders posted). Bearing in mind that we use Navision only to track Sales and Inventory movements (Vendors, Fixed Assets, Production Costing, etc are dealt elsewhere) it seems like a lot. Performance also leaves much to be desired, but we are installing brand new server, which might improve things. We tried to estimate where we are going to end up with our database if sales continue to grow more or less in line with company strategic plans and came to unhappy conclusion: 1. Annual increment in DB size will grow from 50Gb to 70-170GB (pessimistic/optimistic sales forecast) in five years 2. If we proceed with implementation of other modules (Vendors, etc) annual increment will be at least 1.5 times higher This estimate brings up several questions: 1. Does anyone have similar volume of transactions/database size in Navision? What is the maximum size of workable database you have seen? 2. Should we look for a different software solution altogether? 3. If Navision is still the option - what maintenance procedures would you recommend? Quarterly compression? Backup databases for reporting purposes? Building data warehouse to sift [:D] data in it? Any opinion is welcome. Konstantin
One can run a script in SQL which shows number of rows in each table: sp_MSforeachtable “sp_spaceused ‘?’” Anyway 64% of database size is eaten up by following tables: Ledger Entry Dimension 82806468 rows, 6001848 KB Posted Document Dimension 56244921 rows, 6191376 KB Sales Shipment Line 3608147 rows, 4039968 KB Sales Invoice Line 3608239 rows, 3714664 KB Value Entry 4682099 rows, 4767680 KB Item Ledger Entry 4649684 rows, 5649168 KB In total just over 30Gb
Hi, A few questions before I can say anything about your questions : * Are you using SIFT-tables. Turn all this tables off and your database will shrink a lot. Turn only the sift-tables on when you need this (performance reasons). * have you ever used the DBCC REINDEX functions or “optimize” functions in Navision ?? * what’s your fragmentation of your tables ? * Is speed for the moment a problem ? * Which hardware machine you have (Processors, Memory, harddisks (SAN,NAS, …) Greetings, Nico
Remember that you can also use the compress entries functionality on Navision and that posted documents can also be deleted as soon as you don’t have any obligation of keeping them (just the document, not the entries on the journals…) There’s no thing as reasonable database size, it all depends on how many transactions you’re doing… the more you do the more you’ll need. Take care about turning off sift tables… remember that they’re also used by calcfields/calcsums functions and of course by all the flowfields used on the application. Also take care for not adding unnecesary keys on the tables you’re having more data, as it will increase the size of your database a lot… Regards,
2 Nico Sorry for delay with answer, we were trying out Navision optimization feature and with good results so far, thank you. DBCC SHOWCONTIG ([Company$Cust_ Ledger Entry]) WITH ALL_INDEXES shows primary index for CLE as follows: Before optimization: - Scan Density [Best Count:Actual Count]…: 49.11% [2286:4655] - Logical Scan Fragmentation …: 53.12% - Extent Scan Fragmentation …: 70.39% After optimization: - Scan Density [Best Count:Actual Count]…: 99.44% [2137:2149] - Logical Scan Fragmentation …: 0.01% - Extent Scan Fragmentation …: 14.81% Optimization of all tables resulted in about 17% decrease in total database size. SIFT tables are not the biggest problem, but we will try to turn off the ones we don’t really need. 2 Alfonso Thank you for your input. We are testing Navision compression features now, but did dare to try them on live database. The question that remains though is how are we going to get detailed sales reports for compressed periods? Do we need to have a separate database to keep historic data in it?
If you use compression, you can loose some detail-information, but this depends on which tables your reports are based. If the reports are build on ledger entries, than maybe you can delete your posted shipments/invoices. If they are build on posted shipments/invoices, than maybe you can use the compress method. Of course, keeping a second database for history reasons is always the best, but with this size of database, it’s not so easy. BTW, have you ever tried to disable al sift tables in your test-database ? I think your database will shrink with more than 50%. Nico
Just doing some basic Math, (15 months, 20 work days a month, 220,000 orders) gives about 750 Orders per day. This is by no means high volume, and in no way can count for the 47G, unless you have thousands of lines per order. Or maybe an extraodinary number of dimensions. The question therefore is what is creating this volume of data, and more importantly - what is that data being used for. The simple statistical answer, is that there are only so many ways to slice and dice data, and then only so many ways to use it. It may be necessary to look at your business model, and see what information you really do need, and then review how you are using the system. Since you are on SQL, I would be looking at a solution that would take the unnecessary data off line on (say) a weekly basis.
2 David You are right, 355 and 359 tables (Ledger Entry and Posted Document Dimension) comprise about 60% of total database size now. The system was originally designed by a consulting company and the quality of their work leaves much to be desired. Entries in these tables are not used in any reports or functionality as far as we can tell. The question is: can we just purge old dimension entries periodically to maintain reasonable DB size until new system design is generated?
When one of the very large internet companies was still alive & using Navision, we changed the posting routine so that the G/L entries were summarized at the time of posting. At 10K orders a day, the G/L entries were killing system speed. Like your situation, they didn’t need the G/L ledger for navigation. We had tried the the compression route, but it had a huge performance impact on a 24x7 operation, thus the posting modification.
Ahhh…yes. Some things are better left forgotten…as far as business models go. At least they paid well.