0

I'm trying to insert a large amount of data to MySQL table from my C# app. I can't figure out why it takes 14 minutes to insert 6500 rows of data, each row has 4 columns.

At first, I was using simple "insert" command but it wasn't effective, I read that adapter.Update method is better, I tried it now but it still isn't fast enough...

    //Create new dataset
    DataSet DataTariff = new DataSet("DataTariff");
                DataTariff.Tables.Add();
                for (int i = 0; i < ClientInfoTabels.Tables[n].Rows.Count; i++)
                {


                    DataTable FirstCycle = new DataTable();
                    MySqlDataAdapter DataTariffAdapter = new MySqlDataAdapter("select* from customers.`" + ClientsIDList[n].ToString() + "_" + (i + 1).ToString() + "` ORDER BY Date DESC LIMIT 1;", ConnectionString);
                    MySqlCommandBuilder DataTariffCmb = new MySqlCommandBuilder(DataTariffAdapter);

//Get table format from MySQL table

                    DataTariffAdapter.Fill(FirstCycle);

                    DataTable TempTable = new DataTable();
                    TempTable = DataCalc.Tables[ClientInfoTabels.Tables[n].Rows[i][4].ToString()];

                    DataTariffAdapter = new MySqlDataAdapter("select* from customers.`" + ClientsIDList[n].ToString() + "_" + (i + 1).ToString() + "` ORDER BY Date DESC LIMIT 0;", ConnectionString);
                    DataTariffCmb = new MySqlCommandBuilder(DataTariffAdapter);
                    DataTariff.Tables.Add(ClientsIDList[n].ToString() + "_" + (i + 1).ToString());
                    DataTariffAdapter.Fill(DataTariff.Tables[ClientsIDList[n].ToString() + "_" + (i + 1).ToString()]);
                    DataTariff.Tables[i + 1].ImportRow(FirstCycle.Rows[0]);

//Do calculations , Import the data to the TempTable - This takes few seconds 

                    foreach (DataRow dr in TempTable.Rows)
                    {
                        acr.ImportRow(DataTariff.Tables[i + 1], DataTariff.Tables[i + 1].Rows[DataTariff.Tables[i + 1].Rows.Count - 1]);
                        acr.SortByTariff(dr, DataTariff.Tables[i + 1], ElecTariff, i + 1, ClientInfoTabels.Tables[n]);

                    }

                    DataTariff.Tables[ClientsIDList[n].ToString() + "_" + (i + 1).ToString()].Rows.RemoveAt(0);

//Insert new data back to MySQL table - THIS TAKES 14 MINUTES per 6.5K rows!!

                  DataTariffAdapter.Update(DataTariff.Tables[ClientsIDList[n].ToString() + "_" + (i + 1).ToString()]);

                }

I was expecting to insert 6.5K rows in a few minutes, maybe it isn't realistic of me but I hope someone can help me make things faster.

Tom Boudniatski
  • 67
  • 6
  • Can you put some comments in the code to explain which line in code doing what? – Chetan Jul 08 '19 at 04:28
  • I added some comments but the problem is when I try to send the data back to the MySQL database table with DataTariffAdapter.Update("tablename"); – Tom Boudniatski Jul 08 '19 at 05:31
  • https://stackoverflow.com/questions/31287371/dataadapter-update-performance – xdtTransform Jul 08 '19 at 06:20
  • The issue here is that you run an SQL for each row. Using this method. that's for the why. For the "how to be faster" I will recommend [Bluck insert on temp table, then either merge or update with join](https://stackoverflow.com/questions/44166544/fastest-way-of-performing-bulk-update-in-c-sharp-net) – xdtTransform Jul 08 '19 at 06:21
  • @xdtTransform was asking about MySQL. The line {command.Connection = new SqlConnection("Data Source=.;Initial Catalog=mydb;Integrated Security=SSPI"); } isn't supported by MySQL as far as I know , I think it's MS SQL – Tom Boudniatski Jul 08 '19 at 06:35
  • Ho yep yep MySQL, Use the MySQL connector and build the transaction then comit https://stackoverflow.com/questions/30615443/bulk-copy-a-datatable-into-mysql-similar-to-system-data-sqlclient-sqlbulkcopy – xdtTransform Jul 08 '19 at 06:39
  • but most bluck insert lib don't care if it's MySQL, Ms sql Orable or they just need a connection string and object that match the table. – xdtTransform Jul 08 '19 at 06:40

0 Answers0