Re: Write performance problems with MySql and MariaDb
Problem solved. (I am working with Jerome on this same project.)
We had tried using the latest MySQL (8.0.13) instead of MariaDB and the result was the same-- VERY slow updates. However, using transactions fixed the problem, with both the C# Connector/Net and the MS Access ODBC connector. Adding ~12,500 new records now takes around 10 seconds. Below is the C# code we used. I am still curious as to why using batch update does not speed up inserts and updates, but at least we can use transactions to make the I/O as fast as it needs to be.
Thanks.
private MySqlConnection mConn;
...
public void UpdateDataTable(DataTable dt)
{
MySqlDataAdapter da = new MySqlDataAdapter();
da.UpdateBatchSize = 1000;
MySqlCommandBuilder mcb = new MySqlCommandBuilder(da);
mcb.SetAllValues = false;
MySqlCommand selCom = new MySqlCommand( "SELECT * FROM " + dt.TableName + ";");
selCom.Connection = mConn;
da.SelectCommand = selCom;
MySqlCommand cmdUpdate = mcb.GetUpdateCommand();
da.RowUpdated += da_RowUpdated;
da.RowUpdating += da_RowUpdating;
MySqlTransaction tran = mConn.BeginTransaction();
da.Update(dt);
tran.Commit();
}
Subject
Written By
Posted
Re: Write performance problems with MySql and MariaDb
December 06, 2018 01:17PM
Sorry, you can't reply to this topic. It has been closed.
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.