Re: MySQL -- very LOUSY PERFORMANCE compared to MS SQL Server???

Ignoramus14359 <ignoramus14359@NOSPAM.14359.invalid> wrote:

> My test was to run execute statement for 60,000 statements that are
> DROP TABLE, CREATE TABLE and INSERT statements. One important thing to
> know about this file is that the tables are truly huge (some tables
> have hundreds of columns). So each INSERT is a enormous string, as you
> can imagine.

So this is a bulk import.
There are *many* possibilities to speedup things.

Row length does not matter so much. In fact it is recommended to use
extra long rows via the multiple-value-insert syntax. But you may
try prepared statements as well.

More important are indexes (created before or after load).
Make sure you have the MySQL buffers configured huge. Indexing
performance will suffer from insufficient memory.

> The client used to load these databases was a C++ app using either
> ODBC (for MS Sql Server) or the native mysql.lib/dll for MySQL.
>
> Anyway, the results are as follows:
>
> MySQL 5.0: 41 minutes
> SQL Server: 1 minute 50 seconds.
>
> I am not less than SHOCKED by this difference and would like to know
> if there are any onbious tweaks that I could do.

The first place to look would be the fine manual, of course:

<http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html>


XL
--
Axel Schwenke, Senior Software Developer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/