Solved

Access 2010 with MySQL ODBC linked tables slow

Posted on 2013-05-15
6
3,304 Views
Last Modified: 2013-05-29
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
Comment
Question by:wxitguy
  • 3
  • 3
6 Comments
 
LVL 84
ID: 39170813
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
 

Author Comment

by:wxitguy
ID: 39171685
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
 
LVL 84
ID: 39181837
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:wxitguy
ID: 39194700
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
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 39200816
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
 

Author Comment

by:wxitguy
ID: 39205089
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

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Creating and Managing Databases with phpMyAdmin in cPanel.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now