Access Database timeout

[karlkrist]

Hello all- I have a site hosted by HostMySite. This site USED to be pretty
rock-solid, but I have been having tons of problems lately. My issue is that I
keep having problems with the database timing out. This site runs off of an
Access database, but I don't see that as too much of a problem, because I only
get 300-600 page views per day. I have noticed though, that the problems are
usually initiated by a search engine. So let's say that MSNBOT comes through
to do an index and hits the site fairly hard...well, it tanks the site, and
nobody else can access it for about 20 minutes, until the server finally comes
out of its funk and will access my database again. I was hoping to use the
TIMEOUT attribute for the queries, but they don't seem to work with Access.
This query is actually optional for the pages. Each page has a story, and
people can post comments, the comments are displayed at the bottom of the page.
If the database has an error other than timing out (let's say if my query was
bad) then the system will degrade in a way that is fine ('Message System Down'
error at the bottom of the page) BUT- the timeout not only takes a long
time...but it throws an error that gets handled by my 'Whoa...an error' page.
It is treated as an error on the page, not an error in the query (hope that
made sense). (I've got a CFTRY around the query...so an error there gets
handled) Is there any way to trap this error quickly? Timeout would be
perfect, but I don't think it works. Does anyone have any suggestions? My
worst-case scenario would be to not run the query if a search engine is
detected. But this would be defeating the purpose, because the comments do
have a lot of good information (and search terms!) and I would prefer not to
lose them. One other option is to upgrade to SQL Server - but this is such a
small site (like I said 300-600 page view per day) that SQL Server seems like a
waste of money (an extra $160 per year). I have run sites that had 5,000 +
views a day on Access, and they ran fine - albiet there were only a few sites
on a very capable server. My last option is to switch hosting services. I
have contacted HostMySite about this problem at least 8 times, and their
response so far has been 'ummm...I don't see why this would be happening.'
Which doesn't go very far towards fixing the problem. So- if I do switch
hosting services, are there any recommendations in the $15-$20/month range?
I'm not looking to pay $5 per month...but I don't want to pay for SQL Server
either. Long message, but thanks for getting this far. Karl

[Dingonose]

CrystalTech ([url]http://www.crystaltech.com/cfmx_sharedhosting.htm[/url]) has a plan for
$16.95 a month that includes either MySQL or MS SQL. The support is absolutely
first rate.

There is some sentiment that Access is not the best choice to serve a site, as
it has problems with any kind of significant load. Remember, too, that on a
shared server, the amount of traffic other sites on your server can have an
effect on your site, which can be an issue with Access. Over at CrystalTech,
when there gets to be a problem with site that is using Access, they really
encourage you to move to MySQL or MS SQL which handles the loads much better.

HTH,

James Edmunds
New Iberia, LA

[tjfrevert]

Could you cache the queries? Depending on how your site is setup you theoretically could load up one big query into the cache and then use CF's query of queries for each page.