Sage 100

Slow Customer Renumbering - Has anyone tried to renumber or merge customers using A/R Delete/Change Customer utility lately? As part of a larger integration project for this MAS 200 4.50.4 client, one of the last pieces is to renumber 24,000 customers. In test company started with a much smaller subset for renumbering but within 6 hours realized it was renumbering at a rate of 7 customers/hour. Obviously this isn't going to work. Is it this slow for everyone? Their data is sizable and I will try it on a different system with a faster hard drive but endemically something seems amiss.

The speed of doing this rates up there with the speed of renumbering Items! They give us a utility that is basically impossible to use unless you are doing just a couple of changes.

I had this also with a customer not long ago that had LOTS of invoice history - server was a nice server...I had to do the renumber/merge in batches of 200 each night and let it run for about 6-7 hours each time. I thought that was just normal :)

I assume you are doing this locally and not over the network. Wow, I haven't tried renumbering in this volume recently but I'm getting ready to do this for a client. I'll need to experiment before executing so as to properly set expectations!

Sometimes we copy the company to a History company, then purge some of the history to make things go faster. Use the History-company for ""ancient history"".

Had this issue when trying to delete over 10,000 customers. I ended up doing a VI job to mark customers as temporary and then purged temporary customers. It ended up taking an entire weekend over a dedicated server, but it did work...

Wow, Alnoor this certain brings back memories. Wish I could repay the assistance you gave me two years ago in AP. Can it be another case of piss poor coding? I could give you a phone number of someone at Sage who could help but there aren't any. Sounds like the WAD is really TLTF.

I do customer merges on a regular basis for a customer, and always have to do them on weekends because it could take 10-12 hours to perform 15-20 merges. Customer has a LOT of history, but it is on a new server and I am running the utility on the server.

You should give Randy Marion a shout out on this. He used to figure out work arounds for these kind of issues regularly. a little code magic.

It will be interesting to see if running this on a different system helps the speed at all. I know that with the bus. framework modules, we're not supposed to need to do rebuilds as often - but I'd bet there is an indexing issue that's slowing this down. Have you run rebuilds keys & sorts on AR and SO (and RMA if needed)?

If doing a rebuild of keys, it might be helpful to check the optimize file check box. This causes it to write a new file with records in order of the primary key.

@JohnBroadfoot - is that working okay, the optimize option? I recall running it once and it wiped out the file. That was a while ago though.

@MoiraGoggin - I have not had that happen to me; although, I am certainly going to make a backup file before trying it again. Thanks for the heads up. Way too many wierd little issues are popping up now days.

Thanks for posting this. It came at a perfect time, as were about to finalize a plan for a customer doing this for 4k customers. We're changing our approach.

Thank you everyone for your comments and ideas - very much appreciated! I took a quick glance at the code and: * Just like non-BF A/R, it processes one entry at a time. So if you had 100 customer renumber entries it's processed 100 separate times. That's why back in 3.x SQL, a stored procedure version of IM Del / Renumber / Merge was created. It used Set based processing so 100 inventory item entries for renumber or merge or delete would be processed together. Although you can't do real Set processing in non-SQL MAS (nature of pvx), you can emulate it for many of the files involving CustomerNo and get more speed. Would be huge change though * Most files with a CustomerNo are renumbered/merged through a dictionary routine (not native to MAS but part of providex). This means less coding. I haven't figured out yet thought if most of the time is being spent here or in the non-dictionary routine. * The non-dict routine looks pretty optimized. I couldn't see any smoking gun performance issues like the A/P conversion one Jeff alluded to. CONCLUSION: The design is not optimal for mass renumbering / merging --------------------------------- @ChuckPeddy - Yes it was local, directly on server. Will try it separately on a fast laptop and after RKF. BTW thanks for all your entertaining stories at Summit. It was great to meet you! @LeeGraham - Good thought! But the bigger integration project is relying on the renamed CustomerNo to be in history. @AmberStoneburg and @EdWlodarczyk - you confirmed my worst fears. At 7 renumbers/hr it will every night for 3 months to do 24k of these. Have to come up with a Plan B or Plan C (which is leave town since the owner may be cleaning his shotgun right now) @MoiraGoggin - There could definitely be an indexing issue. The kCustomer index (alternate key) is found everywhere so RKF should help (at least get me past 7 customers/hr). @JohnBroadfoot - Optimize is a great thought. It does the same thing as the Resize Data Files and the Expand option from Reinitalize Data Files. Used it a bunch may use it here. Moira and John - The Optimize bug that wiped out data occurred between 4.05 and 4.20. And fortunately it occurred only for a handful of data files that matched a certain layout (e.g. GL_DailyPosting). A patch came out for it. But not an issue today. One side note if you're interested. The Integrity Check and Optimize checkboxes in RKF - they're more like radio buttons. If you check either one or both, then regular rebuild doesn't run and only Integrity Check and/or Optimize runs.

@AlnoorCassim can you expand on the Integrity Check and Optimize checkboxes in RKF causing NO regular rebuilding?

When Sage made the optimizations to the inventory renumber process (due to many, many complaints of MAS users) was that essentially code changes like the ones you were talking about that should have been done to A/R?

@AlnoorCassim And with what @ThereseLogeais Is asking do you do the rebuild after you run the Integrity and Optimize???

My first reaction was, ""What?? No regular rebuild? What does THAT mean??"" then felt it may be a really stupid question so rephrased it as, ""Alnoor, can you expand on that, please?

I'll answer the RKF questions on a new thread tonight or tomorrow morning and will review the code on 4.40+ too to see if it changed any. Right now somebody is cutting up a switch and I might be on receiving end ..

@AlnoorCassim I hope you don't mind but I think this is a very important topic so I blogged it on 90 Minds (very generally) and Sage 100 LinkedIn to give people a heads up. I've done these types of DRM before and never would have thought to consider that the speed has been changed. This is a pretty important topic because it could catch quite a few of us who try to accomplish the same thing. If at some point we can help to shame Sage into fixing this let me know

Thanks @WayneSchulz - Also I'm done with this for now. I ended up testing on a much faster computer and got it to renumber at 13 customers/hr (from 7/hr). Also find several files that had no index at all involving ARDivisionNo and CustomerNo (e.g. SO_LotSerialHistory) and after taking care of that, I'm at 16 customers/hr. Converted that data to SQL 4.50 and it's 15 customers/hr . Statistically the speed improved over 100% but still way too impractical to do a project involving thousands of customers with real client data. Some type of Set processing is necessary to take this to another level. @DawnAnastasi - in version 3.74 SQL, specifically 3.74, they did make stored procedures for certain portions of AR customer DRM and AP vendor DRM. For A/R the proc was only for where DivisionNo + CustomerNo in an index like SOE, ARV, PO1, POP, etc. (which coincidentally are part of indexes in 4.5 either). For A/P, it was GL_20 and JC3. There were also a special T/C Period End proc created as well.

Shame Sage? This only works if they have any institutional pride. I guess the question that could force their hand is to figure out how material this problem is. My client base is such that I doubt this would ever come up. Alnoor, is there anyway you can test this with the 2013 Alpha code? A long shot, but maybe it has been resolved. If it hasn't, by submitting it as an issue while it is still in alpha, it ""could"" get fixed. Yes, another long shot..............

So theoretically you can renumber merge 360 customers a day -- people would likely do this over a long weekend for a big project - let's assume 360 x 4 days (Fri-Mon) 1,440 customers

We are on the horns of exactly this dilemma. We have to renumber 1500 customers. The only option we can come up with is runnign ~100/night for a couple weeks.

@JerryNorman - Since everybody's mileage may vary based on size of data, amount of history, speed of disk, etc, to get a better idea, consider making a copy company, and renumbering in there for 1 hour only to see how many customers completed then extrapolate that to get total number of hours. Then set expectations accordingly. @JeffSchwenk - I don't think it can be fixed anytime soon as the issue is rooted in the design. Some gains are achieved for a handful of files by temporarily adding an index (involving CustomerNo) during renumbering I don't have an available VM to test v5.0 (u.k.a. 2013) but if somebody can send me these files from v5.0 I can do a code compare. AR_DeleteChangeCustomers_ui.pvc AR_DeleteChangeCustomers_bus.pvc SY_DeleteChange.pvc

@AlnoorCassim - (I'm answering for Jerry) I did that already. The customer is running on an AppEazy system running MAS200 v 4.4 pu6. My test yielded about 12 per hour. Not great but not too horrible. Fortunately, we are not under any pressure to do the transition in one shot so we might be able to spread it over those 2 weeks.

I merged 160 items last night and it took about 4 hours to complete

I just noticed Sage posted on the LinkedIn group for Sage 100 ERP -- Sage is aware of the performance issues and I would like to give you an update. We were able to make significant performance improvements for IM Delete Change which are available on 4.4 PU9 and 4.5 PU3. Those of you coming from 4.4 or prior to the 4.5 release will more than likely notice that AR Delete Change is slower. This is due to the many tables impacted by the addition of the National Accounts enhancement. We have made some global changes in 5.0 that we feel will help with this issue. We are still researching the possibility of additional changes. http://www.linkedin.com/groupAnswers?viewQuestionAndAnswers=&discussionID=156249238&gid=1644357&commentID=95124419&trk=view_disc&ut=2t4YzOpyTFK5o1

Elliot Prichard, one of the main Sage 100 programmer, reached out to me today. As Wayne posted above, Elliot mentioned that they had some improvements to the routine in the 2013 release. He was looking for some data that was demonstated long times during renumbers. @AlnoorCassim I gave him your information.

Thanks @JimWoodhead ! I did talk to Elliott and it was pretty productive. He made a big change to the Sy_Selection.pvc program which should greatly improve the speed (relative to the current speed) so I've converted my client's 4.50 data to 5.0 (u.k.a. 2013) and am running several kinds of Renumber tests. I also mentioned my own idea to him of adding temporary indexes on tables that don't have CustomerNo as an index today (to make the selections go faster). Apparently that's exactly what they did to improve Inventory renumber merge speed but it's not on A/R yet. Anyway, I'll report back to him and this thread later in the week with test results so we'll all know where we stand.

Attached are the results I sent to Elliot. In short on 4.50 PU4, I can renumber 13 customers/hr (on a fast machine). On 5.0 / 2013, using same data (converted), it goes up to 29/hr primarily because of his Sy_Selection change. Then when I add temporary indexes (keys) for CustomerNo to 5 data files involved in renumbering, renumber rate goes up to 39/hr. Hopefully Elliott will be allowed to roll in my temporary indexes idea.

Well, I guess everything is relative. With the 2013 and index modifications, speed increased by 300%, which is very impressive. Until you consider that the conversion still takes 600 hours to crank through your 24K renumber database........ Will MAS run on a super computer?

Yes exactly it is all relative! 24k is unusually high but 4k - 6k is not and it would still take a bunch of iterations over several full weekends