Access 2000/2002 to MySQL link v_e_r_y s_l_o_w

[gllincoln]

Hi,

Apologies if this is off topic but I would like to use the client's
installed Access (2002/XP) to link to his MySQL 5.0.45 database running on
WS 2003 Std. I used myODBC current version driver (installed smoothly,
recognizes, I can connect to the database I need to use and see the tables.
It seems slower than I would expect but I can select the tables I want (in
the link table manager wizard) and successfully link to them.

FWIW, the current database tables are fairly small - nothing over a couple
thousand records in a given table.

The problem - pulling up a table or query result dataset with 1 record takes
maybe 30 seconds. A table with 100 or so records takes upwards of 15 minutes
(yes minutes). It's not crashing, it will eventually populate the table -
but it's completely unusable this way.

I can append one record at a time takes 20-30 seconds. Or, I can append 10
and go get a cup of coffee, it will be done in about 4 minutes.

I could probably type some of the records in as fast as Access is fetching
or updating or appending to a table.

It seems to be v e r y s l o w l y grabbing one row at a time - going out
for coffee, coming back and requesting another row. The CPU utilization
doesn't go past 50% and other applications are able to run fairly normally
while we are waiting.

The client has an intranet application, rather complex one - written in
PHP/mySQL by someone else. Dumping mySQL is not an option. I'm hoping to be
able to do the on location management with Access because, frankly, I'm more
competent using Access/VBA than I am with writing PHP/mySQL/HTML to
accomplish the reporting and editing tasks.

I've tried stepping back to a slightly older ODBC version - no help. The
client has the most recent available Jet 4.0 drivers installed. I even tried
uninstalling Office XP and installed Access 2000 from his older copy of
Office 2000 Pro - made no difference.

I'm kind of stumped but pretty sure the problem is somehow related to either
the ODBC drivers (but don't think so because they seem to work for other
folks) or Access is by default looking elsewhere, timing out, then finally
taking a row at a time from mySQL, rinse and repeat.

It should be noted that the machine has more than adequate resources,
phpmyadmin runs nicely - command line mysql snaps to attention, I wrote a
simple, crude PHP connect to pull down the same table data - and it
populates a lengthy scrolled page of a table viewed by a browser in a very
reasonably amount of time. And, I'm not a PHP guru - it's not a slick or
efficient data routine I am performing - "select * from table;" then while I
can get a row, for each populate a table cell.

If Access was pulling the data up as fast as that PHP page - I could live
with it. <sigh>

Any help or ideas even, greatly appreciated! If I can't use Access, then I
guess it's time to start cramming on using PHP interactively with MySQL.

Gordon

 

[Albert D. Kallal]

you don't mention what kind network access you using here, but for the most
part you should be able to successfully retrieve records, if the table has a
small number of records say five or 10 million records, and you retrieve one
record out of that table,it should be near instant in terms of the delay
time when you request that one record.

it's been a few years since I've played with my SQL and MS access, to the
time when I did I stump reforms to be extremely good, and there was no
perceptual delays in most retrieval actions. (I was a course using a local
office network with a 100 base T rating). it's possible that your network
here slower then a fast local lan.

for the most part the trick in keeping performance high is to limit the
number of records you retrieve into a report or form.
A few other tips and always help is make sure that any table you link two
who has a primary key (actually I think you're forced to do that), in
addition to any of the forms that you bind directly to a table who you one
of insured that a time stamp field be is exposed to that particular form. in
addition to having a primary key and time stamp field exposed, you of course
always want to restrict the number of records transferred into what form,
especially by using the where clause.

what this means is that you can get very decent and very high performance
event when using bound forms directly downed to a table on my SQL, but you
must use the where clause to restrict the form load to the one record that
you need. a good example of a search screen in MS access that asks the user
for what records display, and then launches to the one record is outlined
here

http://www.members.shaw.ca/AlbertKallal/Search/index.html


We can also read my following article against some hints and tips on how to
get performs to work over what is called all when, or a wide area network
which might apply to your case:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html


If you are fact running in a limited bandwidth environments such as over the
Internet, then you'll have to pay extra caution in restricting the number of
records transferred for report or form.

 

[gllincoln]

Thanks for the input Albert -

It's on an intranet - fast ethernet, the lan throughput is around 700kbps,
can copy a large file or two, or a large number of small files across the
lan a whole lot faster than Access is getting a few rows of data. I repeat -
these are NOT large records, no binary or blob type objects in the table.
Just normal stuff, demographic info, order histories. Largest table only
contains a couple thousand rows. Can't pull up a query like "SELECT TOP 10 *
FROM table;" in under 3 to 4 minutes.

I've had Access & mySQL hooked up before, in other places, times and I know
it can pull data across a lan better than this. This is horrible - I could
do better with a 9600 comport connection if things were working right.
..
In another forum a guy made a suggestion that I might have some old
connection stuff stuck in the registry of the system that is somehow
grabbing precedence and trying to refresh itself. This is a possibility -
the systems at the client's location were moved from another office that had
an entirely different configuration throughout. There are some legacy issues
with ActiveDirectory and DNS that could stand a clean up.

Problem with this is, where does a person start to look? AD & the registry
are extremely large haystacks.

Gordon

 

[David W. Fenton]

for the most part the trick in keeping performance high is to
limit the number of records you retrieve into a report or form.

You have to have your MySQL tables properly indexed or your
performance is going to suck.

 

[David W. Fenton]

It's on an intranet - fast ethernet, the lan throughput is around
700kbps, can copy a large file or two, or a large number of small
files across the lan a whole lot faster than Access is getting a
few rows of data. I repeat - these are NOT large records, no
binary or blob type objects in the table. Just normal stuff,
demographic info, order histories. Largest table only contains a
couple thousand rows. Can't pull up a query like "SELECT TOP 10 *
FROM table;" in under 3 to 4 minutes.

But TOP 10 is not a MySQL SQL command, so you're going to need to
pull the whole table across the wire for Access to process it.

Are your MySQL tables properly indexed?

In another forum a guy made a suggestion that I might have some
old connection stuff stuck in the registry of the system that is
somehow grabbing precedence and trying to refresh itself. This is
a possibility - the systems at the client's location were moved
from another office that had an entirely different configuration
throughout. There are some legacy issues with ActiveDirectory and
DNS that could stand a clean up.

With Jet data sources, there was a problem in early releases of A2K
that would cause metadata that was cached in linked tables to slow
down a lot when you altered the connect string (we're talking forms
that loaded instantaneously before now taking minutes to load, just
because you've moved the front end to a new network and reconnected
to the new back end).

But with ODBC links, I don't think this is the case. You don't
update them, but completely recreate them from scratch (at least,
that's what I've always had to do with MySQL). Thus, I think it's
unlikely this would be a cause of the problem.

But deleting the links and recreating them would be worth a try,
anyway.

And then you might search for Doug Steele's DSN-Less connection
code. I don't think that would speed things up, but it would make
for an app that's easier to distribute.