Posting speed problem

SteveLove

I have a processing only report that reads through sales orders, filtered to the current date, and posts them. The same user runs it on the same machine at roughly the same time every day and sometimes it takes 5 minutes but sometimes it takes hours. The number of orders processed are always roughly the same and I've tested it with all other users logged out but it seems to make no difference. I've also tried it on their server to hopefully minimise any network issues but got the same results.

Its NAV 2009 classic on SQL server 2005. Any advice would be much appreciated

Thanks, Steve

kapamarou

Search the forum for Performance, Optimization etc. There are plenty of posts about this. You should start by using the client monitor to figure out where the problem appears. Then you can start investigating deeper (Optimize your tables, Use a Commit periodically, look at indexes, look at hardware etc...).

themave

kapamarou wrote:

Search the forum for Performance, Optimization etc. There are plenty of posts about this. You should start by using the client monitor to figure out where the problem appears. Then you can start investigating deeper (Optimize your tables, Use a Commit periodically, look at indexes, look at hardware etc...).

good generalized info, but it sounds like he has a difference problem, then poor speed. since sometimes it works fast (5 minutes) and sometimes it takes hours. doing roughly the same task, on the same machine with no other users. I am just an end user, but from our experience when we have a task that normal goes really fast and then sometime slows way down, there is something different about some of the orders. and keys are usually the problem. when it post slow, there is an item that needs a different key used, when it is fast. that item isn't on a sales order and the so the different key isn't needed. when I say item and not being talking about an inventory item, it could be a dimension combination, or anything else.

I would try to see if you can determine what the differences are between the fast and slow batches.

kapamarou

kapamarou wrote:

You should start by using the client monitor to figure out where the problem appears.

That's why I proposed that. You can use the client monitor to actually see which insert / read / update / delete / whatever is slow. Your monitor would actually show for example maybe 30 ms to insert to the dimensions table and maybe 400 ms to insert to Item Ledger entry (just an example). There are many reasons for poor performance and with no detailed input about the problem a general start point hint is the only way I think.

Eugene

in case it is a concurrent usage problem when one user blocks another you could try decreasing lock timeout duration. This is database parameter you can set from File-Database-Alter in the Advanced tab of the form