Bug #69260 MySqlDataAdapter.Update runs very slow

• Submitted: 16 May 2013 23:22
• Modified: 21 May 2013 20:59
• Reporter: Craig Clark
• Status: Analyzing
• Category: Connector / NET
• Severity: S5 (Performance)
• Version: 6.6.5
• OS: Windows (Windows 7, MySQL Server 5.6, ASP.Net 4)
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: MS SQL 2012 runs update 60 times faster

[16 May 2013 23:22] Craig Clark

Description:
Using MS Visual Studio 2012, I load a dataset with 10,000 rows.  When I do the execute "mySQLDataAdapter.Update(myDataSet.Tables[0]);" with MS SQL 2012 developer it takes 6 seconds to execute that line.  When I use "myMySQLDataAdapter.Update(myDataSet.Tables[0]);" with MySQL it takes 5 - 6 Minutes to execute that line!  I am using the current release version an took the defaults during installation except the data table is on my second drive (same for both MySQL and MS SQL).  Doing an insert of 10K rows with MySQL Workbench using "insert into Volunteer_Test select * from Volunteer;" takes 2.6 seconds.  Is there anything I can do to give me better performance?

How to repeat:
Table:
CREATE TABLE Volunteer_Test(
	Volunteer_ID int  NOT NULL,
	Name varchar(30) NOT NULL,
	Address varchar(30) NULL,
	Email varchar(30) NULL,
	Phone_Home varchar(30) NULL,
	Phone_Cell varchar(30) NULL,
	Phone_Work varchar(30) NULL,
	Comment varchar(300) NULL);

Program:
using System;
using System.IO;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using MySql.Data.MySqlClient;
namespace TestSQLConsoleApp
{
   class Program
   {
      static void Main(string[] args)
      {
         DataSet myDataSet;
         string strSQL = "SELECT * FROM Volunteer_Test;";
         Console.WriteLine("SQL DataBase Testing Program\n");

         //  MS SQL Test ****************************************************************
         try
         {
            string mySQLconnStr = "Data Source=CRAIG-PC-I7\\SQLEXPRESS;Initial Catalog=Test_DriveD;Integrated Security=SSPI;Persist Security Info=False";
            myDataSet = new DataSet();
            SqlConnection mySQLcon = new SqlConnection(mySQLconnStr);
            mySQLcon.Open();

            // Deleting old data
            SqlCommand mySQLCommand = new SqlCommand("Delete From Volunteer_Test", mySQLcon);
            Console.WriteLine("     Begin MS SQL Delete of 10K rows  " + DateTime.Now.ToString());
            int rowCnt = mySQLCommand.ExecuteNonQuery();
            Console.WriteLine("     End MS SQL Delete of " + rowCnt.ToString() + " rows  " + DateTime.Now.ToString());

            SqlDataAdapter mySQLDataAdapter = new SqlDataAdapter(strSQL, mySQLcon);
            mySQLDataAdapter.AcceptChangesDuringUpdate = true;
            mySQLDataAdapter.Fill(myDataSet, "Volunteer_Test");
            SqlCommandBuilder mySqlCommandBuilder = new SqlCommandBuilder(mySQLDataAdapter);
            
            // Adding 10K rows
            DataRow row;
            for (int rowIndx = 0; rowIndx < 10000; rowIndx++)
            {
               row = myDataSet.Tables[0].NewRow();
               row[0] = (-rowIndx).ToString();
               for (int colIndx = 1; colIndx < myDataSet.Tables[0].Columns.Count; colIndx++)
               {
                  row[colIndx] = rowIndx.ToString() + " - " + colIndx.ToString();
               }

               myDataSet.Tables[0].Rows.Add(row);
            }         

            // Copying 10K rows to the Database
            Console.WriteLine("     Begin MS SQL Insert of 10K rows  " + DateTime.Now.ToString());           
            mySQLDataAdapter.Update(myDataSet.Tables[0]); //,"Volunteer");
            Console.WriteLine("     End MS SQL Insert of 10K rows    " + DateTime.Now.ToString());
            myDataSet = null;
         }
         catch (Exception ex)
         {

            Console.WriteLine("\n*****************************************\nException: " + ex.Message + "\n**********************************\n");
         }

         //  MYSQL Test ****************************************************************
         try
         {
            string myMySQLconnStr = "DataSource=localhost;Database=churchvolunteers;User ID=root;Password=morgan";

            myDataSet = new DataSet();
            MySqlConnection myMySQLcon = new MySqlConnection(myMySQLconnStr);
            myMySQLcon.Open();

            // Deleting old data
            MySqlCommand mySQLCommand = new MySqlCommand("Delete From Volunteer_Test", myMySQLcon);
            Console.WriteLine("\n     Begin MYSQL Delete of 10K rows   " + DateTime.Now.ToString());
            int rowCnt = mySQLCommand.ExecuteNonQuery();
            Console.WriteLine("     End MYSQL Delete of " + rowCnt.ToString() + " rows   " + DateTime.Now.ToString());

            MySqlDataAdapter myMySQLDataAdapter = new MySqlDataAdapter(strSQL, myMySQLcon);
            myMySQLDataAdapter.AcceptChangesDuringUpdate = true;
            myMySQLDataAdapter.Fill(myDataSet, "Volunteer_Test");
            MySqlCommandBuilder myMySqlCommandBuilder = new MySqlCommandBuilder(myMySQLDataAdapter);

            // Adding 10K rows
            DataRow row;
            for (int rowIndx = 0; rowIndx < 10000; rowIndx++)
            {
               row = myDataSet.Tables[0].NewRow();
               row[0] = (-rowIndx).ToString();
               for (int colIndx = 1; colIndx < myDataSet.Tables[0].Columns.Count; colIndx++)
               {
                  row[colIndx] = rowIndx.ToString() + " - " + colIndx.ToString();
               }

               myDataSet.Tables[0].Rows.Add(row);
            }

            Console.WriteLine("     Begin MYSQL Insert of 10K rows   " + DateTime.Now.ToString());
            myMySQLDataAdapter.Update(myDataSet.Tables[0]); //,"Volunteer");
            Console.WriteLine("     End MYSQL Insert of 10K rows     " + DateTime.Now.ToString());
            myDataSet = null;
         }
         catch (Exception ex)
         {

            Console.WriteLine("\n*****************************************\nException: " + ex.Message + "\n**********************************\n");
         }

      }
   }
}

Output:
SQL DataBase Testing Program

     Begin MS SQL Delete of 10K rows  5/16/2013 3:13:41 PM
     End MS SQL Delete of 10000 rows  5/16/2013 3:13:41 PM
     Begin MS SQL Insert of 10K rows  5/16/2013 3:13:41 PM
     End MS SQL Insert of 10K rows    5/16/2013 3:13:45 PM

     Begin MYSQL Delete of 10K rows   5/16/2013 3:13:45 PM
     End MYSQL Delete of 10000 rows   5/16/2013 3:13:45 PM
     Begin MYSQL Insert of 10K rows   5/16/2013 3:13:45 PM
     End MYSQL Insert of 10K rows     5/16/2013 3:19:13 PM