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", 20000); /* 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
