Batch posting is terrible slow!!! Why?

Hi, All!!! I have HUGE problem with batch posting. I have to post THOUSANDS invoices in one batch. But with operation may take some hours! I cannot find COMMIT, which transfers invoices from unposted to posted. All COMMITs in “Sale Header”-OnDelete, “Sales-Post”-OnRun are disabled, but invoices trasfers in “posted”. Where is this &^%^@#%$^@# COMMIT!

Posting takes some time, because a lot, make the A LOT, of things are happening behind the scenes. For your interest, when posting invoices, the majority of the work is done in the famous codeunits 12 and 80 and these are about the largest routines Navision has. It is strongly advised to stay away from these crucial codeunits, unless you are a very experienced programmer with an very intimate knowledge of the inner workings of Navision. When such a large batch has to be processed, start it at a workstation that can run all day if necessary. Just accept that when a single invoice takes a few seconds, thousand invoices take a thousand times a few seconds. John

May I ask a naive question since we do not even have Navision right now, but looking to purchase it? What “posting” are we talking about? I thought Navision was “real-time”, so …??? Thanks.

When the user has entered and created an sales invoice for example. To have the entries actually update the finacial areas of the system. The user would activate the post option, this post option would for example update the customers balance, reduce stock, update the general ledger etc etc. Navision can still be considered to be real time because even these types of entries ie those before posting can be easily tracked via various screens and reports. Also all areas are updated after posting and no other function needs to take place unlike a number of other systems on the market.

Posting is the process where (at the sales side, as example) an order is transformed into a shipment and/or invoice. In one pass, the system updates the data for the customer, the item(s), the general ledger and everything else related. A matter of a few seconds, while the system runs through numerous processes, does lots of checks and inserts/modifies/updates plenty records in plenty tables. This means that as soon as an order is posted (has become an invoice), all relevant data is immediately reflecting the new situation. Your stock has been decreased, the customer balance is updated, but also (for example) your year-to-date turnover and profit figures are updated automatically. The way Navision handles these database transactions gives you the guarantee that, in case of serious failure, only the data from very last invoice might not have been posted (be lost by DB rollback, but the order is still present to redo posting), while all other orders are processed and are correct invoices (technically) now. And from that last invoice, nothing will be posted at all. No partial posting is possible - it’s all or nothing. This way of committing (the Record Version technique) may cost some speed (if any, in grand total), but prevents you from very nasty things like database inconsistencies. Which other system allows you to (literally!) pull the powerplug out of the server during transactions, and runs error-free after starting up again? John

At least with the spanish version the commit it’s near the end of the onrun function at codeunit 80. :slight_smile: Be carefull when using commits… remember that if an error cancels a process while not reaching a commit all changes done by that process are undone, but if you use a commit, al changes before the commit can’t be undone automatically when having errors. BTW… it’s useful when you havea process that has a lot of records to work with and you want to keep changes of the records you’ve sucessfully work with… but remember using at the point you’re sure the changes done are totally correct if reached (before obtaining the next record you’re working with). That way can keep you from re-starting from begining some tedious lot-of-time-requiring processes… Regards, – Alfonso Pertierra apertierra@teleline.es Spain

Hi Alexey, did the disabling of the commit-command give you more speed ? If not - maybe the problem is located somewhere else. Usually the first some invoices are posted very fast. That could be 20, 30 or maybe 100 invoices, depending on your sever. Then there is a point, where the performance is going down. The time for one invoice to post is much longer than for the first ones. Where exactly this point is reached, depends on the amount of cache your server is started with. Posting into the commitcache is fast, but when the commitcache is full, the performance is speeding down to the capabilities of your storage-system. Check table 2000000010 while running your job on another workstation. In the beginning the load of your harddisks is normal. Then when the speed comes down, the load jumps to 100%. If it’s like that, then you - could increase the server cache - should be sure that the commitchache is enabled - should be sure that your storage-system is appropriate for your database And at last … i hope you made no changes in the posting routines and forgot to use the right keys :wink: Richard

Coming back to the original question >Where is this &^%^@#%$^@# COMMIT!": Commits are issued automatically by Navision whenever an object is closed. If you call e.g. within a form … myCodeunit.Run(myParameters); A commit is issued after the last statement of myCodeunit has been executed. No matter whether or not myCodeunit includes the statement COMMIT or not. Same applies if a report or form is left. Marcus Marcus Fabian phone: +41 79 4397872 m.fabian@thenet.ch

Taking out the commits to improve speed does not work, the server has to keep two copies of the data of the whole transaction. This is not too much of a hit on the SQL version as it does it with roll backs and tranction logs. If you are using a Navision database you will need a HUGE amount of spare database space and the larger the cache the better. Navision will keep a copy of every record changed and a lot of records are changed in posting an invoice. They are only written to the database when a commit is encountered. I have seen a process eat up 200MB of database space, and slow down the more it processed. Paul Baxter

Databases are designed to optmise for commits (rather than rollbacks) which means performing a commit is doing very little work on the server. This is true of both Navision Server and SQL Server. In both cases, there are not ‘two copies’ of the data, although SQL Server is writing the data (or in some cases a subset of it) to the transaction log as well as the data files, so you could say the log is a kind of copy, for recovery and restore purposes. Both systems are writing the physical pages as they will end up at the commit of the transaction, and simply linking them into the page maps on commit. On rollback, the pages need to be deallocated and places on the free list. For huge transactions this can take some time. On the SQL version, there is no benefit in dividing transactions up using commit - but instead is normally a huge headache in terms of guaranteeing consistency.

Robert There are in fact two copies of the data that have been changed. Think of the situation of a processes that is running over a period of time, that makes changes to the database, but does not call a COMMIT. In that situation another user looking at any table that has been changed will see the data before the process started to run. But the process itself will only see the new data. When the process calls a COMMIT then the changed data gets written into the database an another user will see the changes. So before the commit is called there must be two copies of the data. Paul Baxter

What I was trying to say and failed is that the commits speeds up the process of posting. It also would allow other users to post at the same time. I am not sure how safe the posting process would be with all the COMMITS taken out as Codeunit 80 depends on the same instance of the variable GenJnlPostLine being used. There is a commit and the variable cleared to end the posting of an invoice. I am not sure what happens if that commit is removed. It seemed to work when I tried it but what happens in a multi user enviroment? Paul Baxter

The best advice to find anything is to export ALL Objects to text and then use a text editor to find. This requires a full developers license. There are commits in Code Unit 80, but there is also a commit in the Batch posting report that calls codeunit 80. In my experience, removing the commits will make posting go faster but there is are MAJOR drawbacks. If you run into a problem after posting 899 invoices, you will have to repost all of them. The commits in CodeUnit 80 ensure that you will only have to repost the one invoice that had the problem. Also, without the commits you entire system is basically LOCKED until the batch post is finished. This may be an even more major draw back. With a commit after each invoice, other users can get in some work between invoices. As suggested earlier, perhaps a better way to make batch posting faster, is to use a very large commit cache… assuming you are using the right version of the Navision server. In the USA, you can purchase 1GB of RAM for a few hundred USD. So you should put lots of RAM in your server and pump of the size of cache and use commit cache. The faster the processor the better too.

A) Regarding Quang Tran’s question about “real time”: RESPONSE: It’s up to the operator whether posting is done one invoice at a time (real time processing) or all the posting is done in a batch. Navision is real time, but sometimes the organization’s procedures do not fit that approach. B) When posting a large volume of data, Navision “versioning” creates a full second copy of the affected data areas within Navision’s allocated database space. With a large (several hundred or more) invoice batch, that can take up an enormous amount of database work space. Anytime the amount of free database work space gets below about 15%, processing slows down. With free space below 5%, processing slows down dramatically. I would make sure I had lots of free space available. To post 1,000 invoices, I would recommend several 100 MB of available work space…the more, the better. Other critical factors, mentioned in passing by others here relate to disk speed, data path speed, interference from other users or other applications, i.e. all the normal stuff. We have seen a system that was hosting both Navision and Exchange have Navision come to a grinding halt when Exchange woke up and decided to take over. But I would first focus on expanding the database to have lots of “empty” workspace available for my posting run. Dave Studebaker das@libertyforever.com Liberty Grove Software A Navision Services Partner

David, You might want to double check, but I think that when you do a COMMIT, the prior version is released. Jim.

A thought - Are we looking for a ‘deep and meaningful’ answer here and overlooking the obvious ? We had the situation where an Invoice of say 30 lines would take up to about 3 minutes to post. This now takes less than 5 seconds. The problem - NT Database server service tuning for C/Side. We increased the service memory cache size and enabled the CommitCache function.

Jim is right of course. I’ll have to give this some thought. Dave Studebaker das@libertyforever.com Liberty Grove Software A Navision Services Partner

I have to agree with peter Tuning memory and disk space so your day to day operations work best is the correct way of speeding up the posting process. If you take out all of the commits it will take up a huge amount of disk space and NO normal use of the system is possible. You may need to get some more ram and adjust the cache size but I think you would see a much better increase in speed doing that than taking out the COMMITs. Paul Baxter

Paul, You are greatly simplifying matters, though. What you are describing is not copies of the same piece of data in the database, but multiple versions of the database pages, catagorised by a transaction ID. In a versioned database such as Navision Server (which uses the transaction ID) and Oracle (which uses rollback segments), it is true that from a user perspective he may be reading an older version of a record, while that record is currently being modified in a running transaction (this is the intention of this architecture), but in the database the pages are totally unrelated. It is also a generalisation, in that most systems use ANSI isolation levels to protect transactions (there is no ‘versioning’ isolation level), so your example with the batch job is not possible in SQL Server, Informix, DB2 etc, since a user will never see an ‘old’ copy of a record. Blocking will prevent this from happening, or in the case of ‘read uncommitted’ isolation, the user is able to see as yet uncommitted data - i.e. dirty reads. Both of these principles are employed by the SQL version of Financials. I suppose this is slightly off-topic now.

Sure I am simplifying matters. But as I have replied to this post in detail twice and it not getting on the site and this web site only works until 11:30BST that is all I will Say. Paul Baxter Edited by - triff on 2001 Oct 12 12:16:08