CODEWITHSUNDEEP
c#.netado.net
Davis Dimitriov
Davis Dimitriov

Reputation: 4257

Fastest way to copy a list of ado.net datatables

I have a list of DataTables like

List<DataTable> a = new List<DataTable>();

I want to make a deep copy of this list (i.e. copying each DataTable). My code currently looks like

List<DataTable> aCopy = new List<DataTable>();
for(int i = 0; i < a.Rows.Count; i++) {
    aCopy.Add(a[i].Copy());
}

The performance is absolutely terrible, and I am wondering if there is a known way to speed up such a copy?

Edit: do not worry about why I have this or need to do this, just accept that it is part of a legacy code base that I cannot change

Upvotes: 1

Views: 2425

Answers (4)

Mahesh Chavda
Mahesh Chavda

Reputation: 593

I found following approach much more efficient than other ways of filtering records like LINQ, provided your search criteria is simple:

    public static DataTable FilterByEntityID(this DataTable table, int EntityID)
    {
        table.DefaultView.RowFilter = "EntityId = " + EntityID.ToString();
        return table.DefaultView.ToTable();
    }

Upvotes: 0

caveman_dick
caveman_dick

Reputation: 6637

You should specify the capacity of the list otherwise it will have to grow internally to accommodate the data. See here for the detailed explanation.

List<DataTable> aCopy = new List<DataTable>(a.Count);

Upvotes: 0

dash
dash

Reputation: 91490

You can try the following - it gave me a performance boost, although your mileage might vary! I've adapted it to your example to demonstrate how to copy a datatable using an alternative mechanism - clone the table, then stream the data in. You could easily put this in an extension method.

List<DataTable> aCopy = new List<DataTable>(); 
for(int i = 0; i < a.Rows.Count; i++) { 
   DataTable sourceTable = a[i];
   DataTable copyTable = sourceTable.Clone(); //Clones structure
   copyTable.Load(sourceTable.CreateDataReader());
}

This was many times faster (around 6 in my use case) than the following:

DataTable copyTable = sourceTable.Clone();
foreach(DataRow dr in sourceTable.Rows)
{
    copyTable.ImportRow(dr);
}

Also, If we look at what DataTable.Copy is doing using ILSpy:

public DataTable Copy()
{
    IntPtr intPtr;
    Bid.ScopeEnter(out intPtr, "<ds.DataTable.Copy|API> %d#\n", this.ObjectID);
    DataTable result;
    try
    {
        DataTable dataTable = this.Clone();
        foreach (DataRow row in this.Rows)
        {
            this.CopyRow(dataTable, row);
        }
        result = dataTable;
    }
    finally
    {
        Bid.ScopeLeave(ref intPtr);
    }
    return result;
}

internal void CopyRow(DataTable table, DataRow row)
{
    int num = -1;
    int newRecord = -1;
    if (row == null)
    {
        return;
    }
    if (row.oldRecord != -1)
    {
        num = table.recordManager.ImportRecord(row.Table, row.oldRecord);
    }
    if (row.newRecord != -1)
    {
        if (row.newRecord != row.oldRecord)
        {
            newRecord = table.recordManager.ImportRecord(row.Table, row.newRecord);
        }
        else
        {
            newRecord = num;
        }
    }
    DataRow dataRow = table.AddRecords(num, newRecord);
    if (row.HasErrors)
    {
        dataRow.RowError = row.RowError;
        DataColumn[] columnsInError = row.GetColumnsInError();
        for (int i = 0; i < columnsInError.Length; i++)
        {
            DataColumn column = dataRow.Table.Columns[columnsInError[i].ColumnName];
            dataRow.SetColumnError(column, row.GetColumnError(columnsInError[i]));
        }
    }
}

It's not surprising that the operation will take a long time; not only is it row by row, but it also does additional validation.

Upvotes: 2

rerun
rerun

Reputation: 25505

if you have to copy a data table it is essentially an N time operation. If the data table is very large and causing a large amount of allocation you may be able to speed up the operation by doing a section at a time, but you are essentially bounded by the work set.

Upvotes: 2

Related Questions