System Slow during Posting and Freezing Other Sessions

sgg

Hello All,

Could someone tell what could be done to Speedup the system's Posting routine and Prevent Freezing other Sessions? we have the following settings:

Version : 3.60 SQL Option
Database Size : About 46GB

Observation:
Client Session 1 tries to Post a Sales Invoice.
Other Client Sessions are Prevented from making modifications to Sales Documents (Header and Lines). E.g Adding Dimensions, Adding Sales Lines. Also, Other Clients are prevented from posting Sales Invoices. In fact, other attempts to Post Sales Documents results in the Client Session Freezing until the Session 1 has finished Posting.

After Checking CodeUnit 80, what I saw as possible causes are the COMMITs and the LOCKTABLEs.

Questions:
What could be Done to Improve on the Speed of the Posting Process?
Is it Normal that Other Client Sessions are Frozen Whenever they tried Posting Sales Documents Simultaeneously?

nunomaia

Navision during the posting routines makes many tables locks. Newer versions of Navision client have a better optimization of SQL table locking.

During the post Navision will lock the several item ledger entries. If the records are locked no one else can post.

ERP are optimized to reading and create analysis, because most users take most of if time reading and making reports.

bbrown

Locking and blocking are a fact of life with multi-user systems. The goal is not to eliminate them, but to reduce their duration. Don't guess your way through performance issues. Use the tools available and focus on the problem areas. The Client Monitor & Code Coverage can be useful here along with those available in the "Performance Troubleshooting Guide".

One area that I found can impact posting is the updating of unneeded SiftIndexes. In recent testing (DB: 3.60 SQL ~120GB, 4.03 Clients) turning off a number of SiftIndexes reduced Sales Order posting times by better than 50%. This resulted in a sizable reduction in blocking experienced by other users.

kriki

bbrown wrote:

One area that I found can impact posting is the updating of unneeded SiftIndexes. In recent testing (DB: 3.60 SQL ~120GB, 4.03 Clients) turning off a number of SiftIndexes reduced Sales Order posting times by better than 50%. This resulted in a sizable reduction in blocking experienced by other users.

Same thing for the indexes, especially in tables 17 and 21.

sgg

Thanks Guys,
we are currently cheking the Keys to see if this could be Cleaned up.

Hopefully, the Database Performance will be improved after the unused keys have been Disabled.

Which of this two options will be better (that will result in better Database performance) ?

1. Delete Unused Keys
2. Disable Unused Keys

bostjanl

sgg wrote:

Thanks Guys,
we are currently cheking the Keys to see if this could be Cleaned up.

Hopefully, the Database Performance will be improved after the unused keys have been Disabled.

Which of this two options will be better (that will result in better Database performance) ?

1. Delete Unused Keys
2. Disable Unused Keys

Hi!

When using SQL option, then the major issiue are SIFT keys. these are the ones that slows the system down, becouse thy are not support as they are in native database. Disable SIFT key, not the keys itself. It takes some more time to do CALCFIELDS and SUM, but inserting and modifying takes less time, so posting is faster.
"MaintainSIFTIndex" is the property in key wich handles this thing.


C&P from Navision Help:
******************************************************
MaintainSIFTIndex
Applies to
Keys

This property determines whether SIFT structures should be created (when set to Yes) or dropped (when set to No) in SQL Server to support the corresponding SumIndexFields for the Navision key.

SumIndexFields are created in Navision to support, for example, FlowField calculations and other fast summing operations. SQL Server can sum numeric data by scanning the table. If the SIFT structures exist for the SumIndexFields, summing the fields will be faster, especially for large sets of records, but modifications to the table will be slower since the SIFT structures must also be maintained.

In situations where SumIndexFields must be created on a key to allow FlowField calculations, but the calculations are performed infrequently or on small sets of data, you can disable this property to prevent slow modifications to the table.

bbrown

Don't delete or disable keys. Just disable their MaintainSQLndex property. This requires some testing and experimenting to achieve the right balance between read and write performance.