Is there any way to speed up datatable.LoadDataRow()?

Question

I am refreshing a data table by calling DataTable.LoadDatatRow as follows:

public void FillTable(DataTable myTable, IEnumerable<MyObject> readings)
{
    var index=0;

    foreach(var reading in readings) {
        LoadRow(myTable, reading, index++); 
    }
}

protected override void LoadRow(DataTable table, MyObject objectValue, int index)
{
    table.LoadDataRow(
        new object[] { 
            objectValue.property1 + "a", 
            objectValue.property2 + "b", 
            /* ... etc. */
        }, LoadOption.OverwriteChanges);
}

Now, this is a little simplified for readability, and the LoadDataRow line is kind of long(roughly 9 array values), but contains no big function calls or anything, just formatting strings and a few ? cases. This code works fine when I have ~50 values in my table, and I get about 10 Hz refresh. But once I get more values than normal(say, 200), it shoots up exponentially and I get ~0.2 Hz. This is way too slow.

Does anyone have any idea why I'm getting exponential slowdown when I have a big readings enumerable? Is it something to do with OverWriteChanges(which I'm pretty sure I need for other reasons); or maybe creating an object within the LoadDataRow, or is there some other reason?

I have played with different LoadOptions and that doesn't seem to be the problem. I just can't explain why I'm seeing such an enormous increase in processing time, and I can't figure out how to fix it.

Yes, I have: myTable.PrimaryKey = new[] { myTable.Columns["ParentID"], myTable.Columns["ID"] }; back where I initialize my table. — MLavine, May 07 '13 at 18:23

score 2 · Accepted Answer · answered May 07 '13 at 18:35

Add before and after the call to LoadRow the call to BeginLoadData() and EndLoadData().

The BeginLoadData turns off the event notifications, the index maintenance and the constraints check associated with the processing of the new data. This could improve the performance of LoadDataRow.

public void FillTable(DataTable myTable, IEnumerable<MyObject> readings)
{
    var index=0;
    try
    {
        dt.BeginLoadData();
        foreach(var reading in readings) {
            LoadRow(myTable, reading, index++); 
        }
    }
    finally
    {
        dt.EndLoadData();
    }
}

Also be sure to resume the normal processing in case of failure.

Is there any way to speed up datatable.LoadDataRow()?

1 Answers1