weekend Topic 2

ara3nara3n Member Posts: 9,250
edited 2006-08-02 in General Chat
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/ara3n

Comments

  • Miklos_HollenderMiklos_Hollender Member Posts: 1,597
    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_HollenderMiklos_Hollender Member Posts: 1,597
    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.
  • bbrownbbrown MAMember Posts: 3,231
    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.
    There are no bugs - only undocumented features.
  • ara3nara3n Member Posts: 9,250
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • ara3nara3n Member Posts: 9,250
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Miklos_HollenderMiklos_Hollender Member Posts: 1,597
    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?
  • bbrownbbrown MAMember Posts: 3,231
    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.
    There are no bugs - only undocumented features.
  • Miklos_HollenderMiklos_Hollender Member Posts: 1,597
    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...
  • ara3nara3n Member Posts: 9,250
    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.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • Miklos_HollenderMiklos_Hollender Member Posts: 1,597
    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.
Sign In or Register to comment.