Table Optimisation

RachelSoon

Dear All,
Does the system perform the table optimisation during the database restoration?

I try to optimise the detailed customer ledger entry table with 1300000 ++ record, however, the navision still running after 28 hours.

Therefore, would like to find out if the optimisation will take place if we do database restoration.

How would the table optimisation helps in the system?

thank you.

Regards
Rachel

Waldo

Native or SQL?

For SQL:
one of the things the optimisation does is deleting zero sift records. When restoring a database, the sift tables are filled from scratch, so no zero sift are present. In that way, it's about the same.

For Native:
optimizing will decrease the number of levels in the (B+) tree for a particular table ... I don't know if restoring data will have an optimized number of levels in the tree of a table ... sorry.
The document w1w1ism.pdf on the Doc folder of the product CD contains info about it.

RachelSoon

Hi Waldo,
This is SQL.

Thank you for the information.

Regards
Rachel

Waldo

Just for fun (:oops:) I ran a profiler during the "optimize". I could see following things:
1) Getting index information
2) Rebuilding the indexes of that table
(I even saw the attempt of getting a record link during the optimisation between rebuilding indexes and removing zero SIFT records ).
3) I assume deleting the zero SIFT records. However, I found no DELETE or DROP statement (while manually added zero records were indeed deleted). :-k

Anyway, optimalization is also going to rebuild indexes.

kriki

Is the DB optimized for SQL (= LOOOOOOTS of indexes and SIFTS in table 21). After optimizing the DB for SQL you will have a lot less of those and the optimize will go a lot faster.

Waldo

kriki wrote:

Is the DB optimized for SQL (= LOOOOOOTS of indexes and SIFTS in table 21). After optimizing the DB for SQL you will have a lot less of those and the optimize will go a lot faster.

I guess you mean SQL finetuning? Index/sift tuning and such ... .
This is a manual process by the way

kriki

Waldo wrote:

kriki wrote:

Is the DB optimized for SQL (= LOOOOOOTS of indexes and SIFTS in table 21). After optimizing the DB for SQL you will have a lot less of those and the optimize will go a lot faster.

I guess you mean SQL finetuning? Index/sift tuning and such ... .
This is a manual process by the way

I meant finetuning.
And lots of fun!
At least: I consider it lots of fun!