Item ledger entry

Belias

hi everyone,
I have a big item ledger enty table to manage (at least, it's big for me, because i've never worked with high data volumes). It's 9'700'000 records, and about the 75% of its data refers to one single item code.
Provided the always useful advices about hw, indexes, network, application...Do some sql expert know some hints/advices/best practices to manage this particular kind of data distribution?

Actually, I'm (still) not having a lot of problems (except for some weird behaviour of pages that chooses wrong indexes), but i guess that some issues will arise after the go-live.

thanks in advance for any input!

David_Singleton

Why d you have that many entire BEFORE go live. I would look into the volume of history you are bringing across and see how to do it better. Looks like you just imported all the history from the old system. Is this retail?

Belias

Yep, actually, the warehouse is strongly traced (there will be even more item ledger entry history to bring in the nav at golive).
They need the history of all the serial nos. and lots from "day 1". (At least, this is what have been decided by our project manager and the customer).
Obviously, non traced item are not carried over.

David_Singleton

Trust me, no human being can assimilate 9,700,000 transactions, so any reporting you do on this volume will never be used in this form for anything useful.

if its just to track 9.7 million serial numbers, then form day one you need to work out a different way to track them, becasue tracking this volume of serial numbers using standard Navision will not work. You need to look at a separate table to store them.

Mind you if the client does decide to go this direction, please keep my number handy for when you need some performance tuning.

rhpnt

Belias wrote:

They need the history of all the serial nos. and lots from "day 1".

Typical for customers who don't really know what they want so they "decide" they need everything. Just in case...

Belias wrote:

(At least, this is what have been decided by our project manager and the customer).

You or your company should fire the project manager - obviously he's there just for the paycheck - if not worse...

David_Singleton

rhpnt wrote:

You or your company should fire the project manager - obviously he's there just for the paycheck - if not worse...

Maybe he also needs to read my blog.


The most powerful tool that a Dynamics NAV consultant can use.

David_Singleton

Hey Belias, in all seriousness, I know its probably not your job, but you really need to haul that Project Manager in and try to find out what is going on. This is not a good way to start a project.

Belias

rhpnt wrote:

.....Typical for customers who don't really know what they want so they "decide" they need everything. Just in case...

you're probably correct, but i wasn't on this project since it started, and if I were, i probably wouldn't have took part at the first meetings. Anyway, this company take care of goods that MUST be tracked intensively (cylinders for stuff like oxygen and so on). these cylinders can be lent and then returned and the lent another time, then sold etc etc etc....

rhpnt wrote:

Belias wrote:

(At least, this is what have been decided by our project manager and the customer).

You or your company should fire the project manager - obviously he's there just for the paycheck - if not worse...

Even if you have a great knowledge of NAV, I can't accept someone who don't know "the whole story" to blame the PM. It's a person i've been knowing for 5 years, he knows what he's doing and he's always done his job very good with the max effort. I'm gonna ask him if we really need ALL the ILE.

Btw, i think that there are NAV DBs with much more ILE than ours, isn't it? :-k

David_Singleton

The issue isn't the number of entries. It is a case of matching the customers needs to the solution and vica versa. THe adjust cost routine and inventory valuation reports have issues where one item number has a lot of entries to adjust. Committing by item number wont help etc.

To me it looks like they are just using NAV as it comes without looking at the consequences. What was the reason that you didn't develop a custom tracking module for this? the problem is that ILE and VLE track tons of information that you don't need. So if you make a custom tracking module you only tack the information that is needed.

Belias

The project is really big, and i don't know all of its aspects, but i think (other than other things) we need the serial no. informations on the item ledger entries. Serial no. are reused, they get in&out the warehouse, they have their own master table. So...why not use the standard item tracking? even if i need only 10 of the 50 fields (it's just an an example), it does the work: that is bring the data on ILE, update a master table with some fields etc.
The standard ILE was not really available "out of the box"...it has been a hell to customize. But at the end of the day, i feel like it's pretty solid now.
P.S.: we took the decision to customize the standard about one year ago...i don't remember ALL the reasons, sorry...

stryk

Hi Mirko,

well, I don't want to comment on the "sense & nonsense" of that issue in general - just dealing with the fact you have a ILE of a decent size (yes, I've seen bigger ILE!).

One Problem NAV has, that it e.g. calculated the inventory from all ILE - actually not directly, bt via VSIFT structures. Depending on the exact structure of some VSIFT those will grow with together with the ILE. Hence, there might be performance problems with the ILE and some problems with the VSIFT as well.
For SQL Server "size does not matter" - basically - this is primarily an issue of storage. Solving storage issues could be accomplished by ...
... minimizing the creation of ILE (like not migrating all that old stuff )
... clean-up and archiving (which is not available anymore with standard NAV)
... using "Data Compression" of SQL Server 2008 Enterprise (ILE is a quite good candidate!)

The real performance issues rise if SQL Server starts to Scan indexes - a scan means "sequentially reading the leaf node level of an index". For example like reading a phone book page-by-page-by-page... and so on. The bigger the phone book, the longer it takes to scan it. So in such a case "size matters" a lot!
Hence it is crucial to have sufficient indexing on the ILE (and VSIFT) and a high "discipline" of accessing those structures: means, you should not have a too big variety of different filter-constellations, as every filter might require an (different?) optimized index; it would be not feasible to have an index per every filter, as this would dramatically increase the table/index size and increase the "cost per record" when writing data to the table. Too many indexes will delay insert, update or delete transactions!
Thus, indexing is a matter of balance: adding indexes you really (!) need, and removing those which are not used.

The physical order of the records in ILE could also have impact on performance: the default "Clustered Index" on "Entry No." has advantages and disadvantages (see this thread about details).
In some cases it could improve performance to create a new "Key" on ILE by "Item No." and flag this one as "Clustered" - hence, the records will be sorted by "Item No.", "Entry No." which supports most of the filters applied on ILE (downside: the indexes get larger).
Of course, changing the CI is a heavy-load transaction taking long time for 9.7 million records; this should be tested throughly at first!

Belias

I don't want to comment on the "sense & nonsense" of that issue in general

what do you mean? Are you referring to my initial question of the item that owns the 75% of ILE?
if so, are you telling me it's not an issue?(i'm really asking it, i'm not hyronic )
For the other advices, i'm surely following them for what it does concern me. (they are "the always useful advices about hw, indexes, network, application" i was talking about in the first post). Also, I'm bothering my colleagues for what it concerns them (hw, network and so on...).
PS: i talked about this with the PM and he agreed that we should cut out some old entries. It won't be an easy work, because we have to carefully select what to delete to keep the serial no. history coherent (e.g. first entry must be THE FIRST load, then we delete some entries, and then we restart the ledger entries with an unload - to keep it simple)
As always, thanks everyone for the valuable advices

David_Singleton

stryk wrote:

In some cases it could improve performance to create a new "Key" on ILE by "Item No." and flag this one as "Clustered" - hence, the records will be sorted by "Item No.", "Entry No." which supports most of the filters applied on ILE (downside: the indexes get larger).

I would argue against that, since the comment

Belias wrote:

It's 9'700'000 records, and about the 75% of its data refers to one single item code.

Means that Item No. is a very NON selective index. This would reduce the record set from 9.7 million to 7.3 million which would not be anywhere near enough to negate the over head in maintaining the index itself.

As I said in the beginning (and Belias seems to refuse to listen ), the big issue here is not the number of records, it's that Item No. will not be highly selective in this database and that is going to be where the system falls apart.

The only selective key will be serial number.

stryk

Belias wrote:

I don't want to comment on the "sense & nonsense" of that issue in general

what do you mean? Are you referring to my initial question of the item that owns the 75% of ILE?

No, I mean if it makes sense to migrate so many old/historical entries or not. I just want to focus on the technical aspect, not on the business process behind it.

stryk

David Singleton wrote:

stryk wrote:

In some cases it could improve performance to create a new "Key" on ILE by "Item No." and flag this one as "Clustered" - hence, the records will be sorted by "Item No.", "Entry No." which supports most of the filters applied on ILE (downside: the indexes get larger).

I would argue against that, since the comment

Belias wrote:

It's 9'700'000 records, and about the 75% of its data refers to one single item code.

Means that Item No. is a very NON selective index. This would reduce the record set from 9.7 million to 7.3 million which would not be anywhere near enough to negate the over head in maintaining the index itself.

As I said in the beginning (and Belias seems to refuse to listen ), the big issue here is not the number of records, it's that Item No. will not be highly selective in this database and that is going to be where the system falls apart.

The only selective key will be serial number.

Yeah, this should be discussed (and tested) in detail ... it depends on how the data is queried ... selectivity alone might not be the only criteria ...
I agree that a CI on "Item No." would have small/no benefit for queries running on that 75% Item, but I'm little concerned about the 25% other Items ...
Usually it's "Item No." which is part of most filters, thus a physical sorting on that could have advantages - but there can be disadvantages, too, so that's why I want to highlight to test such a change throughly. "Serial No." sounds pretty smart to me, indeed!

Belias

David Singleton wrote:

...As I said in the beginning (and Belias seems to refuse to listen ), the big issue here is not the number of records, it's that Item No. will not be highly selective in this database and that is going to be where the system falls apart.

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

Belias wrote:

It's 9'700'000 records, and about the 75% of its data refers to one single item code.
...Do some sql expert know some hints/advices/best practices to manage this particular kind of data distribution?

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

Belias wrote:

Btw, i think that there are NAV DBs with much more ILE than ours, isn't it?

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.

David_Singleton

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.

Belias

You said

[FIRST QUOTE]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.

but before you said

[SECOND QUOTE]...As I said in the beginning (and Belias seems to refuse to listen ), the big issue here is not the number of records, it's that Item No. will not be highly selective in this database and that is going to be where the system falls apart.

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.

David_Singleton

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.

David_Singleton

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.

David_Singleton

Belias wrote:

You said

[FIRST QUOTE]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.

but before you said

[SECOND QUOTE]...As I said in the beginning (and Belias seems to refuse to listen ), the big issue here is not the number of records, it's that Item No. will not be highly selective in this database and that is going to be where the system falls apart.

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

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?

Belias

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 :oops:

kriki

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.

Belias

kriki wrote:

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

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

kriki wrote:

In the shipment posting, you can also call the receipt posting and in the receipt posting, you just skip the ILE-postings.

this is not very clear to me :-k

kriki

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.

Belias

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

David_Singleton

David Singleton wrote:

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?

any answer on that?

Belias

@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). :-k

David_Singleton

Belias wrote:

@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). :-k

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.

David_Singleton

kriki wrote:

.
That customer has now 23M of ILE (after 2 years)

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.

David_Singleton

One other thing, (and maybe Jorg can also add some comments on this) but I think you also need to put a lot of focus on your Tempdb. You probably need 8 parts, but I think putting those on separate drives will not help, becasue if the system does start to become dependent on TempDB, its going to be focusing on a single CPU core and a single DB part. So you might need to build a separate RAID10 for the tempDB and break 8 temp files on that dedicated array. Though hopefully if you have enough RAM you can avoid excessive TEMP DB usage.


How big is the total DB?