RE: MySQL + Access + MyODBC + LARGE Tables
Hi,
> -Original Message-
> From: Bill Adams [mailto:[EMAIL PROTECTED]]
> Sent: Friday, February 22, 2002 2:04 PM
> To: MyODBC Mailing List; MySQL List
> Subject: RE: MySQL + Access + MyODBC + LARGE Tables
>
>
> All, there were many emails posted about this on the MyODBC list which,
> of course, can be viewed via the archive on the mysql.com site. For the
> most part I will neither quote nor repeat the information from those
> emails here.
>
>
> The conclusion is that MySQL + Merge Tables is perfectly capable of
> being a data warehouse and is in fact better in most regards when
> compared to other RDMBS. One example: For similar record counts and
> identical index definitions, speed wise MySQL and the "other" rdbms are
> about the same when the query is disk bound (e.g. the index is not
> cached). MySQL is 5-10x faster than the other rdbms in the cached index
> case. There are many other benefits as well.
>
> (I will not name the other commercial RDBMS out of fear of lawyers, the
> DCMA, and posting unauthorized benchmarks. You will have to trust me
> that it is a major RDBMS, MySQL is /fast/ comparatively, and that I am
> not an idiot at setting up and optimizing databases.)
>
> Using MyODBC-3.51.01.01 works fine to access the MySQL database via MS
> Access. Venu (bless him for all of his help) is going to add
> information to the FAQ as such: In the large table case one needs to
> check off three options "Return Matching Rows", "Allow BIG Results", and
> "Enable Dynamic Cursor". I needed to do one last truly terrible hack to
> MyODBC (patch below) so that if someone tries to open a very long table
> (>43M rows in my test case) bad things don't happen as MySQL tries to
> copy the results to a temporary table/file. Perhaps there could be a
> config for "Max Rows When There Is No Criteria" in MyODBC?
Yes. I had this option in mind before the release of the 3.51
driver, but certainly lost the control on that.
As you know, the current MyODBC drivers lacks performance if
the table size is too big as it tries to cache everything
internally, and that causes the issue.
We can introduce the following options:
- Use cache results, set the max limit size ---
- Don't use cache results, get the row based on the request.
Will discuss this with 'monty' when he is back and lets see how
it goes. Even this logic could be used for the new interfaces
for MySQL that are under discussions.
>
> In the next month or two I will try to write an article describing what
> I did in more detail so that everyone may benefit.
Really a good idea, and most of the Access users who are suffering
from performance issues should be able to benefit out of this.
>
> b.
>
> [bill@badams bill]$ cat myodbchack.patch
> --- ../myodbc-3.51.orig/execute.c Fri Feb 22 10:55:35 2002
> +++ execute.c Fri Feb 22 10:53:48 2002
> @@ -72,7 +72,26 @@
>query=tmp_buffer;
>}
> }
> - }
> + }
> + /* Terrible hack by Bill Adams */
> + else if(
> + !my_casecmp(query, "select", 6) &&
> + my_casecmp(query, "where", 5) &&
> + my_casecmp(query, " limit ", 7)
> + ){
> +/* Limit the number of rows when someone does a query without
> + any criteria */
> +char *tmp_buffer;
> +uint length=strlen(query);
> +if ((tmp_buffer=my_malloc(length+30,MYF(0
> + {
> + memcpy(tmp_buffer,query,length);
> + sprintf(tmp_buffer+length," limit %lu", 2); /* Arbitrary */
> + if (query != stmt->query)
> + my_free((gptr) query,MYF(0));
> + query=tmp_buffer;
> + }
> + }/* End Terrible Hack */
>pthread_mutex_lock(&stmt->dbc->lock);
>if (check_if_server_is_alive(stmt->dbc) ||
>mysql_query(&stmt->dbc->mysql,query))
Thanks for the patch. The above should do the work
on this.
Regards, Venu
--
For technical support contracts, go to https://order.mysql.com
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Mr. Venu <[EMAIL PROTECTED]>
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer
/_/ /_/\_, /___/\___\_\___/ California, USA
<___/ www.mysql.com
-
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
RE: MySQL + Access + MyODBC + LARGE Tables
All, there were many emails posted about this on the MyODBC list which,
of course, can be viewed via the archive on the mysql.com site. For the
most part I will neither quote nor repeat the information from those
emails here.
The conclusion is that MySQL + Merge Tables is perfectly capable of
being a data warehouse and is in fact better in most regards when
compared to other RDMBS. One example: For similar record counts and
identical index definitions, speed wise MySQL and the "other" rdbms are
about the same when the query is disk bound (e.g. the index is not
cached). MySQL is 5-10x faster than the other rdbms in the cached index
case. There are many other benefits as well.
(I will not name the other commercial RDBMS out of fear of lawyers, the
DCMA, and posting unauthorized benchmarks. You will have to trust me
that it is a major RDBMS, MySQL is /fast/ comparatively, and that I am
not an idiot at setting up and optimizing databases.)
Using MyODBC-3.51.01.01 works fine to access the MySQL database via MS
Access. Venu (bless him for all of his help) is going to add
information to the FAQ as such: In the large table case one needs to
check off three options "Return Matching Rows", "Allow BIG Results", and
"Enable Dynamic Cursor". I needed to do one last truly terrible hack to
MyODBC (patch below) so that if someone tries to open a very long table
(>43M rows in my test case) bad things don't happen as MySQL tries to
copy the results to a temporary table/file. Perhaps there could be a
config for "Max Rows When There Is No Criteria" in MyODBC?
In the next month or two I will try to write an article describing what
I did in more detail so that everyone may benefit.
b.
[bill@badams bill]$ cat myodbchack.patch
--- ../myodbc-3.51.orig/execute.c Fri Feb 22 10:55:35 2002
+++ execute.c Fri Feb 22 10:53:48 2002
@@ -72,7 +72,26 @@
query=tmp_buffer;
}
}
- }
+ }
+ /* Terrible hack by Bill Adams */
+ else if(
+ !my_casecmp(query, "select", 6) &&
+ my_casecmp(query, "where", 5) &&
+ my_casecmp(query, " limit ", 7)
+ ){
+/* Limit the number of rows when someone does a query without
+ any criteria */
+char *tmp_buffer;
+uint length=strlen(query);
+if ((tmp_buffer=my_malloc(length+30,MYF(0
+ {
+ memcpy(tmp_buffer,query,length);
+ sprintf(tmp_buffer+length," limit %lu", 2); /* Arbitrary */
+ if (query != stmt->query)
+ my_free((gptr) query,MYF(0));
+ query=tmp_buffer;
+ }
+ }/* End Terrible Hack */
pthread_mutex_lock(&stmt->dbc->lock);
if (check_if_server_is_alive(stmt->dbc) ||
mysql_query(&stmt->dbc->mysql,query))
-
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
RV: MySQL + Access + MyODBC + LARGE Tables
-Mensaje original- De: Eugenio Ricart [mailto:[EMAIL PROTECTED]] Enviado el: viernes, 15 de febrero de 2002 7:00 Para: MyODBC Mailing List Asunto: RE: MySQL + Access + MyODBC + LARGE Tables Hello, I work with VB 6.0 ADO 2.5 Access , I am trying work with MySql & and the Last MyODBC . I have problems with Dynamic Cursos and Speed. When I make a query that results 200 records in Dinamic Cursor this take one minute or minute and half to get the records .And when Move throught records (Forward and backward) about one second to go to the next record :( . I need that cache be 1 record.Because I need see if another user changed the information when I move among records. Really the speed is very bad. When I use Static cursor is very fast , but as you know I not see if another user change the value , I have a program of booking and I must control this. With Access 2000 all of this works fine , but really access have another big inconveniences that all know. Please someone of you know How can i do the speed be better with Dynamic Record Type. Thank you Eugenio. -Mensaje original- De: Venu [mailto:[EMAIL PROTECTED]] Enviado el: viernes, 15 de febrero de 2002 5:34 Para: Bill Adams; MySQL List; MyODBC Mailing List Asunto: RE: MySQL + Access + MyODBC + LARGE Tables Hi, > > > 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:bjcebaok > cknfmaldpokp > > -- 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. Probably we can add extra DSN options, to make use of either mysql_store_result() or mysql_use_result(). In the second case, lot of code change is needed in all result set dependency APIs too. > > 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.) Can you be more specific on this ? And a MS ODBC DM trac
RE: MySQL + Access + MyODBC + LARGE Tables
Spoiler: Venu's Suggestion about "Dynamic Cursor" is the answer On Thu, 2002-02-14 at 20:34, Venu wrote: > > 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. > > Probably we can add extra DSN options, to make use of either > mysql_store_result() or mysql_use_result(). In the second > case, lot of code change is needed in all result set dependency > APIs too. That would be nice but perhaps unneeded (see below about your suggestion). > > 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.) > > Can you be more specific on this ? And a MS ODBC DM trace will be better > to analyze. Sorry, I should have been clearer about this. Yesterday (Thursday) I downloaded the bk source. Aside from many other hacks, I changed execute.c:do_query to use mysql_use_result() instead of mysql_store_result(). In THIS version, I got the "Commands out of sync" error. To better show what is happening, I just got the souce again, made the said modification and a couple of more verbose debugging output modifications. In the setup, I had checked off "Return Matching Records" and "Trace...". Here is the sequence of what is happening: [bill@badams myodbc-3.51]$ grep -E 'SQLFree|SQLPre|sync' myodbc.log >SQLFreeHandle | info: SQLFreeHandle: 157150 | >SQLFreeConnect | SQLFreeHandle | info: SQLFreeHandle: 154988 SQLPrepare | | info: SQLPrepare: 15bd68 SELECT Config, nValue FROM MSysConf | SQLFreeStmt | | enter: SQLFreeStmt: 15bd68 option: 1000 | SQLFreeHandle | info: SQLFreeHandle: 15bd68 | >SQLFreeStmt | | enter: SQLFreeStmt: 15bd68 option: 1 | SQLPrepare | | info: SQLPrepare: 15bd68 SELECT `pcm_test_header_200202`.`serial_hi`,`pcm_test_header_200202`.`ymd_ts` FROM `pcm_test_header_200202` | SQLFreeStmt | | enter: SQLFreeStmt: 15bd68 option: 1000 | SQLPrepare | info: SQLPrepare: 15c780 SELECT [column names removed --bill] FROM `pcm_test_header_200202` WHERE `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? OR `serial_hi` = ? AND `ymd_ts` = ? SQLFreeStmt | | enter: SQLFreeStmt: 15c780 option: 1000 | SQLFreeStmt | enter: SQLFreeStmt: 15c780 option: 0 SQLFreeHandle | info: SQLFreeHandle: 15c780 | >SQLFreeStmt | | enter: SQLFreeStmt: 15c780 option: 1 | SQLFreeHandle | info: SQLFreeHandle: 15bd68 | >SQLFreeStmt | | enter: SQLFreeStmt: 15bd68 option: 1 | > 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. > > Try to use Dynamic Cursor Type (OPTION=32) in MyODBC 3.51. YES! The stock 3.51.01.01 myodbc3.dll with Dynamic Cursor Type, Allow BIG Results, and Return Matching rows is the ticket. AFAIK, this satisfies my needs. I will get back later next week after I do some more testing. b. - 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
Re: MySQL + Access + MyODBC + LARGE Tables
Bill, Some databases can use a live result set when retrieving a lot of records and I really really wish MySQL could do the same. A live result set does not create a temporary table or use memory to retrieve all the records. It will grab 50 or so records at a time, and when scrolling further down through the query, it will grab another 50 and so on. And yes, these queries are bi-directional. I've successfully created queries with other databases that "retrieved" a million records in less than 0.1 seconds on a P133 machine. For browsing data in a grid, it is instantaneous even when "sorting" on an index column. Traversing the entire 1 million rows uses absolutely no additional memory. I had a memory monitor running in the background and from start to finish it used maybe 5k of ram and no additional disk space was used for temporary tables which means disk activity was extremely low. Slapping on any kind of Where clause doesn't slow it down because if you're displaying the results to a grid, it fills the grid with the first 10 rows that it finds, then when you page down it pulls in the next 10 rows. These are the benefits of using a live result set. Now the drawback of using a live result set is it doesn't create a static snapshot of the table. A static result set creates a copy of the rows at the instant the query was executed. It does this to protect the rows from being changed by another user. You'd want a static result set when printing reports that are doing subtotals because you don't want other people throwing your totals off. With a live result set, if I'm going through the query and I'm on row 100, another user can change row 150 which may now exclude row 150 from my query because it now falls outside the scope of the Where clause. For me 98% of the time, I don't really care if this happens. The additional speed, lower memory use, and very low disk activity more than makes up for it. So yes, you can access some databases extremely fast. I wish Monty would implement this for MySQL. It would have most people drooling on their keyboards. :-0... Brent At 05:04 PM 2/14/2002 , you wrote: >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:bjcebaokcknfmaldpokp > >-- 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
RE: MySQL + Access + MyODBC + LARGE Tables
Hi, > > > 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:bjcebaok > cknfmaldpokp > > -- 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. Probably we can add extra DSN options, to make use of either mysql_store_result() or mysql_use_result(). In the second case, lot of code change is needed in all result set dependency APIs too. > > 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.) Can you be more specific on this ? And a MS ODBC DM trace will be better to analyze. > > 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. Try to use Dynamic Cursor Type (OPTION=32) in MyODBC 3.51. Regards, Venu -- For technical support contracts, go to https://order.mysql.com __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Venu <[EMAIL PROTECTED]> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Developer /_/ /_/\_, /___/\___\_\___/ California, USA <___/ www.mysql.com - 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
RE: MySQL + Access + MyODBC + LARGE Tables
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.
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:bjcebaokcknfmaldpokp -- 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
