Avatar of bbarr5179
bbarr5179 asked on

Certain reports in Sage are taking ages to generate

Hi,
I have recently installed an SBS 2003 server for a company that replaced a 2K one.  I copied all the files for Sage onto the new server and got it working.  Users P.C's accesed sage data on a mapped drive on the server.  However certain reports are taking 2 - 3 mins to generate whereas before on the older less powerful server they generated within a few secs.  I have checked DNS all P.C's get DNS explicitly from the server. Also I can remote acces any machine on the network and nslookup runs exactly as it should all the shares can be accessed within secs so dont think DNS is the problem. Also  I have Sage installed on the server and it still does it on there with the files local.  Have all the updates for my version of Sage installed it makes no differnce, have also changed the SMB signing policy on the server as another post suggessted no differnce. I am at a total loss it's really begining to get annoying.  If anyone can help it would be greatly appreciated.
SBSTax / Financial SoftwareSoftware

Avatar of undefined
Last Comment
celestial_destruction

8/22/2022 - Mon
Rob Williams

Have you changed ant-virus software? This is known to cause delays with Sage reporting. Perhaps as a test try disabling it briefly.

Have you applied service packs to the server, especially SP2? I know there are issues with Sage and Vista due to the new TCP/IP stack. I wonder if these same additions are made with SP2 on SBS2003. If SP2 is not installed ignore. By the way SP1 on Vista fixes those problems.

I assume it is the same version of Sage?
ASKER
bbarr5179

The server came with SP2.  All P.C's are running XP pro SP2 but they were before and it worked fine.The anti virus software remains the same as before.
ASKER
bbarr5179

I assume by the same version of Sage you mean are all the P.C's running the same version of Sage.  The answer is yes they are all runnning version 11 with all the updates installed.  Thanks for answering I really do apprecaite it this problem is driving me nuts.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
moorhouselondon

I noticed that the word "copied" was used rather than "installed".  There is a possibility that there is something in the installation utility which detects the different environment and perhaps uses a different dll, ini or registry setting.
ASKER
bbarr5179

I copied the files across at first.  Since then I have installed a copy of sage line 50 on the server and set it up to use the copied data from the old server.  However it just does the same as the other machines it works fine except for certain reports which take a stupid amount of time to generate.
moorhouselondon

Oh, if its' just the data that's been copied across that is fine.  Have you checked for errors using the Maintenance facility?  If lots of deletions have been done, or activity such as year end then compacting may help (do a backup first!).

Sage L50 does not use a database engine, so there is no optimisation as number of records increases.  When the report writer searches for candidate records to include in a report, the files are searched linearly, so as time goes on you will find that the more entries on the Audit Trail, the longer it will take, by a similar factor.  How many items do you have on your Audit Trail?  Using ODBC with your own "front-end" doing the reports side of things should achieve better results.  

One quirk I am aware of is that sometimes you have to explicitly put a field into the report design which the generator relies on to produce the report, the field in question would be a field that should be implicitly included, but for some reason has been omitted - the field can be hidden, but it needs to be there.  

Have you tried using UNC path names instead of mapped drives in the Company file on each pc (which points to the server data)?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
bbarr5179

Hi Thanks for youre post

1) How would I go about using ODBC with my own front end to do the reports, I'm not that proficient with Sage you see.
2) Same with UNC path instead of Mapped drive how would I go about it?

As for the size of the audit trail its the same size on the other server the only differnce is a few days worth of info.  But I have used one of the backups from the old server as a test to see if it is size that slowing it down but the new server does the same thing, whereas the old server with exactly the same files which is less powerful opens the offending reports in seconds not mins.
moorhouselondon

Using something like Excel you can produce reports from Sage using the query facility under the Data menu in Excel.  I wouldn't necessarily recommend Excel to do that mind you, as Excel isn't the most efficient of programs either, and as I often say to people, it is a prototyping tool, not a production tool.  Thing about ODBC is that you can produce reports that might span ledgers in a way that would be impossible to do using Sage Report Writer.

In the folder where Sage is installed on each pc is a file called COMPANY (with no extension).  Take a backup of that file and then look at it using a text editor such as Notepad.  If your entries are something like S:\somewhere\company.001\accdata, try using the non-mapped path to the data instead e.g., \\myserver\somewhere\company.001\accdata and see if that makes a difference.
ASKER
bbarr5179

Thanks I will try that.  Don't know if the users will like the idea of using anything but Sage Report Writer, they moan enough if something takes an extra second longer than usual but I will try that as well and see how it goes.  I mean could this just be a slight compatablity issue between version 11 and SBS?  Cos the more I go into it the more it looks that way
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
moorhouselondon

I think it may very well be a compatibility issue between the two, especially as you've pointed out the similarities between the two installs.  I'd guess that v11 came out before SBS2k3 so could well be.  Things I've mentioned are background info to maybe reclaim the situation.  Sage's solution would be to upgrade to the latest version.
ASKER
bbarr5179

Just tried UNC Path on server to see if it would make Sage on the server generate the reports any faster No effect. Thanks though any more thoughts?
moorhouselondon

I note rereading your response to RobWill's comment that it was thought not worth trying to disable the AV - but I would be inclined to investigate that avenue because the behaviour of the AV program is likely to be different on SBS2003 SP2 compared to 2000.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Rob Williams

Also during the AV install different options may have been set.

>>"The server came with SP2"
Came with SP2 or R2? I believe SP2 uses the new TCP/IP stack which apparently Sage has some issues with. However, I would think you we see additional slow downs.

Using the UNC path as suggested by moorhouselondon, is a good idea. I know a trainer for various accounting apps that suggests it often helps with some accounting apps.

Stumbled on this if you want to have a look, but it deals with general slowdowns. "Sage slow performance"
http://www.sbslimited.co.uk/sageslow.htm
moorhouselondon

Hmm, I think RobWill's link has changed my mind about UNC paths in the Company file:-

"The path to the Sage Data is held in the Company File (see above). You can edit this file in Notepad to change the path to the Sage Data files as required. You must use Drive Letters in the Company File, not UNC paths (e.g. \\Server\Data). If your Sage Data is on the Network you must Map a Drive Letter to the Shared Folder location. Make sure the Share has Read and Write Access (this is no longer the default in Windows)."
ASKER
bbarr5179

Hi the link is one i've been to before this is where I got the instructions on changing the SMB Signing.  I have setup the UNC path on the server as I have sage installed on it and it has done nothing to speed up the generation of certain reports everything else runs the same as it does on the normal P.C's.  Also I have mapped a drive letter to the shared folder on the server for the P.C's through the company file using notepad eg E:\Sage Data and its exactly the same.

I was thinking as a work around creating a shared folder on one of the client machine for the data and mapping the P.C's to that, but having a folder o the server for Sage backups to be stored so that when the tape backup was run the sage backups wouyld go on the tape.  Does Sage have any issues with XP Pro SP2?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
moorhouselondon

>XP Pro SP2

None whatever in my experience.
ASKER CERTIFIED SOLUTION
Rob Williams

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
bbarr5179

The reports that are the problem are the ones not standard to sage .  The standard ones that come with sage load as they should in an instant.  Unfortunately the company do not have a support contract with Sage. As for using a share on the XP pro P.c's I would only use it as a short term solution until they could upgrade to a newer version of Sage which at the mo they wont want to do and they need it working properly for Tuesday when their staff return.  They have been managing with it as is but only because they are quiet.  
Rob Williams

I had a client look into Sage Business Vision upgrades. They said the cost was too prohibitive. Eventually everyone as to, so I looked into it. Their cost to upgrade was almost exactly the same cost has having paid the annual fess which would have not only given them free upgrades all along, but support over the past 3 years. The sales fellow said "pay us now or pay us later it doesn't make much difference". Nothing to do with your problem, but you may want to encourage your client to look into that.

>>"the problem are the ones not standard to sage "
Are these using another application like Crystal Reports. Perhaps this is not a Sage issue at all.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER
bbarr5179

No they created them with Sage report designer.
moorhouselondon

Look into the possibility that I mentioned earlier about Fields that shouldn't *need* to be explicitly defined when pulling fields from different "tables" e.g., if you are doing a report of Customers and Invoice Items, without the Invoice Number appearing in the report, it might make a difference in speed if the Invoice Number field is put onto the report, (it can be made non-visible).  I have had instances where  complete nonsense has been generated because a "joined" field wasn't explicitly specified.

Also: look to see if any custom-formulae have been defined on the report which could be executed in an inefficient manner by the report generator.

In the Sorting and Filtering definitions for the reports, have a look to see if the Sorts that have been defined might be taking up a lot of time to generate.
ASKER
bbarr5179

Thanks it was the reports just had to resave them and now it works fine, sorry took so long been away.  Thanks agaiin
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER
bbarr5179

Thanks guys it was the reports themselves all I had to do was resave them.  I didn't even have to change anythig and they worked fine.  Thanks.
Rob Williams

Thanks bbarr5179.
Good to hear it was resolved.
Cheers !
--Rob
celestial_destruction

Hats off to you guys, had been fighting with this one for a few days untill i found this.  Same solution as above, open the report, save, and they seem to work fine.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.