need advice on optimizing tables in Nav 3.70

orbitalsalmon

Hi all,

I've searched the forums and have gotten bits and pieces of information regarding best practices when optimizing tables in Navision, but am still unclear about certain things I am hoping to get feedback on. Note I am not very familiar with administering Navision so please excuse any newbish questions. Here goes:

1. Is it okay to optimize tables that come back with errors after running the TEST function (such as missing relations, etc)?

2. It is said that you should avoid optimizing "entry" tables such as G/L Entry, VAT Entry, because optimizing them will cause slow-downs when inserting new data. Is it considered a best-practice to never optimize entry tables, or are there exceptions to the rule? (because most entry tables are very big and we'd like to shrink them)

3. Regarding the slow-down on data insertion after optmizing, is it a permanent slow-down, or does the speed improve gradually over time until the next optimization?

3. Finally, what tables SHOULD be targeted for optimization? Here is a list of some "non-entry" type tables from our database which have considerable size:

G/L Register
Item Register
Sales Shipment Header
Sales Shipment Line
Sales Invoice Header
Sales Invoice Line
Ledger Entry Dimension
Posted Deposit Line
Posted Deposit Application

Would like to know if there are detriments/problems in optimizing any of the tables listed above.

thanks,
Jon

kine

Base information we need to know is which server you are using - Native DB or MS SQL?

orbitalsalmon

Native DB, currently 3 GB.

Savatage

orbitalsalmon wrote:

1. Is it okay to optimize tables that come back with errors after running the TEST function (such as missing relations, etc)?

How many of these "Errors" are you getting?
Have you been using Navision for a while with upgrades?

Personally I wouldn't worry too much with a 3GB database. we're upto 17GB and I optimize all the time without problems..BUT I not an expert in the optimizing field.

Here's an old post from me in 2003 discussing Optimizing Tables:
http://www.mbsonline.org/forum/topic.asp?TOPIC_ID=8033

Alex_Chow

Hi,

1. You can optimize irregardless of whether the relation is there or not.
2. Optimizing ANY tables will only increase the performance of it. It will not slow-down the tables at all.
3. You won't experience slowdowns during insertion when you optimize
4. Target all of it.

Don't worry, optimizing is a good thing. The only problem is that it will reset the user settings for all the forms related to that table. But it's not a big deal.

Hope this helps

kriki

deadlizard wrote:

1. You can optimize irregardless of whether the relation is there or not.

Correct

deadlizard wrote:

2. Optimizing ANY tables will only increase the performance of it. It will not slow-down the tables at all.

I don't completely agree with this. After optimizing, reading will be faster, but it will slow down a little for writing. This is because Navision has to create extra space to insert the records in the search-structure. After a while there is again enough space free so Navision can insert new record again in a fast way.

deadlizard wrote:

3. You won't experience slowdowns during insertion when you optimize

See my answer for 2.

deadlizard wrote:

4. Target all of it.

The tables to optimize are the tables that are read a lot, but change little.

If your aim is to get better performance, optimization with a Navision-DB is something that won't get you much (on SQL optimization for ALL tables is good for performance).
For performance-gains, you should check all your programs and for example delete indexes that are only used for a report and reprogram the report. Checking the programs if they are using the correct indexes.