Sage 100

Has anyone come across serious performance issues looking up customers and running reports when the number of records in AR Customer approaches half a million? What is the rule of thumb concerning number of records before recommending re-platforming to something more robust? And of course I know there will be many different answers with many different variables - just looking for some real-world feedback. In my case this client has over 300k customers and is running MAS 200 4.50. They are thinking about getting current with Sage 100 Advanced 2015, but I'm wondering if that is just putting a band-aid on a sucking chest wound?

Probably more like putting salt in the wound...

Does your customer have performance issues now?

Yes. I have been blaming the server and the network but am hoping that some of the folks here have larger data sets so I can rule that out.

Solid state drive? The lookup will never be fast unless sage rewrites the ALE.

The data in a lookup listbox is pushed down to the client machine from the server. All the data is rushing through the switch(es). High end Gigabit bidirectional switch with a fully Gigabit network is in order.

@AlnoorCassim - Even in the 200 environment, the ALE information is pushed directly to the user's workstation, NOT to the client server session? Would enabling the CD ODBC driver work any faster?

The querying and generation of the ALE data is done at the server but the actual loading of the listbox of the specific columns of data is pushed down to the wkstn. Just those columns. So if you have in your Customer lookup say 5 columns of data and 1,000 rows of customers to load that's not a lot of traffic at all. At 10,000 rows it becomes noticeable of course but prolly not uncomfortable. At 10,000 rows with 5 people doing the same lookup it becomes more noticeable and perhaps uncomfortable. But now there is contention on the switch and your speed, duplexing, brand, number of switches, overall type of wiring start coming into play. With Cullen saying 300k customers and presumably not adding filters or doing F2 partial lookups or limiting the number of rows returned, that is just nuts Cullen. You can evaluate them going to a fully GB network with high end GB switches and that will help. Moving to v2015 will add salt to the wound like Beth said (btw is sucking chest wound a rugby term? I did see New England trounce Australia). However know what helps tremendously on v2014 and higher is turning off Auto-Complete in System Configuration or at least at the user level in User Maintenance. CS ODBC will not help. SSDs will definitely help on the data generation for the lookup but not pushing the lookup data down.

Even on a proper SQL app you would want to set filters before asking for data with 300k records.

Thanks @AlnoorCassim, and it was New Zealand that whupped the Aussies (that was the World Cup Final, BTW, a match for the ages). I was actually just using the ALE as a point of reference, the performance is not really about the ALE, although yes a partial lookup would be in order. Just testing the ODBC DSN takes over 40 seconds, which seems way too long. CS ODBC Driver has not provided much of an increase, either. Loading tasks takes too long, posting journals takes too long, etc. These tips for increasing performance are great, and I am keeping notes, but my initial query was designed more for the denizens of 90 Minds to fire back with some figures on what sort of large data sets they see out there. Are there clients with more than 300k customers? More than a million records in AR Invoice History Detail? What are the upper limits of Sage 100? I'm sure the question has been asked an answered in other places but I can't find them right now so I'm hoping for some anecdotal information here. Then I can go back and fight with the system, etc. I hope that makes sense and does not give anyone the impression that I don't appreciate the replies so far. Oh, and I'm not sure where I first heard about putting band-aids on sucking chest wounds :-)