What is the fastest way to read data from a DbDataReader?

Question

In the following code, command is a DbCommand that has already been set up:

using( var dataReader = command.ExecuteReader() /*The actual execution of the query takes relatively little time.*/ ) {
                while( dataReader.Read() ) {
                    // These are what take all of the time. Replacing them all with reader.GetValues( myArray ) has no impact.
                    val0 = dataReader.GetValue( 0 );
                    val1 = dataReader.GetValue( 1 );
                    val2 = dataReader.GetValue( 2 );
                }
            }

The bulk of the time for the query I am currently working with is spent doing the GetValue calls. Is it making a round trip to the database for each GetValue call? It seems like it is, and this seems very inefficient. As the code notes, attempting to do it in one shot using GetValues() does not make a difference. Is there a way to get the entire row in one shot? Better yet, is there a way to get the entire result set in one shot?

Thanks.

Scrimshaw

Without writing your implementation, IDataReader and/or DbDataReader and "GetStrongScalarType( ordinalNumber ) is the faster. GetString, GetInt32, etc. and 0, 1, 2 or ordinal. At the end of the data, filling a DataTable or a DataSet or most ORM's are using IDataReader and/or DbDataReader, and using ordinal number. ORM can use ("byStringColumnName"), but they usually (one time) map those to ordinals and cache it for repeat calls. Example ORM : github.com/nhibernate/nhibernate-core/blob/master/src/… – Christianna

Don't use "GetValue". Use the concrete GetString, GetInt32, etc, etc for best performance. – Christianna

Answer 1

using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM dbo.Categories;" +
          "SELECT EmployeeID, LastName FROM dbo.Employees",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        while (reader.HasRows)
        {
            Console.WriteLine("\t{0}\t{1}", reader.GetName(0),
                reader.GetName(1));

            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
            reader.NextResult();
        }
    }

Diencephalon

This isn't any faster than the code I posted in the original question (it's roughly the same), but I think it's the fastest of the answers. At least we know now that there isn't some way-faster method that I was missing. – Scrimshaw

Answer 2

I did some benchmarking myself with various approaches:

public DataTable Read_using_DataTable_Load(string query)
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = query;
        cmd.Connection.Open();
        var table = new DataTable();
        using (var r = cmd.ExecuteReader())
            table.Load(r);
        return table;
    }
}

public DataTable Read_using_DataSet_Fill<S>(string query) where S : IDbDataAdapter, IDisposable, new()
{
    using (var da = new S())
    {
        using (da.SelectCommand = conn.CreateCommand())
        {
            da.SelectCommand.CommandText = query;
            DataSet ds = new DataSet();
            da.Fill(ds);
            return ds.Tables[0];
        }
    }
}

public IEnumerable<S> Read_using_yield_selector<S>(string query, Func<IDataRecord, S> selector)
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = query;
        cmd.Connection.Open();
        using (var r = cmd.ExecuteReader())
            while (r.Read())
                yield return selector(r);
    }
}

public S[] Read_using_selector_ToArray<S>(string query, Func<IDataRecord, S> selector)
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = query;
        cmd.Connection.Open();
        using (var r = cmd.ExecuteReader())
            return ((DbDataReader)r).Cast<IDataRecord>().Select(selector).ToArray();
    }
}

public List<S> Read_using_selector_into_list<S>(string query, Func<IDataRecord, S> selector)
{
    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandText = query;
        cmd.Connection.Open(); 
        using (var r = cmd.ExecuteReader())
        {
            var items = new List<S>();
            while (r.Read())
                items.Add(selector(r));
            return items;
        }
    }
}

1 and 2 returns DataTable while the rest strongly typed result set, so its exactly not apples to apples, but I while time them accordingly.

Just the essentials:

Stopwatch sw = Stopwatch.StartNew();
for (int i = 0; i < 100; i++)
{
    Read_using_DataTable_Load(query); // ~8900 - 9200ms

    Read_using_DataTable_Load(query).Rows.Cast<DataRow>().Select(selector).ToArray(); // ~9000 - 9400ms

    Read_using_DataSet_Fill<MySqlDataAdapter>(query); // ~1750 - 2000ms

    Read_using_DataSet_Fill<MySqlDataAdapter>(query).Rows.Cast<DataRow>().Select(selector).ToArray(); // ~1850 - 2000ms

    Read_using_yield_selector(query, selector).ToArray(); // ~1550 - 1750ms

    Read_using_selector_ToArray(query, selector); // ~1550 - 1700ms

    Read_using_selector_into_list(query, selector); // ~1550 - 1650ms
}

sw.Stop();
MessageBox.Show(sw.Elapsed.TotalMilliseconds.ToString());

The query returned about 1200 rows and 5 fields (run for 100 times). Apart from Read_using_Table_Load all performed well.

Of all I prefer Read_using_yield_selector which returns data lazily, as enumerated. This is great for memory if you only need to enumerate it. To have a copy of the collection in memory, you're better off with Read_using_selector_ToArray or Read_using_selector_into_list as you please.

Ehling

+1 - Interesting, especially the comparison between DataTable.Load() and IDataAdapter.Fill(). I would have thought that the data adapter was the same or slower than loading directly to a DataTable, but it looks like it is 4-5x faster? Any idea why? – Edmanda

@TimMedora no idea, but I confirmed it trying with SQLite Connector as well which yielded just the same result which I answered here. Its so odd nevertheless. – Ehling

@TimMedora: You need to surround the DataTable.Load() with .BeginLoadData() and .EndLoadData() to achieve the same speed as with the DataSet. – Microsporophyll

This answer contains useful information, but doesn't address the OP's question. – Zygoma

@WilliamGross I believe I did. It happens a lot of times on SO where OP will have one question in the title and another one in the body (description). Though related, they wont be exactly the same often. I think I tried to answer "fastest way to read", and also "how to load it at once" written in the question body. But I take your criticism and will answer more appropriately later! :) – Ehling

hi all, anyone can explain how to pass "Func<IDataRecord, S>" parameter in above Read5<S>(string query, Func<IDataRecord, S> selector). – Genitive

@WinodPatel you would do something like: var users = Read5("select * from User", r => new User { Id = r[0], Name = r[1] }). Hope it's clear. – Ehling

Yes nawfal, exactly i want it. Thank u so much – Genitive

Answer 3

I would use something like dapper-dot-net to load it into a basic type model; this is a micro-ORM, so you get the benefits of meta-programming (efficiently pre-generated IL etc) - without the overhead of things like EF or DataTable.

Allaallah

I don't think this would help with his specific question about data readers. Dapper itself uses DbDataReader.GetValue under the hood. – Zygoma

Answer 4

You could use a DbDataAdapter to get all the results and store them in a DataTable.

Succentor

Answer 5

        Dim adapter As New Data.SqlClient.SqlDataAdapter(sqlCommand)
        Dim DT As New DataTable
        adapter.Fill(DT)

Shad

Answer 6

Use Untyped DataSet. That is fastest, as far as I know.

Amanita

Why are you considering it the "fastest?" You didn't supply any reasoning behind this answer. – Lammergeier