As I wrote last week in part 1 "...I thought I might spend a blog post (or two) and go into details including some sample coding (see Part 2)" and thereby putting myself in debt. So here I am with part 2.
Since last week I had spent some time reviewing and revising the code samples I wanted to present here. However, when trying to get the whole thing work, using the Job Queue, I stumbled - big luck - across some forum posts that referred to a very useful blog by martinni. Many thanx to (a.o.) DaveT and Nuno Maia even if you two are not aware of this. No need for me to upload any code to the internet as it's already there. Let's reuse what's there!
So have a look here where the setup of the Job Queue is explained. Or actually in the (linked) usage example that shows how to implement a posting queue almost identical to the one I had thought of to show you here.
.. Microsoft has never included this neat and lean feature in standard NAV, making it available to all our customers, is still a riddle to me.
With some additional coding it could be implemented on both Sales and Purchase side including all documents. And even be optional through some setup parameter. Seems to me a quick win.
Like probably many of us, every now and then I sit down to search and browse through some of the old postings on dynamicsuser.net or mibuso.com. To learn new things, to get inspiration for a new blog post or just to see what's going on. That's what I did this snowy Sunday morning sitting at the kitchen table with children busy preparing an apple pie and my wife baking pancakes. [Lucky me. ]
Today I was looking at various posts regarding locking issues. A subject of great importance to any multiuser database installation (NAV!). Specifically these three raised my attention:
... as, among others, they included the subject posting queue.
Olden days revived when we had introduced a 2-days performance course as part of our Dutch Navision Solution Developer curriculum, having this subject as one of its topics. As the listed forum postings only touched the subject I thought I might spend a blog post (or two) and go into details including some sample coding (see Part 2).
As soon as multiple concurrent users post transactions (i.e. journals and documents) to the same tables they most probably will be confronted with the down side of table locking; i.e. one or the other finds himself waiting as - paraphrasing the NAV message shown - the ...
... table cannot be changed because it is locked by another user. Wait until the user is finished and then try again.
Like for example multiple users posting sales invoices almost at the same time.
If this waiting for locks to be released becomes a nuisance, in other words a real performance issue, it might be time to reorganize the way transactions are posted. A possible solution is to introduce a job queue, i.e. the posting queue, to which each user 'publishes' the transactions that need to be posted. Once the transaction has been queued the user can continue with her next task.
The basic principle of this posting queue is illustrated by the schema above. Different concurrent users or processes (#1, #2 and #3) insert their transaction into the queue from where one single (scheduled) job posts them to the relevant table(s) one after the other.
As this job is the only one doing the posting no other process will be locking the table(s) and no user will be waiting for a lock.
It's good practice to program dialogs to inform users on the state or progress of a process.
Like in standard NAV when posting a sales invoice:
or a journal:
But did you ever had to post dozens (or even hundreds or thousands) of sales invoices or journal lines? Or had programmed a batch job for you customer with a nice progress dialog and when executed seemed to last for ages?
Please, if you never have known this before, store this in some drawer of your mind at hand to your daily conscious:
With the attached this .fob file we have a simple example to illustrate this performance degradation.
The .fob contains the following form (Test Dialog Performance - 88888):
Pushing the Run Dialog button will execute the following code:
Window.OPEN(Text001);Count := 0;StartTime := TIME;Window.UPDATE(1,Count);FOR Count := 1 TO TotalIterations DO IF Count MOD UpdateDialogAfter = 0 THEN Window.UPDATE(1,Count);EndTime := TIME;TimeElapsed := EndTime - StartTime;
Showing this progress dialog:
If we run the dialog, using the default values, one hundred thousand iterations (i.e. Total Iterations = 100,000) will be performed and with every iteration (i.e. Update Dialog After = 1) the progress dialog will be updated.
On the old machine I ran this test on it took approximately 60,000 ms.
However, if we diminish the number of updates to the progress dialog from every iteration to every hundredths iteration (i.e. Update Dialog After = 1), the total of 100,000 iterations will be completed in approximately 650 ms. So now my process runs almost 100 times faster!
In case your batch job took one night to run - let's say 10 hours - it would be possible to speed it up so it would run in less than ... 10 minutes! Well, maybe not that fast as your batch job will have a lot of lines of codes extra for processing data. Speeding up by a factor 10 is not unrealistic.
I still recall, when I started using Role Tailored Client (RTC), that one of the things of that really surprised me was the error messages that appeared in the yellow ribbon.
Well, it was actually not the message as such, or the yellow ribbon, it was more the fact that the error did not prevent me from moving to another field. Although I did enter an invalid value I could just continue working on other fields. That was totally new to me as, on any error thrown, the good old classic client would block any successive action by a user unless it solved the error, e.g. change the value entered to a valid one.
This memory came to mind when I was reading this useful help topic on the differences between validation on Classic Client and RTC. In the context of my posts on Validating Data I would like to recommend this topic to you.
The help provided with Navision/NAV has always been called online help to the dismay of some people as it really wasn't on-line. Since almost a year, however, this 'online' help has been put on-line. And as I experienced, on some Dynamics User Group posts I took part on, that various developers and users did not know that, or thought this was only for msdn members: there for this small post on Microsoft Dynamics NAV 2009 Developer and IT Pro Documentation that is really on-line now!
Gratefully I have been using it already for my own reference and as links pasted into my posts. The only disadvantage to the help provided with the application is the searching and browsing: it's not as good on-line.