mibuso.com

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!

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?

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.

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.

Typical for customers who don't really know what they want so they "decide" they need everything. Just in case...
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.

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.

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


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?

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.

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

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!

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

I would argue against that, since the comment



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.

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.

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!