Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3711
  • Last Modified:

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.
0
wxitguy
Asked:
wxitguy
  • 3
  • 3
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
we have narrowed the cause of the slowness down to one table that is specific to the FE1 database
Have you tried reviewing the indexes on that table? It's not uncommon to change indexes during the lifetime of a database - what worked last year (or last month) may not work too well now, as the table gains data.

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.
0
 
wxitguyAuthor Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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?
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
wxitguyAuthor Commented:
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?
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
I am wondering is there a way to prevent a subform from being refreshed when the parent form's refresh method is called?
Not really - the subform must refresh when the parent refreshes.

You can "unset" the SourceObject of your Subform Control before refreshing:

Me.YourSubformControl.SourceObject = ""

And then reset it after your refresh:

Me.YourSubformControl.SourceObject = "Name of your Subform"

Depending on how your form is setup, you may be able to use a single Subform control, and change the SourceObject as needed. For example, if you have a Customer form with a Subform for Invoices and another for Payments, you could instead use a single Subform control, and change the SourceObject when the user clicks on a button - so if I have a button labeled "Invoices", I'd have code like this:

Me.MySubformControl.SourceObject = "sfmInvoices"

When the user clicks the "Payments" button:

Me.MySubformControl.SourceObject = "sfmPayments"

To remove your subforms when the main form closes:

Me.MySubformControl.SourceObject = ""
0
 
wxitguyAuthor Commented:
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.
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now