weekend Topic 2

ara3n

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?

Miklos_Hollender

1) Even SQL 2000 is capable of record level locking, meaning that ideally locking issues could be completely avoided. So I don't really understand why NAV has deadlocks. Maybe there is something wrong the way C/AL is auto-translated into SQL calls. I've heard there were some fixes in SP2 (like setting a deadlock timeout) but I didn't yet test SP2.

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.

Miklos_Hollender

Locking in No. Series is a huge pain and this is exactly something that could be elegantly solved in a 3-tier system: instead of checking and updating Last No. Used field, a service living in th RAM could hold and assign numbers. Of course if the server shuts down without these changes written to the database could be a big problem. I think could be solved the following way: this service writing a change log in a table, just insertion, no locks, and this change log written into the No. Series Line by another service one by one without locks. If the server shuts down, the No. Series Line could still be updated from this change log by this other service.

bbrown

A few comments:

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.

ara3n

Another feature that is in SQL is autoincrement. This allows sql to assign PK at time of insertion. If they improve that feature and make it configurable, you wouldn't need no. series table.

ara3n

bbrown wrote:

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".

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.

Miklos_Hollender

70% of my customers didn't use accounting at all, because they just had an external accountant, who posted invoices and prepared the statements required by the law - and they had ZERO interest in accounting-related management information.

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?

bbrown

Have you really met a company who were large enough to want day-to-day accounting information

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.

Miklos_Hollender

It's strange. If one wants to extract management information from accounting, the one has to keep the accounts down to profit center (Dimension) level. And it's a big pain. Some of my clients wanted to start it, but during tests they found out it's too hard. It's not the software part that is hard, but the calculations: calculating how much a given department shares from heating bills and so on...

ara3n

Miklos
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.

Miklos_Hollender

There is a German add-on for that, it's called Kostenrechnung. I think either it or a similar customization is an absolute must for everybody who wants to seriously use Dimensions...

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.