-----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 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 --------------------------------------------------------------------- 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
