Adjust cost - very slow

Lion

I have problem with adjust cost script. I try find somethink abut this but my case is some diferent. In this weekend we update navision from 4.0 SP3 to 5.0 SP1. We run this script every two hours. In nav 4 it work 1-2 minutes but in nav 5 it work sometimes 2 minutes but smoetimes 25 minutes. When I log off all users scrip work 2 minutes, but when I run this script when some users working (posted purchase and sale invoices) script work more than 20 minutes and in tihs time every users can't posting documents becasue they have error about locked value entry table. I don't know why this script in nav4 work fine but in nav 5 no. I think it is problem with locktable command.

Thanks
Maciej

Lion

Sory I don't write that we have database on SQL2005 server.

Thanks
Maciej

Lion

OK. I'm wrong. I log off all users and script work 30 min. I don't know what is hapend. I today I run this script many times at hours:
9:00 - 2 min
10:00 - 2 min
11:00- 10 min
12:00 - 15 min
13:00 - 15 min
14:00 - 30 min

In nav4 this script always work less than 2 min.


Thanks
Maciej

kine

I think it is problem with locktable command.

Locktable is not problem... it is there for some reason, and the reason is good...

Problem is performance of your server/db. Go to performance forum and read through the posts etc.

Fisherman

Kine -

I have to slightly disagree with you.

I'm having the same issuing running this on SQL Server. I have noticed, time and time again, that while this is running, and if other users are in the system posting transactions, I am getting an exclusive database lock, which shuts everyone else out while it runs, and it runs for a long, LONG time. Exclusive Table, Extent, and Page Locks are bad, but you won't send a DBA screaming for the hills faster than when he sees an exclusive database lock. That's usually an indication of very poorly managed transactions. David Singleton suggested that we run it more frequently to decrease the batch size (which we will be trying), but the fact that the job locks three tables in a row PRIOR to fetching the record set and updating is very bad.

In .Net/SQL Server programming, you never lock a record until you are ready to update, or unless your application cannot tolerate updates after reads. In any case, though, the goal has always been stated to lock at the lowest level, and for the shortest period possible. The fact that it locks for the DURATION of reads and updates, which can be a sizable operation, is in my mind a serious design flaw to this code.

garak

oh, there are many reasons, why NAV is blocking ....
first, NAV use SERIALIZABLE ISOLATION LEVEL. Second, per Default many SIFTS (4-version) are active. Do you realy need all these Buckets .... Also, often the SQL Server use a key, that is not so good for the Adjustment. An IndexHint in table dbconfig could help. In our SQL Performance Subforum there are many post about this issue. And don't forgett the Maintenance jobs for SQL

Fisherman

garak -

It seems pretty obvious to me why it's blocking... users are attempting to post entries to a table that NAV has locked for updating. SQL server's default behavior is to escalate lock contention so that updates occur as requested. If a long-running process locks a table for updating for the duration of the run, and others want to insert into that table, SQL will continue escalating the lock to the point where it can complete the update. This isn't unique to NAV - all applications that access SQL Server have to play by these rules.

can you explain what you mean by "An IndexHint in table dbconfig could help"

kine

...but the fact that the job locks three tables in a row PRIOR to fetching the record set and updating is very bad...

But this is base of the locking concept - if I want to read data and to be sure that nobody will change them after I read them, I need to lock them... and making cost adjustment on entries which will change after I read them will lead to inconsistent system. You need to understand that it is not the "LOCKTABLE" command which is evil. Problem is why the batch is running so long... (average costing, posting documents to deep history etc.)

Fisherman

Kine -

I understand what you're saying, but this argument wouldn't hold in any other database-driven application. The reason being that you don't lock more data than you need to complete a single update transaction. This boils down to the ACID test. With this update locking the entire table, and then looping through values, performing updates on each iteration of the loop, it seems that each turn of the loop consists of a single, ACID transaction. Therefore, in database-driven applications, you would lock only the records that are needed to update when they are needed, not in aggregate before you determine what needs to update.

I realize that in NAV, this is not possible, since they don't yet have record level locking in NAV, despite the fact that SQL has long supported it, but I would think that they would shorten the lock period.

Lion

Thanks everybody for all replay.

I don't agree that problem is in server perfomance. When we have nav 4 everybody work fine. Adjust cost script work always work less than 2 minutes. I think that is some difference between version of navision, but I don't know what is the difference.

Lion

Hy guys. No answer for my problem? I have big problem with this script becasue my client work 24 hours per day and today this script work 1 hour and 43 minutes. I run this script every day and in Nav 4 work 1-2 minutes. I don't know why now it work so long. Please help.

philippegirod

Just a question : do you use BOM ? (Manufacturing BOM).
In a BOM it's possible to include the item itself (so he needs itself to give itself, it's a never ending story).
In Nav 4, the process to calculate cost is launched 1000 successiv times (item a needs item a, so calculation of item a who needs item a, so calculation of item a who needs item a, etc.. *1000).
Did you find some strange figure in the Item cost after the calculation?

Lion

Hello, thanks for help.


We don't using BOM, and I think that every entries are OK. I look for some strange entry but I don't find this.

David_Singleton

Lion wrote:

Hello, thanks for help.


We don't using BOM, and I think that every entries are OK. I look for some strange entry but I don't find this.

What costing methods are you using?

Lion

We using average cost method. But in Nav 4 there was no problem.

David_Singleton

Lion wrote:

We using average cost method. But in Nav 4 there was no problem.

I am sure you are aware that costing was completely re-written in 5.00. One of the key issues addressed was average costing, and better reporting. As far as I know perfromance was not addressed. Unfortunately Average costing can introduce some perfromence issues in NAV costing, especially in cases where costs are adjusted a lot, i.e. where there are lots of purchase invoices being posted for items that you have on hand.

I know that there was an expectation by many people that costing would be faster in 5.00, but when you add features it generally means more processing time.