1
\$\begingroup\$

I am using a DataTable and the MySqlAdapater to insert around 100 000 rows with around 40 columns to a MySQL database.

I chose this way, because my program needs run on mono (unix / mac) where I could not establish a connection to my database via the System.Data.Sql package, but only via the .NET connector Mysql.Data.MySqlClient which has no SqlBulkCopy-method.

Additionally the amount of columns in my table is flexible, so I was looking for an easy way to iterate the columns. Thats why I used the way:

  • Parsing my data
  • Loading it into a DataTable Object
  • Create a MySQL table dynamically based on my DataTable Object
  • Use a MySqlDataAdapter to bulk insert the DataTable to the Database via its .Update()-method

Right now, it takes around 3-4 minutes to insert 100 000 rows this way - which is subjectively slow (mac mini 2012, i7, 512 GB SSD, 16 GB ram)

Is there any way to speed this up with the Mysql.Data.MySqlClient ?

public static void Import(DataTable dt, string tableName, string conString)
        {
            using (MySqlConnection con = new MySqlConnection(conString))
            {
                con.Open();

                string createTableString = GetCreateTableSql(dt, tableName);
                MySqlCommand cmd = new MySqlCommand(createTableString, con);
                cmd.ExecuteNonQuery();

                using (MySqlCommand selectcmd = new MySqlCommand("SELECT * FROM " + dt.TableName, con))
                {
                    using (MySqlDataAdapter sda = new MySqlDataAdapter())
                    {
                        MySqlCommandBuilder builder = new MySqlCommandBuilder(sda);
                        sda.SelectCommand = selectcmd;

                        builder.GetUpdateCommand();

                        using (DataTable dttemp = new DataTable())
                        {
                            sda.Fill(dttemp);
                            // prevent duplicating data
                            if (dttemp.Rows.Count == 0)
                            {
                                dttemp.Merge(dt);
                                sda.Update(dttemp);
                            }
                            else 
                            {
                                Console.WriteLine("Data already present. Delete manually");
                            }

                        }
                    }
                }
            }
        }
\$\endgroup\$
  • 1
    \$\begingroup\$ Do you have indexes on the table? If so try dropping them and recreating after the insert. \$\endgroup\$ – 404 Dec 29 '16 at 15:10
  • 1
    \$\begingroup\$ Have you tried setting UpdateBatchSize on the MySqlDataAdapter to something like 500 or 1000? \$\endgroup\$ – RobH Dec 29 '16 at 15:23
  • \$\begingroup\$ I don't follow why you are loading dttemp.Merge(dt); from dt.TableName , then merging, and then update. \$\endgroup\$ – paparazzo Dec 29 '16 at 16:59
  • 1
    \$\begingroup\$ My experience w/ Merge is that it is sloooow. We threw away one that took 8+ hours. As a stored procedure it now runs in seconds. This was the absolute worst culprit, but in general C# pretending to be a RDBMS is a poor performer. An RDBMS engine is optimal for doing relational set operations. Use it. The "oh look, Mo! Working disconnected!" Microsoft propaganda is a pig in a poke. P.S. Ours is an Oracle DB. \$\endgroup\$ – radarbob Dec 29 '16 at 19:28
  • \$\begingroup\$ @eurotrash I can not use sda.Update() without indexes. @RobH thanks for the hint, but I tried (1000) and it took twice as much time as before \$\endgroup\$ – ManuKaracho Dec 29 '16 at 20:16
3
\$\begingroup\$

Some quick remarks:

  • Naming is important. I don't consider dt, conString, con, etc. proper names since they don't tell me enough. There's no need to abbreviate. selectcmd doesn't follow Microsoft's rules about compound words, and dttemp even contains the dreaded "temp".

  • You haven't encapsulated the MySqlCommand which creates the table in a using.

  • MySqlCommandBuilder inherits from a class that implements IDisposable, so perhaps that too should be used in a using statement?

\$\endgroup\$
  • \$\begingroup\$ thanks for the hints! I will take more care about this. Do you have a hint for the performance issues as well? \$\endgroup\$ – ManuKaracho Dec 29 '16 at 20:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.