Hello Everybody
Looks like last topic went well. Here is another one. With 3 tiered system in v 5.0. Navision will still of locking issue on the main Tables.
They are Ledgers Entries. and
No. Series lines when 50 people are creating sales orders at the same time.
What kind of ideas would improve the performance? I'm sure AX has thought about that. They can handle a lot more users.
With most 3 tiered system, ledgers gets hit overnight with some processing running at night.
Do you think Navision needs these staging tables with different keys so that table locking doesn't happen, and at night the data gets posted to GL?
Ahmed Rashed Amini
Independent Consultant/Developer
blog:
https://dynamicsuser.net/nav/b/ara3n0
Comments
2) 3-tier systems generally offer better performance as connections are pooled by the service tier instead of each client connecting as and when it sees fit. But I think it has nothing to do with locking.
3) NAV's posting process should be redesigned: posting a document should only handle the Cust/Vend/Item/Value/Reservation entries and G/L/VAT/Analysis View entries should be posted in batch jobs overnight. This would improve performance and would offer many additional benefits (such as being able to use NAV for business operations without configuring the Financials). I think most smart ERP systems like Compiere operate this way - it's ridiculous that you can't print and invoice without posting it to the G/L. It's really bad design - a legacy from 8-10 years ago when it was a purely financial system.
4) Some friends of mine worked with AX 3.0 and the performance was really bad. First, as you can mix procedural OO code with SQL - WHILE SELECT ... DO or something like that - it's easy to do customizations with horrible performance. But even the pure standard had a bad performance as of version 3.0 . The problem with AX is that it's too normalized. F.e. Inventory entries don't have Location Code, because fields like Location, Bin are just Dimensions. And these Dimensions can be configured down to end-user level, so for you Location Code can be Dim 1 and for me Dim 3. Obviously, the code that transforms it into database queries is really slow, printing a simple inventory list is a pain. I have not tested these myself, these are just stories from friends working with AX 3.0 Maybe it was improved in later versions. Maybe not.
Deadlocks should never occur and are usually the result of poor programming. On the other hand, resource locking which may result in temporary blocking of other users is a fact of life with multi-user databases. The system should be structured in a way the minimize this blocking.
Locks are required anytime a database change is made (insert, delete, modify). Changing the database without locks opens up serious data integrity issues.
Just like SQL determines which index to use, it also determine which locking method to use. Record-Level locking is not always the most efficient. Applying Record-Locks to large number of records increases the system resource requirements.
I agree that the posting processes need to be redesigned but I disagree on batch posting the G/L. Today's customers want realtime information and this includes the accounting department. They do not expect to make a sizable inventment (new system) and then be treated as "second class citizens".
It would be nice to see Navision leverage some of SQL' s features (stored procs, views, user procs, etc.) in future releases.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
You do have a good point. Actually this is allready happening on analysis by dimension and accound schedules.
If you uncheck update on posting, then those tables don't get updated and when you run trial balance on income states based on dimension, you don't get the uptodate data.
One of our client was complaining about this. The reason we turn them off was because locking issues during posting, and if it errors out, it doesn't role back. We had do modify the report to run analysis view updates and run job scheduler to update them every 15 min so that the reports run fast enough without noticing that they are running updates.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
For the other 30%, preparing accounting information for the board meeting held at each Mondays would have been waaay frequent enough. Hey! NAV is supposed to be an SMB software, isn't it? Have you really met a company who were large enough to want day-to-day accounting information, and still chose NAV over AX, SAP, Oracle Fin etc?
In the almost 20 years I have worked in this business , I have work with clients that range from 5 to 5000 employees and with very few exceptions these customers considered their core accounting (GLAPAR) to be a critical part of the system.
Current, accurate financials are no longer the exclusive realm of larger companies. Many of today's smaller and mid-size companies compete at very low profit margins.
Also allocation by Dimension does not exists. So that in order to allocate a GL account to other GL accounts, you not only have to balance by GL account, but also by Dimension.
So for example
Amount Department Project
1000 A New
500 A Old
To allocate this to different departments
You have to make two entries for
1000 with dim A and New
500 with dim A and Old
You could have find out the balance for each dimension combination. And guess what that can take your days for just allocation.
Ofcourse I had to write a customization for the client.
Independent Consultant/Developer
blog: https://dynamicsuser.net/nav/b/ara3n
However, the basic problem is not the software, but calculating manually the heating costs of all the tiny offices and when you buy 12 printer cartridges then figuring out how much each department will use... I think it needs one accountant full-time.