| 文章内容 -
|
|
|
ADO.NET 中 DataReader 各种读取方式性能差别
很早就做了DataReader 一个测试
～～【拍砖有分】允许，偶也拉风一次哈～～
A.
非官方（^_^）测试结论（以下序号越大，性能越低）
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);
}
下一篇:错误602:未能在sysindexes中找到数据库ID7中对象ID1的索引ID1对应的行
上一篇:ASP.NET性能调优总结
开放文章词条: ADO.NET 中 DataReader 各种读取方式性能差别
开放文章目录: ZPYJ > 中文作品研究 > 编程知识
|
|
|
|