I'm not an expert on MySQL or can address any of the tuning issues you bring
up.  I will say this, you are not totally correct in how ACCESS is
retrieving records.  VB and Microsoft Jet retrieve dynasets which is
basically the primary key in its entirety.  When you move to the next screen
ACCESS retrieves the attribute values related to the primary key.  The
dynaset is stored in RAM and if there is none available it will go to
virtual memory.  Thus, if you have millions of records ACCESS is going to
retrive millions of KEY_ID and try and store them within the local machine's
Volitile memory space.  I'm sure you can see the problem here because you
are also trying to run an operating system and at least one application at
the same time.

The trick is to only bring the dynaset accross the network you need to
retrieve and use MySQL's indexing processing power to get the records.  I
have had success with tables with millions of records in ACCESS on a PC.  Of
course, if I tried to open and browse through the table in datasheet view it
would drag down the system and take 20 mins just to open the table with the
first set of records.  However, if I sent a record limiting query to the
backend the only records sent over the network would be the ones requested.
I don't think I ever ran into a situation where an end user needed to browse
through a table with a million records.

Another word to the wise about ACCESS.  Make sure you split your database
into a back-end and front end so the user is actually working off the front
end located within their local drivespace.  You would put linked and local
tables in the back-end and forms and reports in the front.  This way if
there is a local system lock it will only trash the local application and
not the network application.  You can see the issue here as well.  The
simple act of someone killing the cpu power during a write operation and the
phone will be ringing because no one can access the database
application...if you don't have a back-up you might just be writing the
thing all over again.  I know you probably are aware of this issue but it
didn't hurt to say it (*_*).

I hope this helped at least a little.

-----Original Message-----
From: Bill Adams [mailto:[EMAIL PROTECTED]]
Sent: Thursday, February 14, 2002 6:05 PM
To: MySQL List; MyODBC Mailing List
Subject: MySQL + Access + MyODBC + LARGE Tables


Monty, Venu, I hope you read this... :)


I really, really want to use MySQL as the database backend for my
datawarehouse.  Mind you I have played around with merge tables quite a
bit and know that MySQL is more than up to the task.  There are numerous
(not necessarily cost related) reasons as to why MySQL is better for my
application. If it were just me, it would be a slam-dunk as I only use
perl, etc. to extract data from the database.  However most of my users
use MS Access as a front end and extraction tool.

When pulling datasets from a database, Access tries to be smart and if
there is what it thinks is a primary key on a table, it will extract the
values of the primary key for the matching records and then re-query the
table with a parameterized query to get the rest of the values.  This is
true in both the case where a user tries to view a table or runs a
simple query.

Taking a simple case of the user opening the table in data sheet view
(if this is solved, the other cases will be solved too), the following
happens -- okay, this is a bit simplified, see my message "Large
Datasets w/Access" for better background:
http://lists.mysql.com/cgi-ez/ezmlm-cgi?5:mss:4918:200202:bjcebaokcknfmaldpo
kp

-- Access opens a statement handle (#1) and queries the table for the
primary key values.  E.g. It would pass "SELECT idx FROM TABLE".  Note
that it only cares about getting a partial list here.  I.e. if the
screen only shows 10 records, Access only cares about 10 primary key
values.

-- Access opens a second statement handle (#2) without closing the first
handle and then gets the values in a parameterized query. E.g.: "SELECT
a, b, idx FROM table WHERE idx=? OR idx=?...".  It then pulls the
records it cares about with this statement and closes the statement.

-- If, say, the user presses "page down", [I think] access then gets the
next set of primary key values from statement handle #1, sets up another
prepared query and gets the values as above.


MyODBC, as compiled today, uses mysql_store_result to get records.  This
is fine for reasonably sized tables.  However, if the table has millions
of records, writing the results to a temporary table has many
detrimental effects, e.g.: Access seems to hang from the user's
perspectiv, Access crashes because there are too many records for it to
handle at once (data requirements to great); MySQL creates HUGE
temporary tables or bombs if SQL_BIG_RESULT was not set.

So in the case of a very long table, it is important to use
mysql_use_result instead.  This makes it so that results are returned
right away and eases the load on all programs involved.  The astute
reader will realize that if one uses mysql_use_result and does not fetch
all of the records, the next query will return the remaining records
from the previous query first.  It follows that Access bombs because in
statement #2 it is getting results from statement #1. (This is seen from
the myodbc.log line: " | error: message: Commands out of sync;  You
can't run this command now" in the myodbc3.dll changed to use the said
function.)

The bottom line is that in order for MySQL + Access + MyODBC to be
usable as a datawarehouse MySQL/MyODBC (a) must be able to return
uncached results; and (b) be able to have multiple statements open,
active, and with pending data to be fetched at the same time.

SO....

Does anyone have any suggestions on how to accomplish this?

How difficult would it be (for a relatively good C/C++ programmer) to
alter mysqld so that mysql_use_result could handle multiple statements
open at the same time?

Other suggestions...?


Thanks for reading this and your time.


--Bill
(all opinions are mine, bla bla bla)
(I am on the MyODB list but not the MySQL list at the moment)




---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail
<[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php


---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to