Re: ODBC to MySQL

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

L. V. Lammert wrote:

> Trying to guage performance of MySQL from an ODBC connection - using 
> the following schema:
>
> CREATE TABLE PartProduction (
>   Part char(8) NOT NULL default '',
>   Serial char(6) NOT NULL default '',
>   ModelYear char(4) NOT NULL default '',
>   OperatorID int(11) default NULL,
>   Timestamp timestamp(14) NOT NULL,
>   Container char(9) default NULL,
>   ShipmentID int(11) default NULL,
>   QCOperatorID int(11) default NULL,
>   QCTimestamp timestamp(14) default 0,
>   PRIMARY KEY  (Part,Serial,ModelYear)
> ) TYPE=MyISAM;
>
> Produced some HORRIBLE performance figures with 1,000,000 rows:
>
> Open table via ODBC (querying entire table):            29.5 s
> Open form using table (I assume also querying entire table):    3:03 M!!
>
> I'm in process of testing with InnoDB (and perhaps others), but can 
> anyone confirm *good* performance via ODBC? If so, perhaps, under what 
> configuration? Is there something obviously performance-sucking about 
> this layout?
>
>    

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.

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.

MySQL is great for doing simple queries against indexed data.  It's 
great for light-medium applications involving a few joins and having 
clustered servers or regular backups to add some sort of data integrity 
protection.

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

Not having triggers, stored procedures, transactions, or real subqueries 
can suck but you have to design around that.


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