Why is second approach slower when looping through a DataTable column

Question

I have a very large DataTable ~ 4million rows.

I need to calculate columns in the table, and if I process entire column in a method (Go1), it is faster than Go2 where I loop through rows and call method for each row.

I need to use Go2 approach, because later I need to add more rows to the table and update all columns.

But why is Go2 approach slower - is it just overhead of call ProcessRow() each time?

Is there a workaround?

public static void AddSignal()
{
    foreach (DataRow row in Data.Rows)
    {
        row[x] = (invertSignal ? -1:1)*Math.Sign(row.Field<double>(y) - row.Field<double>(y));
    }
}

public class ByRowAddSignal
{
    DataRow row;

    public ByRowAddSignal()
    {

    }

    public void ProcessRow(int r)
    {
        row = Data.Rows[r];
        row[x] = (invertSignal ? -1 : 1) * Math.Sign(row.Field<double>(y) - row.Field<double>(y));
    }
}

Public static DataTable Data;
public void Go1()
{
      Data = LoadData();

      AddSignal();
}

public void Go2()
{
      Data = LoadData();

      ByRowAddSignal byRowAddSignal = new ByRowAddSignal ();

      for (int r = 0; r < Data.Rows.Count; r++)
      {
            byRowAddSignal.ProcessRow(r);
      }
}

Yes, of course. Release mode optimizes your code, to run faster. — rokkerboci, Jan 18 '18 at 17:38
For example, to optimize away an unnecessary method call, and instead inline it — rokkerboci, Jan 18 '18 at 17:39
Just tried it - both approaches run faster - but Go2 is still slower — ManInMoon, Jan 18 '18 at 17:40
With that said, I would refactor your code, as it doesn't make sense to use an entire other class, just for one method. You should have a static method ProcessRow(DataRow row) that would do the magic — rokkerboci, Jan 18 '18 at 17:41
This is a simple example. The actual method are much more complicated and have variables that need to be kept alive — ManInMoon, Jan 18 '18 at 17:44

Titian Cernicova-Dragomir · Answer 1 · 2018-01-18T17:42:14.747

1

Looking at the code for DataRowCollection we find the following:

public DataRow this[int index]
{
    get
    {
        return ((RBTree<DataRow>)this.list)[index];
    }
}

And RBTree<K> is actually a tree not an array backed list, so indexing into it is complicated, as on each index call you need to iterate to the aproproate element. The code from RBTree<K> shows this:

public K this[int index]
{
    get
    {
        return this.Key(this.GetNodeByIndex(index).NodeID);
    }
}
private NodePath GetNodeByIndex(int userIndex)
{
    int num;
    int mainTreeNodeID = default(int);
    if (this._inUseSatelliteTreeCount == 0)
    {
        num = this.ComputeNodeByIndex(this.root, userIndex + 1);
        mainTreeNodeID = 0;
    }
    else
    {
        num = this.ComputeNodeByIndex(userIndex, out mainTreeNodeID);
    }
    if (num == 0)
    {
        if (TreeAccessMethod.INDEX_ONLY == this._accessMethod)
        {
            throw ExceptionBuilder.RowOutOfRange(userIndex);
        }
        throw ExceptionBuilder.InternalRBTreeError(RBTreeError.IndexOutOFRangeinGetNodeByIndex);
    }
    return new NodePath(num, mainTreeNodeID);
}
private int ComputeNodeByIndex(int x_id, int index)
{
    while (x_id != 0)
    {
        int num = this.Left(x_id);
        int num2 = this.SubTreeSize(num) + 1;
        if (index < num2)
        {
            x_id = num;
        }
        else
        {
            if (num2 >= index)
            {
                break;
            }
            x_id = this.Right(x_id);
            index -= num2;
        }
    }
    return x_id;
}

Note Code decompiled with ILSpy

edited Jan 18 '18 at 17:42

answered Jan 18 '18 at 17:40

Titian Cernicova-Dragomir

230,986
31
415
357

OK - so what can I do about it? – ManInMoon Jan 18 '18 at 17:42
1

Use `foreach` to iterate the tree once or put the rows in a list and use that to index into (although at 4 milion records that will be a memory issue) – Titian Cernicova-Dragomir Jan 18 '18 at 17:43
You could use the IEnumerable's ToList() extension method. – rokkerboci Jan 18 '18 at 17:43
1

@rokkerboci Yup that would be the simplest approach to get a List from the rows, but the memory considerations are an issue .. – Titian Cernicova-Dragomir Jan 18 '18 at 17:44
@BlueMonkMN I fully agree with you, `foreach` is by far the best way to go but the question specifically says he needs to index into the row collection for some reason. I would suggest refactoring the code that requires this to work without indexing – Titian Cernicova-Dragomir Jan 18 '18 at 17:52
Yes I tried doing a foreach and passing in the DataRow, but it didn't appear to make much difference. Looks like the ProcessRow call is the overhead – ManInMoon Jan 18 '18 at 17:53
Indexing will make it slower, depending on the size of the Process row method, if little work is done there the call overhead may be significant .. the no call version will be faster over 4 milion iterations for sure – Titian Cernicova-Dragomir Jan 18 '18 at 17:55
While just a hint if the method is small consider inlineing it https://stackoverflow.com/questions/473782/inline-functions-in-c – Titian Cernicova-Dragomir Jan 18 '18 at 17:56

Why is second approach slower when looping through a DataTable column

1 Answers1