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.

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 : https://github.com/nhibernate/nhibernate-core/blob/master/src/NHibernate/Driver/NHybridDataReader.cs — granadaCoder, Oct 29 '18 at 14:05
Don't use "GetValue". Use the concrete GetString, GetInt32, etc, etc for best performance. — granadaCoder, Oct 29 '18 at 14:05

score 43 · Answer 1 · edited Jul 09 '21 at 03:59

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.

+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? — Tim M., Feb 13 '13 at 19:11
@TimMedora no idea, but I confirmed it trying with `SQLite Connector` as well which yielded just the same result which I answered [here](http://stackoverflow.com/a/14807940/661933). Its so odd nevertheless. — nawfal, Feb 13 '13 at 19:13
@TimMedora: You need to surround the `DataTable.Load()` with `.BeginLoadData()` and `.EndLoadData()` to achieve the same speed as with the `DataSet`. — Nikola Bogdanović, Apr 18 '14 at 14:43
This answer contains useful information, but doesn't address the OP's question. — William Gross, Jul 22 '14 at 14:48
@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! :) — nawfal, Jul 22 '14 at 14:57
hi all, anyone can explain how to pass "Func" parameter in above Read5~~(string query, Func selector).~~ — nativegrip, Jul 05 '17 at 08:25
@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. — nawfal, Jul 05 '17 at 11:38

score 4 · Accepted Answer · answered Apr 22 '11 at 18:17

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();
        }
    }

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. — Greg Smalter, Apr 29 '12 at 18:46

score 4 · Answer 3 · answered Apr 22 '11 at 18:23

4

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.

answered Apr 22 '11 at 18:23

Marc Gravell

1,026,079
266
2,566
2,900

4

I don't think this would help with his specific question about data readers. Dapper itself uses DbDataReader.GetValue under the hood. – William Gross Jul 22 '14 at 14:42

score 1 · Answer 4 · edited Jun 11 '15 at 15:18

1

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

edited Jun 11 '15 at 15:18

ryanyuyu

6,366
10
48
53

answered Apr 22 '11 at 18:15

Tyler Ferraro

3,753
1
21
28

score 1 · Answer 5 · answered Apr 22 '11 at 18:16

1

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

answered Apr 22 '11 at 18:16

Ron Harlev

16,227
24
89
132

score -1 · Answer 6 · answered Mar 31 '15 at 19:27

-1

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

answered Mar 31 '15 at 19:27

migula

7
1

Why are you considering it the "fastest?" You didn't supply any reasoning behind this answer. – Kevin B Burns Mar 27 '19 at 15:47

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

6 Answers6

Linked