Actinic takes over 3 hours to startup using external linked SQL source on fast server

[tattysilver]

We have recently transferred our inventory of 5000 products from Excel to SQL. The SQL database is located on a remote virtual server and is 6MB in size, using an External Link via ODBC for quite a number of standard and custom variables.

We have noticed a significant increase in speed of navigation through Actinic, which is good. However, it still takes an unacceptably long time to start Actinic up (and by unacceptably long I mean over 3 HOURS. Much longer than the 7 minutes it took using Excel with external linking.)

We have a bandwidth meter that shows data being downloaded at between 500KB/s and 700KB/s consistently during the startup. But with an SQL database of only 6MB in size, to read the entire database logically it should take no more than 10-15 seconds. Over the three hours 10 minutes it did take, Actinic had consumed nearly 7GB of bandwidth reading from the SQL database, almost as if it had read the entire database 1200 times from start to finish.

We use this external SQL database for several applications and I refuse to use 2 databases (one local, one remote) - it would leave room for error and completely defeat the point.

What's going on? Any help would be greatly appreciated.

Matt

[Big Game Hunter]

basic question Matt, is this remote server a hosted server or your own server?

[fergusw]

Matt,
On first glance at your issue I think some feedback from the Actinic developers as to what is happening at start up regarding functions such as the dB caching, polling, data retrieval etc may be what you need to answer this.
If you are getting a good performance with the link during normal usage e.g. navigation etc then this points to something specific with startup processes within the software.

3 hours is unusable, I agree.
Have you thought perhaps about installing SQL express on the Actinic machine and creating the network link between the two SQL servers. That way you eliminate the SQL network issue at startup.
You'd simple require to replicate the SQL dB's across the network - there are several ways to do that. If it's only 6Mb in size SQL server wouldn't even blink at replicating that!

[tattysilver]

Thanks for the replies.

The server we are using is a hosted server. Fergus, how would I go about setting up or accessing a detailed log to send to Actinic or post on the forum? (with DB caching, polling etc.)

With regard to using MS SQL Express, I have already tried opening a MySQL database on the localhost using Wamp and phpMyAdmin, transferring the entire inventory here, linking to it and relaunching Actinic. This time it took just 15 minutes and was very quick at navigating through items once loaded. So it proves the point that the delay seems to be due to bandwidth, but doesn't really suggest why such a long delay.

15 minutes is still double the startup time using Excel! As SQL Express runs locally in effect I would assume the delay would be around the same. Surely Actinic doesn't read the entire SQL database the equivalent of 1200 times just to boot up.

It would be really helpful if someone could post some approx lengths of time to boot Actinic using SQL on a local and/or hosted server so I can see whether this is a problem with my site or just a (pretty serious) problem with Actinic in general. There are many examples of users linking to SQL databases with 10,000+ items that load from scratch in seconds.

Thanks again in advance for any help.

Matt

[fergusw]

Matt,
I've seen sites that link several fields to an SQL database that open normally as well (normally being a few seconds, rather than minutes or hours!), so I would suggest there is either something happening with regards to the data polling happening over the network.
I'd register a ticket with support [@] actinic.co.uk to see what light they can shed on it.
My suggestion was to use a local MS SQL database (e.g. SQL Express (free up to 2Gb in size) rather than a local mySQL database. Same format as your "master" dB and hence a lot easier to replicate across the network. You should find the native ODBC connection might be a bit faster on the windows box that to a mySQL database runing on Wamp (or maybe not!)

Good luck with the hunt - be sure and post your findings here for us all to see

[Big Game Hunter]

matt

I'm not an mysql expert, but when we have worked with databases on our sites and sharing info across bandwith, we have found the most basic of things is usually the issue.

First i would say to look for is do you have all the relevant ports open to accommodate your set up?
Are you running anti virus like Nortons that scans databases, that could be clogging your outgoing bandwidth in the background?
Also have you set the refresh rates on your databses? Sometimes the tables can have a very slow refresh rate especially when they are hosted 3rd party. Do you have a piece of software that can clear your tables?

I host my databases with Fasthosts and have found that their refresh rate on the SQL databases is terrible and sometimes leave me high and dry for hours at a time waiting for their servers to refresh the connections etc. It may be worth giving your hosting company a ring and asking what's their server logs saying their side?

[cbarling]

Matt, which version of Actinic v9 are you on? I know that some fixes have been made in this area in maintenance releases.

If you are on v9.0.4 and still experiencing the problem, I will get someone who is running a special project on performance to get in touch.

Chris

[pmsupport]

Hi Matt,

I understand you are seeing a change in performance
since changing to using an SQL external link

Please can you email me and I can take a look at this performance issue for you? My email address is: pmsupport [@] actinic.co.uk

My hope is to try and recreate the problem if possible and see what is causing this problem.

Paul

[pmsupport]

Hi Matt,

Are you also using Actinic EPOS link?

Paul

[tattysilver]

Apologies for the delayed response with this. Two weeks on and as a temporary workaround I'm using a local SQL database, which loads in around 15 minutes but navigates very quickly. We've been very busy recently and I haven't had time to try to fix this.

Shannon, as far as I'm aware we do have all the relevant ports open to accomodate this. I use an SSH tunnel via PuTTY to allow secure access to the remote SQL database. We don't run any software firewalls or background-scanning anti virus programs like Norton. How would I go about changing the table refresh rates or obtaining software to clear them? I can get in touch with our host admin if necessary but I was under the assumption that this was a client-side issue.

cbarling, I'm using Actinic version 9.0.4.0.0.0.JFJA.

Paul, we're not using Actinic EPOS link. Thanks for taking a look at this for me. I've e-mailed you and hopefully we'll be able to pinpoint the issue.


Thanks again everyone for all the suggestions.

Matt