Re: Extremely slow C# dataset update preformance
Posted by: Peter Brawley
Date: December 06, 2018 01:35PM


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.


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.


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.

Options: ReplyQuote

Written By
Re: Extremely slow C# dataset update preformance
December 06, 2018 01:35PM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.