从DbDataReader读取数据的最快方法是什么?

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

在下面的代码中，command是一个已经设置好的DbCommand:

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?

我正在处理的查询的大部分时间都花在了GetValue调用上。对于每个GetValue调用，它是否往返于数据库?看起来是这样的，这似乎是非常低效的。正如代码所指出的，尝试使用GetValues()一次完成它并没有什么区别。有没有一种方法能一次搞定整排人?更好的是，是否有一种方法可以一次获得整个结果集?

Thanks.

谢谢。

6 个解决方案

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

#2

I did some benchmarking myself with various approaches:

我用各种方法对自己做了一些基准测试:

public DataTable Read1(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 Read2<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> Read3<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[] Read4<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> Read5<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.

1和2返回DataTable，而其余的强类型结果集则返回，所以不是苹果对苹果，而是我相应地计时。

Just the essentials:

要点:

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

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

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

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

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

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

    Read5(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 Read1 all performed well. Of all I prefer Read3 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 Read4 or Read5 as you please.

查询返回大约1200行和5个字段(运行100次)。除了Read1之外，所有都表现良好。在所有我喜欢的Read3中，如枚举所示，返回的数据是惰性的。如果您只需要枚举它，那么这对于内存非常有用。要在内存中保存该集合的副本，最好使用Read4或Read5。

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

我将使用类似dap -dot-net的东西来加载到一个基本类型模型中;这是一个微型orm，因此您可以获得元编程的好处(有效地预生成IL等)——而不需要像EF或DataTable这样的开销。

#4

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

您可以使用DbDataAdapter获取所有结果并将它们存储在一个DataTable中。

#5

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

#6

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

使用非类型化数据集。据我所知，这是最快的。