Hi, I know in theory this should go in Advanced Distribution forum but I think it will be better appreciated here. I am using at a client Attain 3.60 SQL with WMS. And the Warehouse shipments can have a lot of lines in their case. I found in several replies that people said that Navision warned that doing that might degrade performance … Well I never heard those warnings but I experienced them first hand [}:)] My client was complaining about performance when creating warehouse shipments but only from time to time. After a lot of investigations I found out that it would happen for whse shipment with more than few hundred lines. Creating one with 1600 lines would take few hours with 5GB more in the log file… [xx(] Using performance analysis I found the main reason (or one of them anyway). A warehouse shipment has a sorting method option field. And for those that did not have the curiosity to see how it works Navision sets the key on the lines as per the option selected and then fills in a integer field (“Sorting Sequence No.”) sequentially. The subform is sorted by that field so presto you have on the fly sorting for the dumb user that is too lazy to choose a key [}:)] But the thing is once you go dumb you have to do a lot more work. Anytime you add a line you need to run that function if you want the sorting to be right still … So believe or not this is what Navision is doing. In the function that adds ONE sales line to a Whse Shipment it calls the sorting function to update the integer field. So that means : - first line in: 1 line read and modified - second line in: 2 lines read and modified - third line in: 3 lines read and modified … - Line N in: N lines read and modified. So if my highschool memory does not fail me adding N lines to a new whse.shipment will generate a total N * (N + 1) / 2 records to be read and modified [B)] So for 1000 lines added in one transaction you have 500500 !!! records read and modified in that transaction on top of actually inserting the original 1000 records. All of the sudden few hours and 5 GB extra in log file do not sound that surprising anymore. So by taking that function out I have reduced time to a fraction of original. And the thing is I never used that sorting anyway … And even if I did want to use it if I was Navision and was faced to this sort of performance problem and offering that sorting option I would have made a different decision [:(] Can anyone tell me what I am missing? Thanks for listening Cristi Nicola P.S. While writing this I just remembered that Whse. Pick has that sorting too and a whse shipment with 1000 lines will create a whse pick with 1000 lines [}:)] Excuse but I have some code to delete [:p]
I hope You have reported this little feature to MBS in the service system?
I think Navision HQ was too involved in reading the great stuff in http://www.navisioner.com/html/jitsort.htm to even notice the warning the author himself makes:
quote:
José Femenías Cañuelo Although there are some situations in which having a predefined key is the only practical solution, in many other cases, performing just in time sorting -specially after having applied a filter, which may limit greatly the number of records to show- may be a much more desirable solution.
The first time I saw this Sorting Method stuff all around Warehouse Management I immediately hated it. But I also didn’t remember what would happen in extreme cases like yours with lots of lines. It is not like the Navision way from Navision itself…
Hi Lars, As a matter of fact I did not report it… I never heard anything back from them when I was asking a question but I guess it is worth a shot. And really it is not a bug necessarily just a very bad feature … [:D] Cristi Nicola
If you kick up a big enough fuss, something will be done about it. But it depends on how much of a problem it is for you. Performance wise this is definitely a bug, with high priority in my opinion. I hope you get a result.
Performance bug ? Another One : create an item with serial number tracking, try to create eg 1000 SN’s for a purchase order line for example…
OK, nav nav, report that one too then!
well, robert, the last one has been reported in march and is still “in progress”… …but I think in 3.70 HF 8-10 there have been made some improvements on this. it seems faster than in 3.60 and the first version of 3.70