Access 2010 with MySQL ODBC linked tables slow
Hello,
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.
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.
ASKER
Hello, thank your for your reply.
Today I have tried the following based on your suggestions...
1. Compact/repair, compile, compact/repair. No change.
2. Decompile, compact/repair, compile, compact/repair. No change.
3. Confirmed Access is seeing the indexes by viewing them in the Indexes ribbon item.
4. Changed indexes... the primary key in the "slow" table was a varchar(20) field but the largest string in it is currently 12, so I made it varchar(12), No change. Removed all other indexes on that table, no change. Removed the primary key and added a new autonumber primary key, this made opening FE1 significantly slower. Added a unique index on the varchar(12) field, which brought FE1 back to the original slowness.
5. Tried creating a blank database and importing all objects into it, then re-linking the tables... no change.
Today I have tried the following based on your suggestions...
1. Compact/repair, compile, compact/repair. No change.
2. Decompile, compact/repair, compile, compact/repair. No change.
3. Confirmed Access is seeing the indexes by viewing them in the Indexes ribbon item.
4. Changed indexes... the primary key in the "slow" table was a varchar(20) field but the largest string in it is currently 12, so I made it varchar(12), No change. Removed all other indexes on that table, no change. Removed the primary key and added a new autonumber primary key, this made opening FE1 significantly slower. Added a unique index on the varchar(12) field, which brought FE1 back to the original slowness.
5. Tried creating a blank database and importing all objects into it, then re-linking the tables... no change.
What about if you directly open the linked table? Any slowness there?
What if you create a new, blank db, link JUST THAT TABLE, and create a form based on that table? Any slowness?
What about complexity, as I asked earlier - are you using a form/report with many subforms/subreports?
What if you create a new, blank db, link JUST THAT TABLE, and create a form based on that table? Any slowness?
What about complexity, as I asked earlier - are you using a form/report with many subforms/subreports?
ASKER
Hello, thanks again for your reply.
Directly opening the linked table seems fine, no different than opening any other table. It opens immediately.
I did not create a new blank ACCDB and create a form based on that table yet... we have been working hard on this issue and we are looking at several different "sources" of possible slowness. One source is every time the main form is refreshed from VB code, it takes up to 20 seconds. There are a lot of subforms, some of which don't need to be refreshed every time the main form is refreshed... I am wondering is there a way to prevent a subform from being refreshed when the parent form's refresh method is called?
Directly opening the linked table seems fine, no different than opening any other table. It opens immediately.
I did not create a new blank ACCDB and create a form based on that table yet... we have been working hard on this issue and we are looking at several different "sources" of possible slowness. One source is every time the main form is refreshed from VB code, it takes up to 20 seconds. There are a lot of subforms, some of which don't need to be refreshed every time the main form is refreshed... I am wondering is there a way to prevent a subform from being refreshed when the parent form's refresh method is called?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you for the information, this is part of our solution ... we unset the recordsource of some of the subforms that took the longest, when a user clicks on that subform's tab it then sets the recordsource. When switching records on the parent form, it will then unset the subform recordsource since it doesn't need to refresh again until it is clicked on. This made a big difference.
We also did the following:
1. Found some queries that were not optimized well, some of them recent. They did all reference the one "slow" table. Rewrote them to be better optimized and this helped.
2. Moved the "slow" table into its own separate MySQL database on the same server, with a separate DSN file. No idea why this helped but it did improve speed slightly and removed the white screen / running query message.
With these changes our users have noticed a significant improvement in the DB performance. Thanks for your help.
We also did the following:
1. Found some queries that were not optimized well, some of them recent. They did all reference the one "slow" table. Rewrote them to be better optimized and this helped.
2. Moved the "slow" table into its own separate MySQL database on the same server, with a separate DSN file. No idea why this helped but it did improve speed slightly and removed the white screen / running query message.
With these changes our users have noticed a significant improvement in the DB performance. Thanks for your help.
Also be sure that your indexes are "transferring" to Access. You can open the linked table in Access in Design view (Access will protest, but open it anyway), and then review the Indexes which are being used by Access. To do that, open the table in Design view and click the Indexes ribbon item (on the Design ribbon group).
Also, be sure that you've performed maintenance on the FE: That is, BEFORE converting to ACCDE:
1) Compact the ACCDB
2) Compile it - from the VBA Editor, click Debug - Compile. Fix any errors, continue doing this until the menuitem is disabled
3) Compact again.
You might also try a Decompile. To do that, build a shortcut with a Target like this:
"full path to msaccess.exe" "full path to your database" /decompile
Run this, then run through the 3 steps above again.
Might also be a good idea to build a new, blank database and import everything into it.
Note too that complexity can certainly have an impact on Access performance. Forms with lots of subforms (or reports with lots of subreports) can get very slow as the database gets larger.