Re: ODBC to MySQL

[Ken Hagan <ken@alacrity-it.com>]

L. V. Lammert wrote:

> At 03:52 PM 1/20/2005 -0600, you wrote:
>
>> I don't know that dumping an entire table out is any way of measuring 
>> other than your network and hard drive I/O performance.  Now matter 
>> what schema you're using, how it's indexed, and how much data are in 
>> each row, just dumping a table is a factor of how quickly the server 
>> can fetch and transmit the data and how quickly the client can 
>> receive and process it.
>
>
> Quite right, .. The test is to *open* the table (via ODBC - including 
> loading the first screenof data), .. and *open* a form linked to the 
> table (again via ODBC). Neither includes any attempt to manipulate data.
>
> I would have hoped that the ODBC client was a little smarter than this!
>
It sounds like you're using Access as your ODBC client.  Access does all 
kinds of things in addition to what a barebones ODBC application might.  
I wouldn't even venture to guess all the stuff it's doing when opening 
forms linked to tables and opening dataviews.  For starters, though, if 
the form contains any fields that are populated with data from the table 
(such as a select DISTINCT(somethingOrOther)) then indexing the database 
will greatly improve the display time of the form.

>> MySQL is going to suck at this.  Then again, if you're designing an 
>> application for dumping out an entire database to a client, a file 
>> would work just fine.
>
>
> You're saying, then, that ODBC cannot be used to access tables with a 
> large number of rows?
>
It will suck for returning all of the rows cause that's just shovelling 
tons of data.  It can be used for it but won't be blazing fast.  Parsing 
the data on the server and using ODBC to transport the information you 
need is much better.

>> I've seen great performance from MySQL using indexed tables with 
>> hundreds of thousands of rows using ODBC from Crystal Reports as well 
>> as ASP.  The performance was not niceably slower or faster than a 
>> similar application running on SQL server (we were migrating).
>
>
> That would seem to indicate some sort of problem with this table 
> structure? Does MySQL choke on multi-column keys, for example?
>
MySQL is great on multi-column keys.  No problem there.  No problem 
doing joins with tons of tables and indices.  Use the EXPLAIN command 
from the MySQL CLI to see that it's doing the right thing and it will 
show you the database overhead for any given query and the time the 
server takes to parse and execute.

If you want to find the bottleneck, I would use the MySQL CLI to explain 
queries you are running to the server and use a couple of client tools 
to try to determine if it's ODBC, MySQL, the schema and queries, or the 
client that are giving you less than satisfactory results.  

Your current problem sounds like the client is trying to just read in 
the whole table and then work with it or doing a whole bunch of sorts 
and sequential scans on a lot of data.  If you cannot change client 
software, you might want to try investigate what's being queries and see 
if slapping some indexes or rearranging some tables might make a drastic 
improvement.


-
To unsubscribe, send email to majordomo@silug.org with
"unsubscribe silug-discuss" in the body.