Speeding up DataTable iteration

Question

So I have a DataTable data that needs to get some column's content replaced.

string[] columns = { "Column1", "Column2", "Column3" };
int rowCount = data.Rows.Count;
int colCount = columns.Length;
for (int i = 0; i < rowCount; i++)
{
    for (int j = 0; j < columnsCount; j++)
    {
        data.Rows[i][columns[j]] = "replace text";
    }
}

The code does it's job, but it's slow as the DataTable has more records. 12k rows is around 4 minutes.

How could I speed up the process?

What is your replace operation? is it straight assignment of a new constant string (i.e. `"replace text"`) or is it some method? — Thomas N, Jan 31 '20 at 12:10
It's a function that takes the original string and removes several characters using string.Replace() — bapster, Jan 31 '20 at 12:14
Does this answer your question? [Parallel ForEach on DataTable](https://stackoverflow.com/questions/3408557/parallel-foreach-on-datatable) — Murray Foxcroft, Jan 31 '20 at 12:17
@bapster then unbind it for the duration of this work? (or suspend UI updates); mutating a 12k rows datatable should be pretty fast (as in: milliseconds) (note: datatable *is very rarely* a good choice for anything these days, but I'm guessing you don't want to change the actual storage mechanism that you're using) — Marc Gravell, Jan 31 '20 at 12:24
Have you tried using SuspendLayout() & ResumeLayout() before and after your code? — Mauricio Atanache, Jan 31 '20 at 13:09
Disabling databinding during the process was the key. Thank you guys — bapster, Jan 31 '20 at 13:46

score 1 · Accepted Answer · answered Jan 31 '20 at 12:27

So with 1 million rows and 3 columns a find and replace on every cell takes ~2 seconds (on my PC). I would guess that you have a problem with databindings that are responding to every change or your replace operation is very slow for some reason.

    Stopwatch sw = new Stopwatch();

    DataTable dt = new DataTable();
    dt.Columns.Add("Column1");
    dt.Columns.Add("Column2");
    dt.Columns.Add("Column3");

    Random r = new Random();
    sw.Start();
    for (int i = 0; i < 1_000_000; i++)
    {
        dt.Rows.Add(dt.NewRow());
        for (int j = 0; j < 3; j++)
            dt.Rows[i][j] = r.Next().ToString();
    }

    Console.WriteLine("Row Count:" + dt.Rows.Count);
    Console.WriteLine("Rows with the digit 0:" + dt.Rows.Cast<DataRow>().Count(row=>((string)row[0]).Contains('0')));

    sw.Stop();
    Console.WriteLine("Time taken to fill table:" + sw.Elapsed);

    sw.Reset();
    sw.Start();
    for( int i = 0 ; i < 1_000_000 ; i++)
        for (int j = 0; j < 3; j++)
            dt.Rows[i][j] = ((string) dt.Rows[i][j]).Replace('0', '1');

    sw.Stop();
    Console.WriteLine("Row Count:" + dt.Rows.Count);
    Console.WriteLine("Rows with the digit 0:" + dt.Rows.Cast<DataRow>().Count(row=>((string)row[0]).Contains('0')));
    Console.WriteLine("Time taken to update table:" + sw.Elapsed);

    Console.ReadLine();

Speeding up DataTable iteration

1 Answers1