Actually there is no reason to expect MySQL to perform better than
Access for a nominally sized database and everything running on one PC.
Access was designed and optimized for JET whereas ODBC is a
general-purpose API.
Also, the connection method impacts this significantly, as do the
complexity of the query. For a simple test I did comparing connection
methods from Access check http://www.trewtech.com/sqltest.html
However, to see Access flounder horribly, simply put your MDB backend
at the other end of an ethernet connection from your frontend.
Now, for even more pain, add a few more users connecting to it at the
same time. Access is _not_ a server and the kiss of death for an
access database is to try to use it as a backend over a network. It's
amazing how inefficient a query is when the "server" is actually the
network filesystem.
Add in absurdly long-running bugs that Microsoft denies exist - well,
rather, since it's extremely difficult to actually report a bug to
Microsoft without paying them for the privilege, perhaps they just don't know - such as
randomly corrupting memo fields - and it's essentially useless for anything
but a very small database with a very small number of users, or only
running on one PC with no network.
Jamie
Regards,
James mailto:[EMAIL PROTECTED]
Thursday, February 08, 2001, 5:02:59 PM, you wrote:
Q> We've got a server app that does a lot of 'small' database reads and
Q> writes. We were originally using MS Access via DAO (Jet Engine) and we
Q> wanted to tighten up DB performance, so we've written a general ODBC
Q> database wrapper object, but mainly just to connect to MySQL. I figured
Q> there'd be ODBC overhead, but its a lot worse than I imagined.
Q> I want to know: does it make sense that our original system, connecting via
Q> 'Jet-engine' to Access, is actually much faster than connecting to MySQL
Q> via ODBC? This seems to be what's happened.
Q> I'm wondering if it has to do with the overhead of connecting to a
Q> server-based database via a tcp socket (even on localhost) rather than the
Q> direct-to-disk Jet engine; maybe because we do so many small reads/updates
Q> it's actually faster with Access? Any thoughts? Is it worth my time to
Q> look into using MySQL directly instead of thru ODBC?
Q> I'm obviously working on Windows (NT), connecting at ODBC version 2.0 to
Q> MySQL server 3.23, using a database converted directly from Access to MySQL
Q> using the cool (but unstable) DBTools GUI, which kindly retained all keys
Q> and indexes (which have been reviewed for speed).
---------------------------------------------------------------------
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
