Navision Slowness

Tatoshka

Hi everyone!
We have such problem with Navision.

The largest problem is posting outgoing sales orders in the sales order shipping section of Dynamics. We are experiencing delays up to five minutes when several people are trying to post shipped sales orders (pick tickets) in approximately the same time frame. Plus, I post transactions, customer service posts transactions, etc. Even something so simple as posting an item journal adjustment can be delayed as much as five minutes. The error message says something along the lines that you are in conflict with another person trying to post, and to try to post your transaction again later once that person’s done.
Please advice what can be the reason for that. Why Navison become slow during posting transaction.
We used Navision 5.0 SP1 with 30 users and SQL server 2005

Thanks for answers.

Sog

Why is NAV so slow?
Because everybody is locking the same tables/records at the same moment. And it is impossible to let everybody add a record in the same table at the same time. So only 1 person can post at any given time. Next is the types of documents. Even though you post different documents, the ledgers that are updated remain the same.
which means that nearly every document needs to lock the item ledger +related tables and/or the general ledger. Customer/vendor ledgers are updated with other documents.
1 larger culprit is the no. series table. This table holds the document numbers, and will be locked nearly constantly when ppl post. Because the same document number may not be given to different ppl.
This all happens in 1 transaction, either every ledger required is updated, or none. So NAV does the following: lock the tables, check the rows, create new records, release the locks.
If anything happens during that transaction that shouldn't it's rolled back and no records are created.

NAV's posting routines usually block the tables at an early stage, even though alot of checks still have to be executed. Which means a rewrite (not recommended) of posting routines can help. But your NAV partner will probably not like that, because even though there can be speed gained, it will cost alot to do that and/or will create other critical bugs

mdPartnerNL

So, use a NAS to post in the background.

Tatoshka

Thanks for answers
And what about compression optimization of the database? IF it works, I'll be able to reduce the database size which will improve transaction speed ?

Marije_Brummel

Do you have a proper mainenance plan running in the first place? How is your hardware setup? You are running quite an old version of NAV that needs more HW than newer versions.

davmac1

Before rewriting the code and adding a NAS background posting routine, take a look at your systems.
NAV transactions must complete as quickly as possible to cut down on locking problems.
Any users PCS and Servers short on RAM to where programs are swapped in and out of memory.
Is your SQL Server tuned and optimized?
Is your SQL log file placement optimized for writing the log? On the typical smaller system, this means a dedicated hard disk for the log file - RAID 1, and RAID 10 for the SQL data files.
Are you running 32 bit or 64 bit SQL Server?
The 32 bit version is harder to tune because of its address space limitations, and needs AWE set to partially use extended memory.
If the CPU usage on the SQL Server is high in a NAV environment, it generally means the CPU is being used more to handle the deficiencies in your setup.

Tharanga_Chandrasekara

Before doing anything with your code please do check the SQL server performance.
Best thing is to reduce the database log and optimize your database.

Create your trace file using SQL Profiler and optimize the database using that file with the help of "SQL database tuning advisor".
For more information please do check this below link

http://technet.microsoft.com/en-us/library/ms174202.aspx#Create

Hope this would help you.

Cheers!

Alex_Chow

Before you go through the pain of the SQL stuff, please make sure the Automatic Cost Adjustment is set to Never on the Inventory Setup.

This should always be set to Never and is the #1 cause of slowdowns during posting.

Tatoshka

Alex Chow I know it...:) thanks a lot