ADO.NET 中 DataReader 各种读取方式性能差别

1. DataReader.GetXXX(<<ColumnIndex>>)

2. DataReader.GetXXX(Dictionary<string, int>[<<ColumnName>>])
[Dictionary<string, int>.Add(<<ColumnName>>, DataReader.GetOrdinal(<<ColumnName>>))]

3. DataReader.GetXXX((Int32)Hashtable[<<ColumnName>>])
[Hashtable.Add(<<ColumnName>>, DataReader.GetOrdinal(<<ColumnName>>))]

4. (<<Type>>)DataReader[<<ColumnIndex>>]

5. DataReader.GetXXX(DataReader.GetOrdinal(<<ColumnName>>))

6. Convert.ToXXX(DataReader[<<ColumnIndex>>])

7. (<<Type>>)DataReader[<<ColumnName>>]

8. Convert.ToXXX(DataReader[<<ColumnName>>]
B.
测试实例
说明
1. 此测试，直接使用 ASP.NET（似乎不影响对比性），抱歉了，偶就会 WebForm，比较理想的当然整个 Console Applilcation 让她跑

2. 懒于准备样表数据，直接使用 SQL Server 2k. Northwind.Products 表，且只读取 ProductID 字段（INT 型），并由应用程序多次重复读取同一数据，模拟大数据量的效果

测试代码

protected void Button1_Click(object sender, EventArgs e)
{
int i = 5;
while (i-- > 0) {
ExecuteTest();
System.Threading.Thread.Sleep(1000 * 10);
}
}

private void ExecuteTest()
{
const int COLUMN_INDEX_PRODUCT_ID = 0;
const string COLUMN_NAME_PRODUCT_ID = "ProductID";

StringBuilder sb = new StringBuilder();
int loops = 100;
for (int k = 0; k < 5; k++, loops *= 10) {
sb.AppendFormat("{0, 10:N0}/t", loops * 50);

// 1. DataReader.GetXXX(<<ColumnIndex>>)
using (SqlDataReader dr = GetDataReader()) {
int productId, i;
DateTime start = DateTime.Now;
while (dr.Read()) {
i = loops;
while (i-- > 0) {
productId = dr.GetInt32(COLUMN_INDEX_PRODUCT_ID);
}
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
sb.Append(span.TotalSeconds.ToString("f7")).Append("/t");
}

// 2. (<<Type>>)DataReader[<<ColumnIndex>>]
using (SqlDataReader dr = GetDataReader()) {
int productId, i;
DateTime start = DateTime.Now;
while (dr.Read()) {
i = loops;
while (i-- > 0) {
productId = (int)dr[COLUMN_INDEX_PRODUCT_ID];
}
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
sb.Append(span.TotalSeconds.ToString("f7")).Append("/t");
}

// 3. Convert.ToXXX(DataReader[<<ColumnIndex>>])
using (SqlDataReader dr = GetDataReader()) {
int productId, i;
DateTime start = DateTime.Now;
while (dr.Read()) {
i = loops;
while (i-- > 0) {
productId = Convert.ToInt32(dr[0]);
}
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
sb.Append(span.TotalSeconds.ToString("f7")).Append("/t");
}

// 4. (<<Type>>)DataReader[<<ColumnName>>]
using (SqlDataReader dr = GetDataReader()) {
int productId, i;
DateTime start = DateTime.Now;
while (dr.Read()) {
i = loops;
while (i-- > 0) {
productId = (int)dr[COLUMN_NAME_PRODUCT_ID];
}
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
sb.Append(span.TotalSeconds.ToString("f7")).Append("/t");
}

// 5. Convert.ToXXX(DataReader[<<ColumnName>>]
using (SqlDataReader dr = GetDataReader()) {
int productId, i;
DateTime start = DateTime.Now;
while (dr.Read()) {
i = loops;
while (i-- > 0) {
productId = Convert.ToInt32(dr[COLUMN_NAME_PRODUCT_ID]);
}
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
sb.Append(span.TotalSeconds.ToString("f7")).Append("/t");
}

// 6. DataReader.GetXXX(DataReader.GetOrdinal(<<ColumnName>>))
using (SqlDataReader dr = GetDataReader()) {
int productId, i;
DateTime start = DateTime.Now;
while (dr.Read()) {
i = loops;
while (i-- > 0) {
productId = dr.GetInt32(dr.GetOrdinal(COLUMN_NAME_PRODUCT_ID));
}
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
sb.Append(span.TotalSeconds.ToString("f7")).Append("/t");
}

// 7. DataReader.GetXXX((Int32)Hashtable[<<ColumnName>>])
// Hashtable.Add(<<ColumnName>>, DataReader.GetOrdinal(<<ColumnName>>))
using (SqlDataReader dr = GetDataReader()) {
int productId, i;
DateTime start = DateTime.Now;
Hashtable columns = new Hashtable();

int j = 0;
while (dr.Read()) {
i = loops;
while (i-- > 0) {
if (j++ == 0) columns.Add(COLUMN_NAME_PRODUCT_ID, dr.GetOrdinal("ProductID"));
productId = dr.GetInt32((int)columns[COLUMN_NAME_PRODUCT_ID]);
}
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
sb.Append(span.TotalSeconds.ToString("f7")).Append("/t");
}

// 8. DataReader.GetXXX(Dictionary<string, int>[<<ColumnName>>])
// Dictionary<string, int>.Add(<<ColumnName>>, DataReader.GetOrdinal(<<ColumnName>>))
using (SqlDataReader dr = GetDataReader()) {
int productId, i;
DateTime start = DateTime.Now;
Dictionary<string, int> columns = new Dictionary<string, int>();
int j = 0;
while (dr.Read()) {
i = loops;
while (i-- > 0) {
if (j++ == 0) columns.Add(COLUMN_NAME_PRODUCT_ID, dr.GetOrdinal("ProductID"));
productId = dr.GetInt32(columns[COLUMN_NAME_PRODUCT_ID]);
}
}
DateTime end = DateTime.Now;
TimeSpan span = end - start;
sb.Append(span.TotalSeconds.ToString("f7")).Append("/t");
}

sb.AppendLine();
}
sb.AppendLine();

string path = Server.MapPath("result.txt");
File.AppendAllText(path, sb.ToString());
}

private SqlDataReader GetDataReader()
{
string connStr = "server=.;database=Northwind;uid=sa;";
SqlConnection conn = new SqlConnection(connStr);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT TOP 50 ProductID FROM Products";
conn.Open();
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
C.测试结果
5,0000.00000000.00000000.00000000.01001440.00000000.00000000.00000000.0100144
50,0000.00000000.01001440.01001440.04005760.03004320.01001440.02002880.0100144
500,0000.04005760.15021600.18025920.34048960.29041760.15021600.13018720.1201728
5,000,0000.38054721.82262082.13306723.08443523.07442081.45208801.21174241.1316272
50,000,0003.555112014.320592017.254811223.654012825.777065613.289108811.376358410.2146880

5,0000.00000000.00000000.00000000.00000000.01001440.00000000.00000000.0000000
50,0000.00000000.01001440.01001440.02002880.03004320.01001440.01001440.0100144
500,0000.03004320.12017280.16023040.23033120.51073440.13018720.12017280.1101584
5,000,0000.40057601.50216001.60230402.39344163.09444961.39200161.20172801.1015840
50,000,0003.755400013.619584015.922896023.063163225.376489613.249051211.326286410.2247024

5,0000.00000000.00000000.00000000.00000000.01001440.00000000.00000000.0000000
50,0000.00000000.02002880.01001440.02002880.03004320.01001440.01001440.0100144
500,0000.03004320.14020160.15021600.23033120.25036000.13018720.11015840.1201728
5,000,0000.31044641.28184321.55223202.31332642.55367201.31188641.13162721.0314832
50,000,0003.084435212.658201615.562377623.063163225.386504013.219008011.326286410.2447312

5,0000.00000000.02002880.00000000.00000000.01001440.00000000.00000000.0000000
50,0000.01001440.02002880.02002880.02002880.03004320.01001440.01001440.0100144
500,0000.03004320.13018720.15021600.23033120.25036000.15021600.11015840.1001440
5,000,0000.31044641.26181441.55223202.29329762.54365761.32190081.13162721.0314832
50,000,0003.124492812.578086415.522320023.043134425.406532813.269080011.336300810.2347168

5,0000.00000000.00000000.02002880.00000000.01001440.00000000.00000000.0100144
50,0000.00000000.03004320.01001440.03004320.02002880.01001440.01001440.0100144
500,0000.03004320.12017280.16023040.22031680.25036000.15021600.11015840.1001440
5,000,0000.31044641.25180001.55223202.30331202.54365761.32190081.14164161.0214688
50,000,0003.094449612.638172815.552363223.023105625.406532813.219008011.336300810.2948032



D.测试环境
AMD Athlon XP 1800+ 512M -> 好古董的 PC 啊 ^_^
.NET 2.0 + Windows XP SP2
补：
测试结果说明：

测试结果为，用 DataReader 分别读取

5,000
50,000
500,000
5,000,000
50,000,000

条记录（模拟）所花的总时间，单位为秒，

这里显示了连续 5 次的测试结果。