Inventory (Quantity on Hand) Flowfield

NavStudent

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.

bbrown

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.

NavStudent

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.

Savatage

I was thinking that it has to look at every entry anyway to determine if it's open or not :-k

bbrown

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.

NavStudent

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.

ara3n

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.

bbrown

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.

NavStudent

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.

bbrown

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.

Kowa

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.

NavStudent

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.