MYSQL插入非常慢

Question

我正在将一个大约有900000行的数据表插入到MySQL数据库中，现在在13个小时后，我的程序员已经插入了185000行。我想那是为了减速^^

下面是如何将DataTable插入到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是我自己的类：

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;
    }
}

有谁知道怎么让它更快？

Answer 1

在一条语句中插入几行，如下所示：

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

根据记录的大小，您可能可以在一条语句中插入几十行，如下所示。

Answer 2

我在类似的参数构建过程中发现的是设置参数的"SourceColumn“……在您的"FillParameters“方法中，尝试添加

parameter.SourceColumn = curColumn；

然后，在您的插入例程中，而不是

foreach( DataRow )
   Build The Insert Command
      Execute Insert

更改为

Build The Insert Command
  ForEach( DataRow )
      Execute Insert

这样，您就不必一遍又一遍地构建命令。该参数将转到正在处理的数据行的源列，并为您拉入它。

当我写我的版本时，更新和删除也是一样的。我已经预先构造了命令和参数，并将它们保留到我正在使用的DataTable期间。

因此，实际上可以在foreach( DataRow )之前构建insert和update命令，然后使用它们。再三。显然，在假设所有记录都会通过之前，只测试上传几条记录来确认它是否与您已有的记录一起工作。