Fast Navision 2.60 database growth

markusmathis

Hello

During the last 6 months, one of our customer's database grew from 40 GB to 80 GB, even though the business transactions didn't increase compared to the years before (during the first 3 years, the database grew from 0 to 40 GB).

Did anyone experience something similar or does anyone know, how something like that could happen?

Thanks for your help.

kine

With more data in your DB, the indexes are bigger and bigger... it is not linear grow... how often do you optimize the tables? Are you using MS SQL or Native DB?

kriki

Did you add new indexes on tables with lots of data (in general T17:G/L entry, T32:Item Ledger Entry,...)?

Eg. on a standard DB without extra keys, 2/3 of the size of table 32 are indexes, only 1/3 of it is the data! So if you add some extra indexes,...........

With that kind of size, I suppose you're using SQL-server. If not it can be time to consider it, but using a 3.70 or also a 4.00 client/server with the 2.60 objects.
With SQL, you can tell SQL not to maintain indexes/SIFT-index, sparing a lot of diskspace and speed. (The more that SQL doesn't even use most of those indexes).

kriki

kine, did someone already tell you that you're toooooo fast?

kine

Yes... :-)

markusmathis

We are using a native DB. We just optimized the DB and it shrank form 81 GB to 71 GB. The situation is now better, but the DB still grew 30 GB within 6 months.

Table 32 alone is 40 GB! As you mentioned, one of the problems could be that there are 29 keys in that table. We had to add lots of keys because the customer was complaining about the slow speed of the DB.

Thanks for your help

kine

Yes, but:

More keys - faster reading (on Native DB, on SQL it depend on the fields in the key and you do not need much keys to maintain)
More keys - slower writing (you need update all keys which is time consuming operation and when you have wring keys maintained on MS SQL, you can lock whole table instead few records...)

You must choose some compromise between fast reading and fast writing, and because writing is more critical (locking other users) - in many cases you need fast writes = less keys...