Sage 100

**Custom Crystal report runs slower from Sage 100 menu now that we've implemented the C/S ODBC Driver** -From the Sage 100 menu (at workstation), report currently takes 3min (used to take 30sec). From the menu at server it's fine; currently takes about 10-15sec. -If run directly inside Crystal at workstation it's fine; it takes 30sec. - - - - - - - - - - -I tried re-adding the report to the Sage menu; no help -If we turn off C/S ODBC, report runs fine via the Sage menu -Played with the 'C/S enable' ckbox setting in Sys Config and User Maint; no help (fyi, to my surprise the ckbox is avail in User Maint even though we have the 'enable all users' ckbox enabled in Sys Config) -Initially to get the C/S ODBC working we had to add exception for pvxiosvr.exe to the server firewall. -The reason we enabled C/S ODBC to begin with was because form printing seemed to be sluggish (which is now very fast), even though we're only dealing with internal users; no remote/VPN, etc. users/concerns. I also think the users said the standard Sage 100 reports seem to run faster now as well. **_So why is this custom report now slower?_** Client doesn't have any other existing relevant custom reports to determine if have same issue. -I've compared the config (incl ODBC connection) inside Crystal for a form that's now printing fast and the now-slower custom report, but didn't see anything of help.

What version of Sage? How many users are on it during the day? Alnoor has posted that there is a memory leak in the service. How often do you restart it?

Thanks, @JeffSchwenk. I was hoping you were gonna respond because I thought you had something similar to this at some point, but I couldn't locate it here. Client is running v2017.2. Typically has about 20-25 users in Sage 100. I'll try restarting the Service(s), better yet reboot the server, and I'll also see if I get the same results with no users in Sage. @AlnoorCassim, you have any magic beans/thoughts in your pocket?

The only thing I have left to try is set up the windows scheduler to restart every night. I am perplexed as I use the CS ODBC enhancement as a key benefit for moving to ADV.

Here are a couple that I found. I thought I had a specific one on speed as well, but that is MIA. Found the one I was thinking of. Last one below. https://90minds-com.socialcast.com/messages/38151541?ref=stream https://90minds-com.socialcast.com/messages/36777396?ref=stream https://90minds-com.socialcast.com/messages/38078038?ref=stream https://90minds-com.socialcast.com/messages/35897677?ref=stream https://90minds-com.socialcast.com/messages/35879847?ref=stream https://90minds-com.socialcast.com/messages/38151541?ref=stream And this one maybe the one you were referring to @BrettZimmerman https://90minds-com.socialcast.com/messages/37749684?ref=stream

Are you sure you are running it as CS ODBC? How are you verifying? The slowness could be due to the customer or server firewall processing it through the port. Turn off both and see if it is faster. If it is faster then try creating an exemption for it on both for the port. If you have a lot of users all with the CS ODBC turned on, you might see performance issues. We had a customer who had 65 plus users using it and saw performance decrease. Every print job was going through the server to query the data. They changed it to only 10 or 15 that needed it and everything started to go faster. Really, if the workstation and network isn't slow and the data file being queried isn't very large, then CS ODBC wouldn't be faster.

The memory leak issue Jeff mentioned _usually_ causes reports to stop running entirely or have sudden (Dr Seuss like) ISAM communication errors or sudden slow down to a crawl. It would be relevant here if your custom report was say working fine in the morning then later in the day or next day it took 3 minutes to run. Is it possible in LM / Setup / System Config / ODBC you have the _Enable for All Users_ box checked? I would definitely NOT do that. Instead keep that unchecked and choose Enable CS ODBC Driver in User Maintenance for say 10 users that do a lot of reporting and form printing. As a test, you could create a simple custom report yourself then see if it takes longer to run than expected. If slow still (relatively), then it sounds like a client or server side firewall (but inside the network) as @ToddMartin said. One last thing. A long time ago, I saw the CS ODBC Service was setup with a domain account but not same domain as where Sage App Server was running. There was no error but it was slow **presumably** because of the cross-domain stuff (they had the whole forests and trees of the Active Directory going). I told him him to try using LocalSystem account because that's how you normally setup CS ODBC Service (unlike how you run Sage service).

@AlnoorCassim ...**.try using LocalSystem account because that's how you normally setup CS ODBC Service (unlike how you run ..**... Really? This is new (unless I just don't read the documentation). I usually use the same user running the Sage service for simplicity. Bad??

Unless CS ODBC has changed in recent versions and it may have, a domain account isn't required because the service never opens or runs from a UNC path. That was definitely true in the earlier days not sure now.

So it DOESN'T hurt?

_**Update:**_ The following resulted in 'no change': oRebooted server oDisabled sys config 'enable all users' setting, and enabled setting in user maint for just one user oTried running report with no other users in Sage oTried using both 'Local System' and true 'administrator' for ODBC Service logon Haven't yet tried the following: oCompletely temporarily disabling server/Windows firewall(s) and workstation firewall, just to see if that has any impact oCreating new similar fairly-simple custom report and run from menu from workstation **A couple other items I'm looking for feedback on:** oI'm wondering if the enabled 'spawn' setting in user maint is impacting this. oThis particular report has an invoice date parameter. With the ODBC Service running, the parameter screen pretty-much comes up right away when running the report from the menu from a workstation. But if stop the service, that parameter screen takes about 10sec to appear.