Performance

charlesinla · Post by **charlesinla** » Mon 07 Aug 2006 16:42

I saw so many people saying that the mysqldirect is the fastest, I tried to import 35000 rows data from a dataset into mysql server, it costs me 18 minutes.....what might go wrong???

This is the last issue I have to persuade my boss to purchase mysqldirect, hope someone can help me on this!!

Thanks!!

charlesinla · Post by **charlesinla** » Mon 07 Aug 2006 19:34

sorry I think I should give out more info:

I am using VB.NET to write my test program,
I used a dataset to get data from MS-SQL (35000 rows, 7 small columns only), I used sqlconnection, sqladapter to do the test.

With the same code, if I import the data into a MSSQL, it takes within one minute, but if I import the data into mySQL, it takes 18 minutes to run.

I am using mySQL 5, client version 5.0.11,
Hardware: P4 2.8, 2GB memory

Serious · Post by **Serious** » Tue 08 Aug 2006 11:35

We tested insertion of 50000 rows into the server on remote host using MySqlDataAdapter.Update() method. This took about 50 seconds.

Please check your network and MySQL Server configuration. If this doesn't help, check if MySqlConnection.Compress option improves performance of your test. Setting MySqlConnection.Unicode=false also speeds up data transfer.

If data insertion is time-critical in your application, try to use MySqlLoader component.

Here is our test table definition

Code: Select all

CREATE TABLE test.dump_test (
  id INT(11) NOT NULL AUTO_INCREMENT,
  column1 VARCHAR(50),
  column2 VARCHAR(50),
  column3 VARCHAR(50),
  `timestamp` TIMESTAMP(0) default 'CURRENT_TIMESTAMP',
  PRIMARY KEY (id)
)
ENGINE=MYISAM
ROW_FORMAT=DYNAMIC
AUTO_INCREMENT=2535925
CHARACTER SET utf8 COLLATE utf8_general_ci