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 次的测试结果。
测试结果补充分析:
1. 按 Index(SELECT 子句中列索引) 读取比按 Name(列名)读取快
2. DataReader.GetXXX(<<ColumnIndex>>) 遥遥领先,基于两点:
a. 此方法内部直接访问对应的数据库类型,不存在 box/unbox 。
b. 基于索引访问。
3. (<<Type>>)DataReader[<<ColumnIndex>>] 比 Convert.ToXXX(DataReader[<<ColumnIndex>>]) 快
a. 前者属于编程语言特有的强制类型转换,对于值类型,存在 unbox 过程。
b. 后者使用的是.NET FCL提供的一组与语言无关的类型转换方法。静态类 Convert 中的类型转换方法,调用源类型实现的 IConvertible 接口进行目标类型的转换。也就是说,只有实现了 IConvertible 接口接口的类型才能用 Convert.ToXXX 进行类型转换。如, Convert.ToInt32(object)
public static int ToInt32(object value)
{
if (value != null)
{
return ((IConvertible) value).ToInt32(null);
}
return 0;
}
4. 由于使用 Index 方式访问,容易出错,可维护性差,一种折中方式,是先根据 Name 读取 Index,然后调用 GetXXX 方法。如,
int id = DataReader.GetInt32(DataReader.GetOrdinal("ID"));
5. 当循环读取所有行时,直接使用方式 4 ,每此都要读取列名(DataReader.GetOrdinal 内部也是一个 Lookup 的过程),因此需要改进。考虑首次读取后将 Name 缓存起来,以后直接读缓存,由于需要 index/name 成对关联,考虑性能,Hashtable 是不二选择,NET 2.0 还提供了泛型版本 System.Collections.Generic.Dictionary<TK,TV> 。
对于非泛型的 Hashtable 只有一个 key 存在 box/unbox,两者性能差别,并不十分明显。
但相对于,各种按 Name 访问方式,有明显的性能优势。
6. 以上各组读取方式比较,随着数据量的增加,性能差异越来越明显,少量数据读取无法看出明显差别。
7. 对于借助于自动化工具、代码生成工具开发应用程序,应该优先考虑 GetXXX 的方式读取。
8. xxxDataAdapter.Fill 方法内部使用对应 Data Provider 的 xxxDataReader 填充 DataTable。
details at
http://www.cnblogs.com/Jinglecat/archive/2007/08/08/847145.html