我自己用不同的方法进行了一些基准测试:
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和2返回DataTable
,而其余的则返回强类型结果集,因此它们并不完全相同,但我会按照它们的实际情况进行计时。
只提取关键部分:
Stopwatch sw = Stopwatch.StartNew();
for (int i = 0; i < 100; i++)
{
Read_using_DataTable_Load(query);
Read_using_DataTable_Load(query).Rows.Cast<DataRow>().Select(selector).ToArray();
Read_using_DataSet_Fill<MySqlDataAdapter>(query);
Read_using_DataSet_Fill<MySqlDataAdapter>(query).Rows.Cast<DataRow>().Select(selector).ToArray();
Read_using_yield_selector(query, selector).ToArray();
Read_using_selector_ToArray(query, selector);
Read_using_selector_into_list(query, selector);
}
sw.Stop();
MessageBox.Show(sw.Elapsed.TotalMilliseconds.ToString());
查询返回了大约1200行和5个字段(运行了100次)。除了Read_using_Table_Load
外,所有的表现都很好。
在所有方法中,我更喜欢Read_using_yield_selector
,它可以按需惰性地返回数据。如果您只需要枚举数据,则这对于内存非常有用。如果您希望将集合复制到内存中,则最好使用Read_using_selector_ToArray
或Read_using_selector_into_list
。