Inventory (Quantity on Hand) Flowfield

NavStudent
NavStudent Member Posts: 399
edited 2007-11-21 in NAV/Navision Classic Client
Hello

I was wondering why it sums up all the quantity in ILE, instead of summing Remaining quantity.

Wouldn't it be faster, especially since in 5 SP1 they will use indexed views?

The flow field should do a lookup on remaining qty where it's open for that item.
my 2 cents

Comments

  • bbrown
    bbrown Member Posts: 3,268
    What 's the difference? It still needs to sum the same number of records?

    Basing the sum on Quantity only requires that the SumIndex (or indexed view) be updated on insert of new entries. Moving the sum to "Remaining Amount" would also require the SumIndex (or indexed view) to be updated on record modify (item applications).

    Indexed views do not come at no cost. They are real tables stored on the disk and must be updated when the underlying tables are changed. There is a trade-off between the faster access of the indexed view and the additional writes required.
    There are no bugs - only undocumented features.
  • NavStudent
    NavStudent Member Posts: 399
    The difference is that (maybe I wasn't clear), to sum only ILE that are OPEN.

    Granted Inserts will have the same cost, but the real benefit will be Reads.

    Cause You'll only look at a tiny set of open ILE. Not millions.

    Also they should create a separate file group if that's possible for those indexed views.
    my 2 cents
  • Savatage
    Savatage Member Posts: 7,142
    I was thinking that it has to look at every entry anyway to determine if it's open or not :-k
    Harry Ruiz
    http://www.BiloBeauty.com
    http://www.autismspeaks.org
  • bbrown
    bbrown Member Posts: 3,268
    Trying to set a filter on a Boolean field wil gain you no performance advantage. SQL will simply ignore the filter (not very selective) and do a table scan.

    Yes, inserts will have the same cost, but your approach would add the cost for modifies.
    There are no bugs - only undocumented features.
  • NavStudent
    NavStudent Member Posts: 399
    sql will select the key because you'll have only a very small open tries.
    Do a query for an item that is open on sql and see the execution plan.
    Modifcation will have a cost, but the read will be much faster. That’s the whole Point of SIFT.

    Anyways your issues do not matter because the remaining quantity has a SumIndexfield, hence a indexed view.
    All I’m suggesting use one view instead of another.
    my 2 cents
  • ara3n
    ara3n Member Posts: 9,258
    Savatage wrote:
    I was thinking that it has to look at every entry anyway to determine if it's open or not :-k

    Open is flaged to false once remaining qty is set to zero.
    Ahmed Rashed Amini
    Independent Consultant/Developer


    blog: https://dynamicsuser.net/nav/b/ara3n
  • bbrown
    bbrown Member Posts: 3,268
    NavStudent wrote:
    sql will select the key because you'll have only a very small open tries.

    That's an assumption. You will probably get those results where there is a very small percentage of open entries. But what about the system where there is a large number of open entries.
    There are no bugs - only undocumented features.
  • NavStudent
    NavStudent Member Posts: 399
    Find me a system that has been live for 6 months and has more open entries than closed for an item. You can improve the query by setting where remaining qty <>0.

    Also the SumIndexFields should be covering fields in the indexes, so that no lookup to the clustered index is done.

    In the two scenarios, in first you are reading all the records for a given item. In second scenario you are reading a subset. No matter how the records are in the db the subset will always be faster.
    my 2 cents
  • bbrown
    bbrown Member Posts: 3,268
    Back to your original point:

    This discussion is somewhat irrelevant in the context of an indexed view. The indexed view stores the result set of the view and not the individual source records. So whether the sum was on quantity or Remaining Quantity the result is the same. Your approach may improve normal view queries (in most cases) but it would have no impact on an indexed view.
    There are no bugs - only undocumented features.
  • Kowa
    Kowa Member Posts: 926
    NavStudent wrote:
    I was wondering why it sums up all the quantity in ILE, instead of summing Remaining quantity.
    It is true that these two should sum up to the same value, but experience shows that due to bugs in older cost adjustment routines, that this is not always the case. I don't know how many weeks I have spent in the last years on several databases of our clients to fix these old screwed-up ILEs. The quantity field is the only one that you can rely on.
    Kai Kowalewski
  • NavStudent
    NavStudent Member Posts: 399
    yes older version would create just ILE, with 3.x version it creates value entry. And I've seen those db's as well and had to fix a few.
    my 2 cents

Categories