Performance VERY slow after technical update

wirtnix

hello. i just performed the technical update from 2.60 over 3.01 to 3.70
(SQL Server 2000, 160GB of database size)

now the performance of the sales-invoice + order-invoice booking function is very, very slow.

about 6 minutes for 1 invoice...

could i increase it by updating the statistics with the query-analyzer?

kine

May be yes, and do not forget, that 3.70 need more RAM for data cache. And may be that after few postings it will be better, after the SQL have new statistics etc...

wirtnix

well, the machine has 4GB RAM but the SQL server never takes more than 1.8GB....

is there any option for that in the sql-enterprise-manager?

kine

It is based on the version of MS SQL and OS... see section maximum amount of physical memory... in books online...

and do not forget, that if you have more than 2GB RAM, you need to use /3GB swith in your OS....

If you have more than 4GB, you must use /3GB and /PAE swithch and on MS SQL server the /AWE switch...

pri_1_ranjan

you can run the optimization process if using sql.

In Menu Bar - File - Database - Information .
Click tables . this will open other form.
here you can optimize the tables.
The activity such as posting becomes slow as navision system for flowfield creates $3 tables .. Once the operation is over they do not delete the tables. Hence these tables are left redundant . All the performance issues are related to database size.
Get in touch with some DBA to optimize the process for you. but do follow the abouve steps

the performance will improve slightly but not drastically .

hope this helps

kine

There are many things to optimize on Navision SQL Option. One things are SIFT indexes on working tables (you can disable SIFTS on MS SQL in Key properties for working tables like Sales header and lines etc...) - but it is only one example...

kriki

In Menu Bar - File - Database - Information .
Click tables . this will open other form.
here you can optimize the tables.

It is better NOT to optimize tables that change constantly, because with optimize they are optimized for reading and diskspace, but writing becomes slower.
The reason is that the indexes have a tree-like structure and each node (=the place where two or more branches come together) takes diskspace. Sometimes (most of the time) the tree is not balanced, so it takes more diskspace and there are more nodes then really needed. Optimizing balances the tree and removes the nodes that are not needed. Problem is that for inserting a new record, time is lost because a new node has to be created.

The optimizing process was (very) usefull in the time that you had to buy every MB of DB-size (before SQL on Navision). With this you could limit a little the DB-size you had to buy.

kine

Kriki - this is not applicable to the MS SQL server - on ms SQL server only indexes are defragmented (it is good for reading too - you do not need to read many pages into memory to read the data, for writing too - no many pages - sometime it is problem of the full pages, but with this the pages are reorganised to have free space for new records - it is good for writing), statistics are recreated (it is good for MS SQL to be able to select good index), and from SIFT tables are 0 values deleted (in some cases you have 100,000 records in SIFT tables per 100 records in the data table)... it is needed for good performance on MS SQL...

wirtnix

the performance was slow because the "Artikelposten" table had to make sums for millions of data. a sum index was created for one Key and the problem is now solved.

Sorcerer

for all non german speaking people: "Item Ledger Entry" Table