Re: writing to database slows down

[John Payne]

Unfortunately I have not done much benchmarking with .net with MySQL - confession, most of my experience is with MS SQL Server.. 150 recs in 5 secs does seem on the low side but is the network benchmark from the client or server ? is the server a dedicated database server, would tuning the server help ? How large a row is being written ? Use ISAM instead of InnoDB ?

If it is a issue with client expectations, 400,000 is a large number of records turn the process into an out of hours job ;-)


I am assuming you are using one of the .net data adaptors..

The only other thing I can think of is loading up a dataset with a large number of rows and use a data adaptor to write the whole lot in a chunk say 10,000 at a time. I am not sure if this will speed things up though.

:-

MySqlConnection myConn = new MySqlConnection(DataServices.conn);
MySqlDataAdapter myDataAdapter = new MySqlDataAdapter();

myDataAdapter.InsertCommand = new MySqlCommand("insertchemistrydetails",myConn);

myDataAdapter.InsertCommand.Parameters.Add("?pChemistryDetailsUUID" ,MySqlDbType.VarChar);
myDataAdapter.InsertCommand.CommandType= CommandType.StoredProcedure;
myConn.Open();

try
{
myDataAdapter.Update(ds,"chemistrydetails");