Fastest way to update more than 50.000 rows in a mdb database c#

Question

I searched on the net something but nothing really helped me. I want to update, with a list of article, a database, but the way that I've found is really slow.

This is my code:

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();

But this way take a lot of minutes something like 10 minutes or more. There are another way to speed up this updating ? Thanks.

you lay try to desable the constraints on that table, but becarefull of the new data, that may be not verify the constraint when you will activate it again. — Blood-HaZaRd, Jul 03 '14 at 08:06
Before entering the `for` loop try creating the parameters with `cmd.Parameters.Add()` and then do `cmd.Prepare()`. Then, inside the loop just assign the parameter values and `.ExecuteNonQuery()`. That might speed things up a bit. — Gord Thompson, Jul 03 '14 at 08:23

John Willemse · Accepted Answer · 2014-07-03T10:03:55.863

Try modifying your code to this:

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();

The cmd.Prepare method will speed things up since it creates a compiled version of the command on the data source.

John i think that this is a good solution, but after update of 2 articles, return an error on ExecuteNonQuery(). The error is **exception of type system.exception** — puti26, Jul 03 '14 at 08:56
The `if (cmd.ExecuteNonQuery() != 1)` caused the exception to be thrown. ExecuteNonQuery returns the number of rows affected. If you modify the code inside the loop like I did it should work. — John Willemse, Jul 03 '14 at 10:05
Yes, thank you. Just now i've seen that the rows updated was 3 and not 1, thank you again ! It work now :D — puti26, Jul 03 '14 at 10:11

score 1 · Answer 2 · answered Jul 03 '14 at 08:09

1

Small change option:

Using StringBuilder and string.Format construct one big command text.

var sb = new StringBuilder();

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

Even faster option:

As in first example construct a sql but this time make it look (in result) like:

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

This is the fastest you can get without using bulk inserts.

answered Jul 03 '14 at 08:09

jjaskulowski

2,524
3
26
36

I just figured out it probably is not T-SQL. For Oracle update join i found this post: http://stackoverflow.com/questions/2446764/oracle-update-statement-with-inner-join Yet, the programming approach remains the same. – jjaskulowski Jul 03 '14 at 08:16
With the first way i should use the executeNonQuery out of the for loop, and cmd.CommandText = sb.ToString(); right? – puti26 Jul 03 '14 at 09:29
But doesn't work, i use access mdb. Maybe is this the reason ? – puti26 Jul 03 '14 at 09:52
_unexpected characters after the end of the statement sql_ – puti26 Jul 03 '14 at 10:03
Try to end each insert statement with `;`. Did that help? – jjaskulowski Jul 03 '14 at 10:05

Peter Brennan · Answer 3 · 2014-07-03T10:03:07.930

Performing mass-updates with Ado.net and OleDb is painfully slow. If possible, you could consider performing the update via DAO. Just add the reference to the DAO-Library (COM-Object) and use something like the following code (caution -> untested):

// 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();

Note the fact that we are doing a full table scan here. But, unless the total number of records in the table is many orders of magnitude bigger than the number of updated records, it should still outperform the Ado.net approach significantly...

Fastest way to update more than 50.000 rows in a mdb database c#

3 Answers3