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
