MySQL is very slow compared to MS Access when inserting hundreds of thousands of rows
I am currently adding hundreds of thousands of rows of data to a table, first in an MS Access table and then in a MySQL table.
I tried MS Access first, took less than 40 seconds. Then I tried with exactly the same source and with the same table structure as MySQL and it took 6 minutes and 40 seconds. It's 1000% slower !!!
So, is it a myth that the database server has the best performance?
a source to share
Thousands of independent INSERTs will be very slow. Because MySQL is a multi-user transactional database, there is a lot more going on during each query than Access. Each INSERT operation on a SQL server goes through the following steps:
- Decode and parse the request.
- Open the table for writing, setting locks if necessary.
- Insert a new line.
- Update the indexes as needed.
- Save the table to disk.
Ideally, you want to perform steps 1, 2, 4, and 5 as many times as possible. MySQL has some features to help you.
PREPARATION OF INQUIRIES
When preparing a query that you intend to reuse, you only complete step 1 once. Here's how:
PREPARE myinsert FROM 'INSERT INTO mytable VALUES (?, ?, ?)';
SET @id = 100;
SET @name = 'Joe';
SET @age = 34;
EXECUTE myinsert USING @id, @name, @age;
SET @id = 101;
SET @name = 'Fran';
SET @age = 23;
EXECUTE myinsert USING @id, @name, @age;
# Repeat until done
DEALLOCATE PREPARE myinsert;
Learn more about PREPARE at mysql.com.
Use transactions
Combine several (or several hundred) INSERTs into a transaction. The server only needs to execute steps 2, 4 and 5 times for each transaction.
PREPARE myinsert FROM 'INSERT INTO mytable VALUES (?, ?, ?)';
START TRANSACTION;
SET @id = 100;
SET @name = 'Joe';
SET @age = 34;
EXECUTE myinsert USING @id, @name, @age;
SET @id = 101;
SET @name = 'Fran';
SET @age = 23;
EXECUTE myinsert USING @id, @name, @age;
# Repeat a hundred times
COMMIT;
START TRANSACTION;
SET ...
SET ...
EXECUTE ...;
# Repeat a hundred times
COMMIT;
# Repeat transactions until done
DEALLOCATE PREPARE myinsert;
Learn more about the transaction .
Load table from file
Instead of doing thousands of INSERTS, do one batch download of your data. If your data is in a delimited file like CSV, use the LOAD DATA statement.
LOAD DATA LOCAL INFILE '/full/path/to/file/mydata.csv' INTO TABLE `mytable` FIELDS TERMINATED BY ',' LINES TERMINATED BY '\r\n';
Here's a link to the MySQL page on LOAD DATA .
a source to share
Usually the most important aspect of performance with databases is not how fast you can insert data, but how fast you can query it. MySQL is, I believe, a more powerful optimizer than MS Access and can make better use of indexes. An example of this is a lost index scan , which can give a factor of 10 or more for some types of queries.
Also, the method you use to insert data can affect the time it takes to insert. For example, it is generally faster to use bulk insert compared to many separate insert statements. In addition, disabling indexes when adding and enabling them again can improve performance.
a source to share
Does MySQL provide any SQL trace facility so you can see what is sending it? From my experience of using Access with SQL Server via ODBC, I can tell you that Jet is doing some seemingly strange solutions with bulk inserts. What it does is send an insert for every record, not a batch insert for all records. This makes it significantly slower, but it does mean it cannot bind SQL Server to long update (and corresponding table locks, etc.).
It's dumb in terms of your insert, but smart in terms of being a good client / server citizen - it allows SQL Server to decide how to serialize the commands requested and interleave them with other users. This means that the locks are shorter than on the bulk insert.
With SQL Server, you can use ADO to do the trick and force it to handle the insert as a batch. I don't know if there is a way to do this with MySQL.
You might think:
If the source and destination tables are in MySQL, the pass-through request should make it fully handled by MySQL.
a source to share