数据库访问与本地文件访问实测报告

一直认为数据库访问比本地文件访问是要慢很多的，但究竟慢多少却不清楚。终于忍不住作了下比较测试。

测试环境：OS---Windows 2000 Server        DatabaseServer----SQLServer 2000        程序语言：CSharp

测试方式：从数据库中读取参数表/从XML中读取配置参数等同数量的记录并实例化对象。

测试程序如下：

 using System;
 using System.Globalization;
 using System.IO;
 using System.Xml.Serialization;
 using System.Xml;
 using System.Data;
 using System.Data.SqlClient;

 public class Demo
 {
  public static void Main()
  {
   System.Console.WriteLine("===== ExecuteXML() =====================================");
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine(ExecuteXML());
   System.Console.WriteLine("========================================================");
   
   System.Console.WriteLine("===== ExecuteSQL() =====================================");
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine(ExecuteSQL());
   System.Console.WriteLine("========================================================/n");

  }

  static double ExecuteSQL()
  {
   string _connString = "server=data-server;user id=sa;password=1234;database=LeadDB;min pool size=4;max pool size=4;packet size=3072";
   SqlCommand cmd = new SqlCommand();
   SqlConnection conn = new SqlConnection(_connString);

   double begin = CultureInfo.InvariantCulture.Calendar.GetMilliseconds(DateTime.Now);
   try {
    conn.Open();

    cmd.Connection = conn;
    cmd.CommandText = "SELECT [CategoryId], [Name] FROM [SYS_Category] WHERE [CategoryType] = 2";

    cmd.CommandType = CommandType.Text;
    SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
    while (rdr.Read())
    {
     new Entity(rdr.GetInt32(0), rdr.GetString(1));
    }
    rdr.Close();
   }catch {
    conn.Close();
    throw;
   }
   begin = CultureInfo.InvariantCulture.Calendar.GetMilliseconds(DateTime.Now) - begin;
   return (begin >= 0)?begin:begin+1000.0;
  }

  static double ExecuteXML()
  {
   double begin = CultureInfo.InvariantCulture.Calendar.GetMilliseconds(DateTime.Now);
   XmlDocument xmlDoc = new XmlDocument();
   xmlDoc.Load( @"F:/LeadBridge/XCERP/Web/OnlineService/SYS_Category_Headship.xml" );
   
   foreach(XmlNode node in xmlDoc.SelectNodes("items/item")){
    new Entity(int.Parse(node.Attributes["id"].Value), node.InnerText);
   }
   begin = CultureInfo.InvariantCulture.Calendar.GetMilliseconds(DateTime.Now) - begin;
   return (begin >= 0)?begin:begin+1000.0;
  }
 }

 public class Entity {
  public Entity(int age, string name){
   this._Age = age;
   this._Name = name;
  }
  private int _Age;
  private string _Name;
  public int Age{
   get{ return _Age; }
   set{ _Age = value; }
  }
  public string Name{
   get{ return _Name; }
   set{ _Name = value; }
  }
 }

执行结果如下：

===== ExecuteXML() =====================================
47
0
0
16
0
0
0
0
16
0
========================================================
===== ExecuteSQL() =====================================
203
0
0
0
0
0
16
0
0
0
========================================================

做过多次测试，基本上是如上数量级别（一个数量级）的差异（第一次执行差异），因此建议，大家对于无需经常修改的数据保存为本地参数文件形式比直接从数库读取效率要高一些。