Mysql – Running MS Access queries for WAN hosted theSQL database

ms accessMySQL

I have a front end MS Access running through a backend MySQL database via ODBC, I recently moved MySQL database from a local LAN server to a hosted WAN server on Amazon RDS. Now all of my queries are running very slow. By searching online I found the best way is to rewrite the queries in pass through mode so stored procedures are not running locally in Access. I have tried VPN vs ODBC, didn't help.

Problem with this approach is that I have thousands of queries (saved and in code) that run sums, dlookups and other Access procedures, it will take months to rewrite all of them correctly so the queries run on the hosted site.

Is there another quicker fix solution to this problem?

Best Answer

You could run Windows server on AWS EC2 or a Windows Desktop using Amazon Workspaces and try running Access there in AWS's network environment. That may significantly improve performance. Another thing to consider is that network performance can be limited on micro instances.

There might be other advantages of hosting your whole application in the cloud in terms of uptime.

Related Solutions

Improving SHOW TABLE STATUS Query Performance in MySQL

You may want to disable the variable innodb_stats_on_metadata.

According to the MySQL Documentation on innodb_stats_on_metadata

When this variable is enabled (which is the default, as before the variable was created), InnoDB updates statistics during metadata statements such as SHOW TABLE STATUS or SHOW INDEX, or when accessing the INFORMATION_SCHEMA tables TABLES or STATISTICS. (These updates are similar to what happens for ANALYZE TABLE.) When disabled, InnoDB does not update statistics during these operations. Disabling this variable can improve access speed for schemas that have a large number of tables or indexes. It can also improve the stability of execution plans for queries that involve InnoDB tables.

Since you are using Amazon RDS, ask your DB Administrators to disable it. If your DBs are in a Shared Resource, my condolences...

UPDATE 2012-07-25 14:45 EDT

If you can download the RDS CLI, you can adjust innodb_stats_on_metadata in a customized DB Parameter Group. You will have to do the following:

mysqldump data out of RDS Server
destroy DB Server Instance
create DB Server Instance using your custom DB Parameter Group adjusted for innodb_stats_on_metadata
reimport the mysqldump into new DB Instance

Since Amazon RDS does not give away SUPER privilege, that's the only way to change a GLOBAL static option.

UPDATE 2012-07-27 16:34 EDT

Without SUPER Privilege, you can modify a DB Parameter Group for a given MySQL RDS Instance, and launch a reboot with one of the following:

rds-reboot-db-instance <DBInstanceName>
Click Reboot Button in the RDS Management Console

Mysql – SQL queries break our game! (Back-end server is at capacity)

What idexes do you have on this table? If the dbms engine is doing a full table scan, it will completely load a cpu and take a long time. If you have a 2 cpu system and all your data is in RAM, then you'll see 50% cpu usage.

Please create and index, such as

CREATE INDEX BlueBoxEngineDB.TransferIdx1
ON BlueBoxEngineDB.Transfer(Amount,FromUserId,Status);

and then run again your query. It should complete in a glimpse.