最快的方式更新超过50,000行mdb数据库c#

本文关键字：000行 mdb 数据库 方式 更新 | 更新日期: 2023-09-27 18:11:11

我在网上搜索了一些东西，但没有什么真正帮助我。我想更新，用文章列表，一个数据库，但是我发现的方式真的很慢。

这是我的代码:

List<Article> costs = GetIdCosts(); //here there are 70.000 articles
conn = new OleDbConnection(string.Format(MDB_CONNECTION_STRING, PATH, PSW));
conn.Open();
transaction = conn.BeginTransaction();
using (var cmd = conn.CreateCommand())
{
    cmd.Transaction = transaction;
    cmd.CommandText = "UPDATE TABLE_RO SET TABLE_RO.COST = ? WHERE TABLE_RO.ID = ?;";
    for (int i = 0; i < costs.Count; i++)
    {
        double cost = costs[i].Cost;
        int id = costs[i].Id;
        cmd.Parameters.AddWithValue("data", cost);
        cmd.Parameters.AddWithValue("id", id);
        if (cmd.ExecuteNonQuery() != 1) throw new Exception();
    }
}
transaction.Commit();

但是这种方法要花很多时间，比如10分钟或更长时间。还有其他方法可以加快这个更新吗?谢谢。

最快的方式更新超过50,000行mdb数据库c#

试着这样修改你的代码:

List<Article> costs = GetIdCosts(); //here there are 70.000 articles
// Setup and open the database connection
conn = new OleDbConnection(string.Format(MDB_CONNECTION_STRING, PATH, PSW));
conn.Open();
// Setup a command
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
cmd.CommandText = "UPDATE TABLE_RO SET TABLE_RO.COST = ? WHERE TABLE_RO.ID = ?;";
// Setup the paramaters and prepare the command to be executed
cmd.Parameters.Add("?", OleDbType.Currency, 255);
cmd.Parameters.Add("?", OleDbType.Integer, 8); // Assuming you ID is never longer than 8 digits
cmd.Prepare();
OleDbTransaction transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
// Start the loop    
for (int i = 0; i < costs.Count; i++)
{
    cmd.Parameters[0].Value = costs[i].Cost;
    cmd.Parameters[1].Value = costs[i].Id;
    try
    {
        cmd.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        // handle any exception here
    }
}
transaction.Commit();
conn.Close();

cmd.Prepare方法将加快速度，因为它在数据源上创建命令的编译版本。

小改动选项:

使用StringBuilder和string。格式构造一个大的命令文本。

var sb = new StringBuilder();
for(....){
  sb.AppendLine(string.Format("UPDATE TABLE_RO SET TABLE_RO.COST = '{0}' WHERE TABLE_RO.ID = '{1}';",cost, id));
}

更快的选项:

与第一个示例一样，构造一个sql，但这次使它看起来像(在结果中):

-- declaring table variable
declare table @data (id int primary key, cost decimal(10,8))  
-- insert union selected variables into the table
insert into @data
      select 1121 as id, 10.23 as cost
union select 1122 as id, 58.43 as cost
union select ...

-- update TABLE_RO using update join syntax where inner join data
-- and copy value from column in @data to column in TABLE_RO
update dest 
set dest.cost = source.cost
from TABLE_RO dest
inner join @data source on dest.id = source.id

使用Ado.net和OleDb执行大规模更新非常缓慢。如果可能的话，可以考虑通过DAO执行更新。只需将引用添加到dao库(COM-Object)并使用类似以下代码的内容(注意->未经测试):

// Import Reference to "Microsoft DAO 3.6 Object Library" (COM)
string TargetDBPath = "insert Path to .mdb file here";
DAO.DBEngine dbEngine = new DAO.DBEngine();
DAO.Database daodb = dbEngine.OpenDatabase(TargetDBPath, false, false, "MS Access;pwd="+"insert your db password here (if you have any)");
DAO.Recordset rs = daodb.OpenRecordset("insert target Table name here", DAO.RecordsetTypeEnum.dbOpenDynaset);
if (rs.RecordCount > 0)
{
    rs.MoveFirst();
    while (!rs.EOF)
    {
        // Load id of row
        int rowid = rs.Fields["Id"].Value;
        // Iterate List to find entry with matching ID
        for (int i = 0; i < costs.Count; i++)
        {
            double cost = costs[i].Cost;
            int id = costs[i].Id;
            if (rowid == id)
            {
                // Save changed values
                rs.Edit();
                rs.Fields["Id"].Value = cost;
                rs.Update();
            }
        }
        rs.MoveNext();
    }
}
rs.Close();

请注意，我们在这里执行的是全表扫描。但是，除非表中记录的总数比更新记录的数量大很多个数量级，否则它仍然应该明显优于Ado.net方法…

相关文章：