Re: Extremely slow C# dataset update preformance
Posted by: Delbert DeWitty
Date: December 06, 2018 01:06PM

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.

This fix for the MySQL ODBC I/O in MS Access was the same. Simply doing the recordset update as a transaction made all the I/O extremely fast.


What I am curious about is, what is the batch update actually good for then? I had been using batch update (both with the C# MySQL Connector/Net 8.0.13 and the Access MySQL ODBC connector 8.0.13) and it didn't speed the updates at all. In C#, I found no difference with the update time. In Access, with an ADODB recordset, the batch update method did only one thing: it moved the place that took all the time from the recordset update loop to the ".UpdateBatch" method on the recordset.


So, I am now able to do what I need to do in both MS Access and C# and it's really fast but I would still like to find out what the batch update is really for. Maybe I missed some parameter when I used it?


Here is the C# sample code I used:

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();


}

Options: ReplyQuote


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


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.