MySQL Forums
Forum List  »  Connector/ODBC

Write performance problems with MySql and MariaDb
Posted by: Jerome Parent
Date: October 12, 2018 03:36PM

Hello. Although the text below refers to MariaDb, this has also been tested with a recent version of MySql with identical results. Any help would be appreciated:

I am having serious insert and update problems with MariaDB version 10.0.17. Here is our environment:
“Server”:
* Dell Optiplex 3010, 4GB, 250GB, Windows 7 SP1 (64 bit), gigabit ethenet
* MariaDB 10.0.17 (32 bit)
* .Net Framework 4.5.2
Client:
* Dell Optiplex 3010, 4GB, 250GB, Windows 7 SP1 (64 bit), gigabit ethernet
* MS Access 2010 (32 bit)
* MariaDB ODBC 3.0 Driver (32 bit)
* MySql Connector Net 6.9.12
* Visual Studio 2012
* .Net Framework 4.5.2
The MariaDB installation used all defaults (InnoDB, UTF8, etc. ). There is basically no load on the server and 40% of the memory is unused. I created a couple of tables for test purposes. The main table has an integer id number as PK, last name, first name, integer status, entry date. The last name column is indexed and the status field is a foreign key for a small (20 rows) lookup table. There are just under 11,000 records in the existing text file. I tried several load methods for the data: MS Access linked table (ODBC, all defaults) , MS Access VBA DAO recordset (ODBC), MS Access ADODB recordset (ODBC), C# MySql .Net 6.9.12 adapter (all defaults), MySql.exe with input SQL file, and finally, the MariaDB MySqlImport.exe.
The server has no other applications running on it and there is plenty of free disk space. There is very little other network activity and the two machines are connected to gig-e ports in a switch.
The loads for all the MS Access methods are only inserting or updating around 33 records per second. The last C# attempt was even slower: 20 records per second. Before trying the MySqlImport, I assumed a network problem. However, I tried the following query in VBA (both ADODB and DAO) and in C#:
SELECT ResidentId, LastName, FirstName, EntryDate, ResidentStatus
FROM Residents INNER JOIN ResidentStatuses
ON Residents.ResidentStatusId=ResidentStatuses.ResidentStatusId;
This query took just over a tenth of a second in all the environments.
I then tried the following statement in VBA, C# and MySql.exe:
UPDATE residents SET lastname = concat(lastname,'_1');
This took over 500 seconds in VBA and C# but only one half second in MySql.
I then truncated the table and used MySqlImport (running on the CLIENT, with the text load file also on the client) and loaded the table. All 11,000 records took only 2 seconds to load. Therefore, I know this is not a network or disk I/O problem. Honestly, I didn’t expect good performance using ODBC but I wouldn’t have expected it to be this bad. What really surprised me was the C# insert/update performance. I figured using reasonably recent .Net drivers would be pretty fast. I would appreciate any suggestions.
Also, I really would like to avoid using ODBC if possible. Unfortunately, we have to be able to access the MariaDB databases from 32 bit Access 2010 as well as C#. The environment we are in is very much locked down so upgrading the OS or MS Office is out of the question. (I wouldn’t have chosen to install 32 bit Office on a 64 bit OS.) Obviously, the performance problem is the priority here, but I would also appreciate it if anyone could point me to some non-ODBC driver I can use to connect in Access VBA with ADODB.
Thanks in advance.

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

Options: ReplyQuote


Subject
Written By
Posted
Write performance problems with MySql and MariaDb
October 12, 2018 03:36PM


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.