Quote
Unfortunately, we have to be able to access the MariaDB databases from 32 bit Access 2010 as well as C#,
Access is an archaic, slow, single-user system, and you're using old slow 32-bit software to interact with it. Performance will be dismal.
Quote
I was able to solve the problem, mostly. In C# I used a transaction before calling the update method on the MySQLDataadapter and then did a tran.Commit(); The insert of 12,500 or so records now takes around 10 seconds.
What's amazing is that single-user old 32-bit Access can work at all with InnoDB, a mostly ACID-compliant transactional system. Somehow (perhaps a Connector/ODBC forum contributor can explain), Connector/ODBC figures out when it has to avoid Access's row-at-a-time to-and-fro with MySQL. It seems the transaction did the trick.
Quote
Here are the methods and stats used to insert the records:
1. mysqlimport -h85.85.18.99 -P3333 -L -v -udba -p ged c:\users\lab\documents\residents.txt
Time: 00:02.25 (4862 inserts/sec) Network usage: ?? Kbps
2. mysql –h85.85.18.99 –P3333 –udba –Dged –p < c:\users\lab\documents\residents.sql
Time: 04:46 (38 inserts/sec) Network usage: 65Kbps
3. C# 2012 with MySql .Net adapter 6.9.12 using dataadapter.update(datatable)
Time: 04:56 (36 inserts/sec) Network usage: 75Kbps
4. MS Access 2012 (32 bit) VBA DAO recordset (ODBC; MariaDB 3.0 ODBC Driver)
Time: 05:01 (35 inserts/sec) Network usage: 328Kbps
5. MS Access 2012 (32 bit) VBA ADODB recordset (ODBC; MariaDB 3.0 ODBC Driver)
Time 07:57 (23 inserts/sec) Network usage: 215Kbps
mysqlimport uses Load Data Infile, which is much faster than sets of Insert statements.