mibuso.com

I don't refuse, this concern is where the topic started


and also, i was not caring about the number of ILE

So, trust me...I've listened to you, as always! Serial no. is probably a good candidate to be a clusterd idx, we do a lot of queries on it. I've just to check how many entries without serial no. are being generated during the day to day work, because if they are a lot, my CI on serial no. can become less useful.

No! What you are not listening to, is that serial numbers should not be maintained in the Item Ledger Entry table in a case like this. You are creating too much redundant information.

You said

but before you said

and that's why i told i was listening to your advice...(i mean the second quote in this post).

Anyway, misunderstandings apart: I listened to your advice (the first quote in this post), but even if you're correct -and i think you are- the logic behind the tracking we are working on is very complex, and changing the whole module we have developed on the top of the standard nav will be a pain in the a**, now. There's simply no time to rethink the whole module. This is not "solving the problem", but honestly i'm trying to focus on how to improve what we already did (and already works). We're not exploding ILE more than what nav standard already does, so i'm confident we'll be able to make it work as fast as possible: this is what i'm trying to achieve with you (mibuso) and my colleagues' help.

Actually think like this. Does everyone of the 9.7 million items have a DIFFERENT purchase order and purchase price, if not then why would you need 9.7 million (minimum) value ledger entries to track costs.

I can even imagine it gets worse that you may get the idea to start adding more fields to Item ledger Entry to track more serial number information.

All I can say is book mark this thread and come back to it in two years time and see how it went. You will be surprised how fast those two years will fly by, and if it is a problem, then the cost to fix it later will be 10 times what it will cost to do it properly now.

In terms of performance with what you have got, Jorg has given all the advise, it's good advise, so follow it in detail. Monitor all the indexes very regularly. Maybe you will get lucky.

Oh and lots of disks and lots of RAM.

And good luck.

These tw quotes are connected, they do NOT contradict one another. You are creating too much redundant information by storing Serial numbers in the Item ledger entry table. Since the Item No is common and only the serial numbers are different, this causes very low selectivity on Item No, which traditionally is a highly selective key in Navision.

If you purchase 100 items on one order, and they all have the same cost, then you need just one Item Ledger entry, and 100 serial number entries.

If you purchase ONE item on a purchase order, and each time you purchase that item it has a different cost, then you have no option except to have 100 separate item ledger entries.


But anyway it looks like we are flogging a dead horse here, so lets just drop it. Do what you can with tuning and lets hope it all goes well. I assume you have already done performance testing with the existing 9.7 million entries and that it works fine now, so the key is just to do as much as you can to keep the level of performance you have now for the next few years.

Out of curiosity, how long does it take now to run Adjust Cost just on that one item?

yesterday I've had a chat with the PM about it and it reminded me the main reason we've gone for the standard solution:
except for purchase orders and internal transfers (those are just the 10-20% of the total entries), the other entries are generated by orders composed of few lines with quantity 1 or sales return order with few lines of quantity 1. (this is due to the peculiar business of our customer).
now, a separate table to store the tracking is useless (or better, counterproductive), because the result that will be generated by a "day-to-day" order of our customer will be, for example, 3 ledger entries with 3 item tracking in the new custom table.
With the NAV standard solution (that we adopted), we would just have 3 ledger entries with all the infos we need.
PS: yes, this is something I would have told at the beginning, but i didn't remembered it

About internal transfers:
In case your internal transfers are all (or mostly done) in 1 go, you can add a toggle to the transfer header to say it can skip the "In-Transit Location Code" and use directly the "Destination Location Code".
In the shipment posting, you can also call the receipt posting and in the receipt posting, you just skip the ILE-postings.

That saves a lot on ILE's!

We did this for a customer, because that customer has a lot of transfers where shipping and receiving are posted together and it works fine.
That customer has now 23M of ILE (after 2 years) and 27M of Value Entries and we don't have performance problems.

Neat! I'll talk about it with my collagues to verify if it's applicable to our customer's business.

this is not very clear to me

In standard NAV, you first have to ship a TO before you can receive it. So you need to Post it twice.
In this case, while posting the shipment, you automatically want to post the receipt.

How to do that?

In the shipment codeunit, just after finishing the shipment posting (in C5704), just before the COMMIT, you must run the Receipt posting (C5705).
But running the receipt posting will create extra ILE postings (from the transit-location to the to-location) you don't need anymore. So In C5705, you need to skip the ILE-posting for this kind of transfer orders.

oh, ok, it was still related to transfer...i misunderstood your post: i thought you were telling me to post the return order immediately when posting the sales order, so I was pretty confused about it. Ok, cleared out now thank you

any answer on that?

@David Singleton: I'm still waiting for it to finish since you asked me the first time!
jokes apart, I'm investigating about it, i'll let you know as soon as possible. In my opinion it will take a ton of time or it will even error out due to the 2GB limitation (it can happen with big ILE, if i remembered).

I am quite curious about this. Whilst its not uncommon to see this total volume of ILE (10-30 M), I can't remember having seen this many ILE (7.5M) for one Item.

Is that 23M for One item? Or is that total ILE? Again I am really curious how long it takes to adjust that one item.