Access 2010 with MySQL ODBC linked tables slow
Posted on 2013-05-15
I am having an issue with Microsoft Access 2010 and linked tables to MySQL (operational version is 5.5.28 ... my test setup is currently running 5.5.29-MariaDB) through the MySQL ODBC connector. The linked tables are connected to through a file DSN on a shared network folder.
We run two front ends (FE1,FE2) in Access 2010 connecting to the same back end DB server with 6 different back-end databases for a total of approximately 85 tables linked in each FE (each FE has two BEs that are specific to that FE, and the other two BEs are shared between them). All 6 DBs total 200MB of hard drive space, the MySQL server is running linux (Fedora 17) with 12GB of RAM with an SSD and Intel I7 quad-core processor. We are a 24/7 business, our number of users of these databases ranges from 1 during the overnight hours to 12 in the middle of the day. All users are running 64-bit Windows 7 with 32-bit Access 2010. The forms and queries in both DBs are complicated with many subforms and complex join queries.
Towards the end of April, FE1 started running very slow. When opening the main form, the screen will turn white and "running query" will appear at the bottom for at least 5 seconds multiple times. Before the end of April, this never happened and these databases have been running for years, first in Access 2000 with MDB backend, then in January 2012 the BE was ported to MySQL, and in January 2013 the FEs were ported to Access 2010.
This white screen with "running query" also appears when switching records on the main form and other common functions that our users do throughout the day resulting in lots of frustration for our users. FE2 continues to be quite fast and does not have this issue, though the layouts and purposes of the FEs are a bit different. Also, the Track Name Autocorrect option in Access is disabled. Each user runs their own local copy of the FE which is converted to ACCDE and then changed to ACCDR so it runs in run-time mode. The problem occurs in all formats (ACCDB, ACCDE, ACCDR) on all computers in our office though.
From testing using a backup from the beginning of April, we have narrowed the cause of the slowness down to one table that is specific to the FE1 database that has 2550 records it with 66 fields, some of which are Text (Memo in Access) fields.
I have tried the following to try to alleviate the slowness:
1. Tried to run repair and optimize on every table with no difference.
2. Migrated the MySQL DBs to three different DB servers of varying computer specs, the current test setup I have is running a six-core Intel I7 with 64GB of RAM with SSD hard drive. Kind of overkill, but the problem still occurs in my test setup where I am the only user connecting to this DB.
3. I do have my.cnf changed from default for the operational MySQL server and further adjusted for the test setup hardware, although I do not know if it is perfectly optimized for the hardware it is on. However I have tried changing many of the options both in up and down direction and nothing has made a difference. The slowness does not seem to be stemming from the MysQL server itself though.
4. I did try using MariaDB after reading that it has some optimizations over MySQL, but did not make a difference.
5. We are running MySQL ODBC driver version 5.1.9 operationally. In my test setup I updated to 5.2.5 and it did not help.
6. Changed all Text fields to varchar(255), no difference.
7. I ran ODBC tracing and was able to get the query that is causing the white screen and "running query" when opening the main form. It joins 3 tables that have ~400, ~2550, and ~1600 records on their primary keys or indexed fields. I copy/pasted the query into phpMyAdmin and it ran in 0.0294 seconds with about 1550 records returned. This query includes the table I mentioned above that we narrowed down the cause of the slowness to.
8. I have also enabled the slow query log and lowered the slow query flag to the minimum of 1 second, but no queries ever appear in the log. I believe now that this is because the slowness is not related to the MySQL server.
9. At this point, it seems to be an Access / ODBC issue but I have tried changing options in the DSN files but again nothing seems to make a difference. If necessary I can post my DSN file.
I am stumped and hoping someone here can point me in the right direction for possibly some sort of ODBC option/setting to change or Access setting to change. If any more information is needed, please let me know.