CODEWITHSUNDEEP
c#sql-serverperformancedao
tigrou
tigrou

Reputation: 4516

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

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...

Upvotes: 1

Views: 1185

Answers (1)

tigrou
tigrou

Reputation: 4516

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().

Upvotes: 1

Related Questions