Optimization..

yuppicide

When I go to File, Database, Information, there's a button that says Tables. In there I see an Optimize button.

Besides the obvious, what does it do? Will it possibly help anything if I do them all?

I did two tests, one was at 95.something% and after optimization it's 97.something%. Another onr was at 85.something% and it's now 97.8%

Savatage

Doing all won't hurt. It will lock the table it's working on - so feel free to select all & optimize at the end of the day as you're walking out the door.

It will clean up blank spaces n such and reduce the database size used. There's been discussions on if it actually increases speed. It might but it's probably un noticable. I try to do it once a week.

yuppicide

Thanks! Yeah, only takes a few seconds to do.

kriki

It does different things on SQL and on native.

Native: because you create/modify/delete records, the indexes (stored in a B-tree) become fragmented and NAV balances the B-trees again for the indexes (making space the indexes use smaller). This makes that reading becomes faster, but writing becomes slower because there is no more place to insert new records. My advice is to do this ONLY for tables that change very little. So no sales lines, or item ledger entries. But yes for customer, vendor, item.
The only way to do this is manually or using some keylogging tool that sends the keys to the NAV-client (for NAV it still seems manual...).

SQL: It does a SQL index rebuild using the fillfactor of the table/index. It does NOT use the fill factor defined in the server properties. This value is only used when creating a table/index and if you don't specify a fillfactor. The fillfactor of a table/index can be changed by using the rebuild index command and specifying a new value.
In the versions until 5.0 without SP, it also does a cleanup of the 0-sift records (and in some versions that created some problems).
It is best not to do it through NAV, but do it by using the rebuild index of SQL (there is a maintenance plan you can use if you want). The cleanup of the 0-sift-records should not be necessary anymore because you should be on 5.0SP1 or later.