[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: ODBC to MySQL
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
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
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 firstname.lastname@example.org with
"unsubscribe silug-discuss" in the body.