.Net C# Connector/Net Parameters very slow

Question

I am using the .net MYSQL Connector: https://dev.mysql.com/downloads/file.php?id=455843 to establish a mysql connection within C#. I have installed MariaDB 10.0 on my localhost Windows. DB Engine is MyISAM.

Problem:

I have to make around 500k inserts from an existing csv file. To make the source more readable i used prepared Statements. This took me around 38 seconds to process 1000 rows. Obviously this is WAY to much. Note: I not even tried to execute the query with parameters.

public void process(String commandText, Dictionary<string, string> param, int satz)
    {


        command.CommandText = commandText;
        // command.Prepare();
        Console.WriteLine(satz);
        foreach(KeyValuePair<string, string> pair in param)
        {

            try {
                command.Parameters.AddWithValue(pair.Key, pair.Value);

            } catch (MySqlException ex)
            {
                /* some stuff */
            }
        }
         // command.ExecuteNonQuery();
    }

Since i dont know exactly what AddWithValue does i have checked a sql profiler. It shows me that he made 4 requests in total => its not databases related.

As soon i change the commandText to a direct INSERT Statement without Parameters the function took me around 0.8 sec.

  public void process(String commandText, Dictionary<string, string> param, int satz)
    {


        command.CommandText = commandText;
        // command.Prepare();
        Console.WriteLine(satz);
        foreach(KeyValuePair<string, string> pair in param)
        {

            try {
                //command.Parameters.AddWithValue(pair.Key, pair.Value);
                //command.Parameters[pair.Key].DbType = System.Data.DbType.AnsiString;

            } catch (MySqlException ex)
            {
                /*if (ex.ErrorCode == -2147467259)
                    continue;
                else
                    throw ex;*/
            }
        }
         command.ExecuteNonQuery();
    }

Here is the generated SQL Query

 public void process210(String[] attributes)
    {
        if (++line210 > 1000) return;
        Console.Write(line210+" : ");
        // String commandText = "INSERT INTO Programm (Serien_Nr, Prog_Schluessel, Prog_Name, Preisfeld, Front_Tiefe, Preisgruppe, Preisfeld_Block, Lieferbar_ab, Lieferbar_bis, Frontauspraegungs_ID, Frontgruppe, Var_Familien_Nr, Varianten_Art_Front, Var_Schluessel_Front, Mindest_Lieferzeit, Hersteller_ID) values (@Serien_Nr, @Prog_Schluessel, @Prog_Name, @Preisfeld, @Front_Tiefe, @Preisgruppe, @Preisfeld_Block, @Lieferbar_ab, @Lieferbar_bis, @Frontauspraegungs_ID, @Frontgruppe, @Var_Familien_Nr, @Varianten_Art_Front, @Var_Schluessel_Front, @Mindest_Lieferzeit, @Hersteller_ID) ;";
        String programm_Serien_Nr = attributes[1].Replace("\"", "").Replace(";", "");
        String programm_Prog_Schluessel = attributes[2].Replace("\\", "").Replace("\"", "").Replace(";", "");
        String programm_Prog_Name = attributes[3].Replace("\\", "").Replace("\"", "").Replace(";", "");
        String programm_Preisfeld = attributes[4].Replace("\\", "").Replace("\"", "").Replace(";", "");
        String programm_Front_Tiefe = attributes[5].Replace("\\", "").Replace("\"", "").Replace(";", "");
        String programm_Preisgruppe = attributes[6].Replace("\\", "").Replace("\"", "").Replace(";", "");
        String programm_Preisfeld_Block = attributes[7].Replace("\\", "").Replace("\"", "").Replace(";", "");
        String programm_Lieferbar_Ab = attributes[8].Replace("\\", "").Replace("\"", "").Replace(";", ""); 
        String programm_Lieferbar_Bis = attributes[9].Replace("\\", "").Replace("\"", "").Replace(";", "");
        String programm_Frontauspraegungs_ID = attributes[10].Replace("\\", "").Replace("\"", "").Replace(";", ""); 
        String programm_Frontgruppe = attributes[11].Replace("\\", "").Replace("\"", "").Replace(";", ""); 
        String programm_Var_Familien_Nr = attributes[12].Replace("\\", "").Replace("\"", "").Replace(";", ""); 
        String programm_Varianten_Art_Front = attributes[13].Replace("\\", "").Replace("\"", "").Replace(";", ""); 
        String programm_Var_Schluessel_Front = attributes[14].Replace("\\", "").Replace("\"", "").Replace(";", ""); 
        String programm_Mindest_Lieferzeit = attributes[15].Replace(";", "").Replace("\\", "").Replace("\"", ""); 

        String commandText = "INSERT INTO Programm (Serien_Nr, Prog_Schluessel, Prog_Name, Preisfeld, Front_Tiefe, Preisgruppe, Preisfeld_Block, Lieferbar_ab, Lieferbar_bis, Frontauspraegungs_ID, Frontgruppe, Var_Familien_Nr, Varianten_Art_Front, Var_Schluessel_Front, Mindest_Lieferzeit, Hersteller_ID) values (\""+ programm_Serien_Nr + "\", \"" + programm_Prog_Schluessel + "\", \"" + programm_Prog_Name + "\", \"" + programm_Preisfeld + "\", \"" + programm_Front_Tiefe + "\", \"" + programm_Preisgruppe + "\", \"" + programm_Preisfeld_Block + "\", \"" + programm_Lieferbar_Ab + "\", \"" + programm_Lieferbar_Bis + "\", \"" + programm_Frontauspraegungs_ID + "\", \"" + programm_Frontgruppe + "\", \"" + programm_Var_Familien_Nr + "\", \"" + programm_Varianten_Art_Front + "\", \"" + programm_Var_Schluessel_Front + "\", \"" + programm_Mindest_Lieferzeit + "\", \"" + hersteller_id + "\") ;";
        Dictionary<string, string> dictionary = new Dictionary<string, string>();
        dictionary.Add("@Serien_Nr", attributes[1]);
        dictionary.Add("@Prog_Schluessel", attributes[2]);
        dictionary.Add("@Prog_Name", attributes[3]);
        dictionary.Add("@Preisfeld", attributes[4]);
        dictionary.Add("@Front_Tiefe", attributes[5]);
        dictionary.Add("@Preisgruppe", attributes[6]);
        dictionary.Add("@Preisfeld_Block", attributes[7]);
        dictionary.Add("@Lieferbar_ab", attributes[8]);
        dictionary.Add("@Lieferbar_bis", attributes[9]);
        dictionary.Add("@Frontauspraegungs_ID", attributes[10]);
        dictionary.Add("@Frontgruppe", attributes[11]);
        dictionary.Add("@Var_Familien_Nr", attributes[12]);
        dictionary.Add("@Varianten_Art_Front", attributes[13]);
        dictionary.Add("@Var_Schluessel_Front", attributes[14]);
        dictionary.Add("@Mindest_Lieferzeit", attributes[15]);
        dictionary.Add("@Hersteller_ID", hersteller_id);
        process(commandText, dictionary, 210);
    }

Any Ideas why it is so terrible slow? I rly would like to use prepared statements since it improves the readability of the code a lot.

Edit:

Like Andy Nichols said i forgot to flush the Collection from MysqlCommand after the execution. Thank you.

Solution would be like this

public static void process(String commandText, Dictionary<string, string> param, int satz)
    {
        command = new MySqlCommand();
        command.Connection = con;
        command.CommandText = commandText;
        command.Prepare();
        foreach (KeyValuePair<string, string> pair in param)
        {

            try {
                command.Parameters.AddWithValue(pair.Key, pair.Value);

            } catch (MySqlException ex)
            {
                /* stuff */
            }
        }

        command.ExecuteNonQuery();
    }

Answer 1

As established in the comments.

The command object is being reused in a loop - one input statement per row. On each iteration you are calling command.Parameters.AddWithValue for each of the 16 parameters. This means your parameter collection is increasing by 16 per row.

To fix this you need to either:

Clear the parameters before the foreach loop which adds them

or

Set up the parameters without values when first creating the command object. Then, on each iteration set the values. You don't actually need to clear and re-add the parameters each time if the name and datatype are unchanged.