How can I fill a DataTable using a SqlDataReader as fast as when using SqlDataAdapter?

Question

I have wrote a method which return a DataRow as soon as it has been fetched from a IDataReader source (using yield keyword). It works, but it is really slower than filling the DataTable using a SqlDataAdapter.

public static IEnumerable<DataRow> AsEnumerable(this IDataReader source)
{
    //create columns
    DataTable dt = new DataTable(); 
    for (int i = 0; i < source.FieldCount; i++)
    {
        dt.Columns.Add(source.GetName(i), source.GetFieldType(i));
    }

    //fetch data
    object[] data = new object[source.FieldCount];   
    dt.BeginLoadData();
    while (source.Read())
    {
        DataRow dr = dt.NewRow(); //this is very slow, bottleneck ?

        source.GetValues(data); 
        for (int i = 0; i < source.FieldCount; i++)
        {
            dr[i] = data[i];
        }
        dt.Rows.Add(dr);
        yield return dr;
    }
    dt.EndLoadData();
}

Here is the results (250.000 rows, lot of columns) :

00:08:34.652  //using SqlDataAdapter
00:12:95.153  //using method above

How can i make it as fast as using SqlDataAdapter ?

Why i'm using this and not SqlDataAdapter : i'd like to process rows as soon as they came from the source, not to 'block' a very long time and wait for all records to be there.

Maybe there is another way to do this. Note : i cannot simply yield a IDataReader (which is fast and support what i need : accessing the data using Item[ColumnName], because data is going to be acceded by different threads, so i need to copy it somewhere before moving to next row...

maybe it would be faster to just do source.GetData(dr.ItemArray); — Lorentz Vedeler
– Lorentz Vedeler, Commented Oct 5, 2013 at 11:33
Good suggestion. I'm going to try this. What i tried before asking the question is to fill DataRow using DataRow.ItemArray[i] (inside for loop) and it was really slow. — tigrou
– tigrou, Commented Oct 5, 2013 at 11:41
Its slightly faster, i get between 9.6 and 10.5 seconds (good), but it is still always slower than SqlDataAdapter (even after several tries) — tigrou
– tigrou, Commented Oct 5, 2013 at 11:47

tigrou · Accepted Answer · 2013-10-10 13:00:04Z

1

I will answer my own question :

A method that will give similar performance as using SqlDataReader is to use DataTable.LoadDataRow(object[] values, bool fAcceptChanges) method. This is the method used internally when a DataTable is populated using Fill() method.

It's fast because it fill DataRow data by accessing some internal fields directly, while using DataRow indexer (eg: dr[i] = value) will do several times some internal checks.

As Lorentz suggested, using ItemArray is faster than indexer but it is still slower than using LoadDataRow().

answered Oct 10, 2013 at 13:00

tigrou

4,5865 gold badges41 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How can I fill a DataTable using a SqlDataReader as fast as when using SqlDataAdapter?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related