Where do host your database? (1 Viewer)

[perlfan]

Hi there, I'm using Access together with a MySQL database as the backend. Whenever I hosted the database with a shared hosting provider (remote access via ODBC), the connection performance was just not sufficient even though my queries are not that complex. In some tables we have a few thousand records. The server hosting concept is nice, as different users can access the database from any place, but the queries took so long to run... - for better speed, I changed the setup to a local network hosting. The performance now is much better at the cost of no accessibility from outside the network. Can you recommend a setup where I have better accessibility and high performance? Thank you for any recommendations. Frank

 

[Minty]

We use Microsoft-hosted Azure SQL , and provided you are careful with the design and use the server to do the heavy lifting the performance is more than adequate.

They have regional data centres so you should obviously pick one that is geographically closest to your main user base.

 

[amorosik]

... but the queries took so long to run...

If from a local station you launch a query that returns many megabytes of data (so that the communication lasts for several seconds at most), at what speed (in megabyte/sec) are the data returned from hosting to your local station?
A simple measure can also be done via the Windows task manager

 

[CJ_London]

I would investigate using a terminal server or Citrix service. Both FE and BE are on the server (copy of FE in each users profile). Each user logs in using remote desktop and performance is close to having the BE on your local drive. A potential side benefit is a user can connect from any device that supports remote desktop (such as those using iOS or Linux).

You might also want to review the comments and links in this thread to see if you can improve performance

Solved - General Advice for Multi User Split DB

Hi, I feel that my database is performing slowly, are there advice or settings to improve the performance of my database (split database)?

www.access-programmers.co.uk

 

[Josef P.]

the connection performance was just not sufficient even though my queries are not that complex.

Regardless of where the DBMS is used:
How is filtering done? Are the queries designed so that Access can pass the filters to MySQL via ODBC to filter in the MySQL server, or does Access have to filter itself?
Instead of joining linked tables in an Access query, it is more efficient to design the join in the DBMS and link a view in Access.

 

[perlfan]

I think I will try to look into a remote desktop setup. That's maybe the best workaround. Thank you for your input. Frank