MYSQL Insert very slow

Question

i'm inserting a datatable with about 900000 rows into a MySQL DB, Now after 13 hours, my Programm has inserted 185000 rows. Thats to slow i think ^^

Here is my code how i insert my DataTable into MYSQL DB

//data is typeof DataTable and filled with my data.

using (System.Data.SqlClient.SqlConnection srcConnection = new SqlConnection(Settings.Default.SourceConnection))
using (MySql.Data.MySqlClient.MySqlConnection dstConnection = new MySqlConnection(Settings.Default.TargetConnection))
{
    if (dstConnection.State != ConnectionState.Open)
        dstConnection.Open();
    if (srcConnection.State != ConnectionState.Open)
        srcConnection.Open();

    var builder = SqlBuilder(tableName, data)

    foreach (DataRow toCopy in data)
    {
        using (var cmdUpdate = builder.GenerateUpdateCommand(dstConnection, toCopy))
        {
            if (cmdUpdate.ExecuteNonQuery() == 0)
            {
                using (var cmdInsert = builder.GenerateInsertCommand(dstConnection, toCopy))
                {
                    cmdInsert.ExecuteNonQuery;
                }
            }
        }
    }
}

builder is my own class:

public class SqlBuilder
{
    private readonly string _tableName;
    private readonly DataColumnCollection _columns;
    private readonly List<string> _columnStrings = new List<string>();
    private DataColumn _primaryDataColumn;
    private readonly Logger _logger = LogManager.GetLogger(typeof(SqlBuilder).FullName);

    public SqlBuilder(string tableName, DataTable table)
    {
        if (tableName == null) throw new ArgumentNullException("tableName");
        if (table == null) throw new ArgumentNullException("table");
        if (table.PrimaryKey.Count() != 1) throw new ArgumentException("Only one primary key supported", "table");

        _tableName = tableName;
        _columns = table.Columns;
        _primaryDataColumn = table.PrimaryKey[0];

        foreach (DataColumn column in _columns)
            _columnStrings.Add(column.ColumnName);
    }

    public SqlBuilder()
    {
    }

    public string TableName
    {
        get { return _tableName; }
    }

    public String[] ColumnStrings
    {
        get { return _columnStrings.ToArray(); }
    }


    public IDbCommand GenerateCountCommand(IDbConnection con)
    {
        IDbCommand result = con.CreateCommand();
        result.CommandText = String.Format("SELECT COUNT(*) FROM {0}", TableName);
        return result;
    }

    public IDbCommand GenerateDeleteTableCommand(IDbConnection con)
    {
        IDbCommand result = con.CreateCommand();
        result.CommandText = String.Format("DELETE FROM {0}", TableName);
        return result;
    }

    public IDbCommand GenerateDeleteChangeLogTableCommand(IDbConnection con)
    {
        IDbCommand result = con.CreateCommand();
        result.CommandText = String.Format("DELETE FROM ChangeLog");
        return result;
    }

    public String GenerateInsertCommandText()
    {
        String result;

        result = String.Format(
            "INSERT INTO {0} ({1}) VALUES ({2})",
            TableName,
            String.Join(",", _columnStrings.ToArray()),
            String.Join(",", _columnStrings.Select(s => "@" + s).ToArray()));

        return result;
    }

    public string GenerateUpdateCommandText()
    {
        String result;

        result = String.Format(
            "UPDATE {0} SET {1} WHERE {2}",
            TableName,
            String.Join(", ", _columnStrings.Where((s =>!_primaryDataColumn.ColumnName.Equals(s))).Select(s => s+"=@" + s).ToArray()),
            _primaryDataColumn.ColumnName+"=@"+_primaryDataColumn.ColumnName
            );

        return result;
    }

    public string GenerateDeleteCommandText(int id)
    {
        string result = String.Format("DELETE FROM {0} WHERE {1} = {2}", _tableName , _columnStrings[0], id);
        return result;
    }

    public IDbCommand GenerateInsertCommand(IDbConnection con, DataRow row)
    {
        IDbCommand result = con.CreateCommand();
        result.CommandText = GenerateInsertCommandText();

        result = FillParameters(row, result);

        if (_logger.IsTraceEnabled) _logger.Trace(result.CommandText);

        return result;
    }

    public IDbCommand GenerateUpdateCommand(IDbConnection connection, DataRow dataRow)
    {
        IDbCommand result = connection.CreateCommand();
        result.CommandText = GenerateUpdateCommandText();

        result = FillParameters(dataRow, result);

        if (_logger.IsTraceEnabled) _logger.Trace(result.CommandText);

        return result;
    }

    public IDbCommand GenerateDeleteCommand(IDbConnection con, int id)
    {
        IDbCommand result = con.CreateCommand();

        result.CommandText = GenerateDeleteCommandText(id);

        return result;
    }

    private IDbCommand FillParameters(DataRow row, IDbCommand result)
    {
        foreach (var curColumn in _columnStrings)
        {
            IDbDataParameter parameter = result.CreateParameter();
            parameter.ParameterName = curColumn;
            parameter.Value = row[curColumn];
            result.Parameters.Add(parameter);
        }

        return result;
    }
}

Anyone any idea how to make this faster???

Answer 1

Insert several rows in one statement, like this:

INSERT INTO table (...) VALUES 
(values for first record),
(values for second record),
...

You probably can insert a couple of dozen rows in one statement like this, depending on the record size.

Answer 2

What I've found in a similar building process for parameters was to set the "SourceColumn" of the parameter... In your "FillParameters" method try adding

parameter.SourceColumn = curColumn;

Then, during your INSERT routine, instead of

foreach( DataRow )
   Build The Insert Command
      Execute Insert

change to

Build The Insert Command
  ForEach( DataRow )
      Execute Insert

This way, you don't have to keep building the command over and over. The parameter will go to the source column of the data row being processed and pull it in for you.

When I wrote my version, the same is applied to the update and delete too. I have the commands and parameters constructed up front and leave them for the duration of the DataTable I'm working with.

So you can actually build both the insert and update command before the foreach( DataRow ), then just use them. over and over. Obviously, test uploading only a few records to confirm it works with what you have otherwise before assuming it will all go through.