HFSoft.Data2.0数据持久组件性能简要测试结果
HFSoft.Data数据持久组件2.0版本基本功能编写完成，为了验证新版本在改进后的性能于是和传统ADO.Net方式操作数据作了个性能对比。测试方面主要是：添加、修改和查询；此次测试主要是验证组件基础数据操作的性能，因此测试并不十分全面。对于测试的结果可能存在因编写代码问题导致结果有偏差的情况，发现因代码编写问题导致测试结果不正确的朋友请提出。从下面的测试结果可以看到组件的效率还是不错，基本可以达到和传统编写SQL一致的效率。
测试环境： P42.4G
1G
win2003+dotNet2.0+Sql2000
具体列出测试的方式和代码：
1）数据添加性能测试对比
数据结构：
|
ID |
varchar |
36 |
|
FirstName |
varchar |
50 |
|
LastName |
varchar |
50 |
|
City |
varchar |
50 |
|
Address |
varchar |
200 |
|
Remark |
ntext |
16 |
测试方法向数据添加一百条记录，分别执行方法十次取中间八次的时间。
传统ADO.NET操作方式代码:
const string SQL_INSERT = "INSERT INTO TEST(ID,FIRSTNAME,LASTNAME,CITY,ADDRESS,REMARK) VALUES(@ID,@FIRSTNAME,@LASTNAME,@CITY,@ADDRESS,@REMARK)";
static void AddOfSql()
{
SqlConnection conn;
SqlCommand cmd;
mWatch.Reset();
mWatch.Start();
for(int i =0;i<100;i++)
{
using(conn = new SqlConnection("data source=.;initial catalog=northwind;user id=sa;pwd=790128;"))
{
cmd =new SqlCommand();
cmd.Connection = conn;
cmd.CommandText = SQL_INSERT;
cmd.Parameters.Add("@ID",Guid.NewGuid().ToString());
cmd.Parameters.Add("@FIRSTNAME","Fan"+i);
cmd.Parameters.Add("@LASTNAME","Henry" +i);
cmd.Parameters.Add("@CITY","
cmd.Parameters.Add("@ADDRESS","LongDong"+i);
cmd.Parameters.Add("@REMARK","Remark"+i);
conn.Open();
cmd.ExecuteNonQuery();
}
}
mWatch.Stop();
Console.WriteLine("AddOfSQL:" + mWatch.ElapsedMilliseconds+"(毫秒)");
}
HFSoft.Data组件操作代码:
static void AddOfHFSoft()
{
DBEntities.Test item;
mWatch.Reset();
mWatch.Start();
for (int i = 0; i < 100; i++)
{
item = new DBEntities.Test();
item.FirstName = "Fan" + i;
item.LastName = "Henry" + i;
item.City = "
item.Address = "LongDong" + i;
item.Remark = "Remark" + i;
HFSoft.Data.AdoContext.Add(item);
}
mWatch.Stop();
Console.WriteLine("AddOfHFSoft:" + mWatch.ElapsedMilliseconds + "(毫秒)");
}
测试结果：
|
AddOfSQL:93(毫秒) |
AddOfHFSoft:89(毫秒) |
|
AddOfSQL:89(毫秒) |
AddOfHFSoft:95(毫秒) |
|
AddOfSQL:100(毫秒) |
AddOfHFSoft:86(毫秒) |
|
AddOfSQL:86(毫秒) |
AddOfHFSoft:86(毫秒) |
|
AddOfSQL:86(毫秒) |
AddOfHFSoft:86(毫秒) |
|
AddOfSQL:84(毫秒) |
AddOfHFSoft:84(毫秒) |
|
AddOfSQL:84(毫秒) |
AddOfHFSoft:87(毫秒) |
|
AddOfSQL:83(毫秒) |
AddOfHFSoft:88(毫秒) |
2）数据修改性能测试对比
数据结构：
|
ID |
varchar |
36 |
|
FirstName |
varchar |
50 |
|
LastName |
varchar |
50 |
|
City |
varchar |
50 |
|
Address |
varchar |
200 |
|
Remark |
ntext |
16 |
测试方式从数据中获取一百条记录，然后根据索引更新每一条记录。分别执行十次获取中间八次的时间。
传统ADO.NET操作方式代码:
const string SQL_UPDATE = "UPDATE TEST SET FIRSTNAME=@FIRSTNAME,LASTNAME=@LASTNAME,CITY=@CITY,ADDRESS=@ADDRESS,REMARK=@REMARK WHERE ID=@ID";
static void EditOfSql()
{
IList<DBEntities.Test> items = HFSoft.Data.AdoContext.List<DBEntities.Test>(null, new HFSoft.Data.Region(0, 100));
SqlConnection conn;
SqlCommand cmd;
mWatch.Reset();
mWatch.Start();
foreach (DBEntities.Test item in items)
{
item.FirstName += "1";
item.LastName += "1";
item.City += "1";
item.Address += "1";
item.Remark += "1";
using (conn = new SqlConnection("data source=.;initial catalog=northwind;user id=sa;pwd=790128;"))
{
cmd = new SqlCommand(SQL_UPDATE, conn);
cmd.Parameters.Add("@FIRSTNAME", item.FirstName);
cmd.Parameters.Add("@LASTNAME", item.LastName);
cmd.Parameters.Add("@CITY", item.City);
cmd.Parameters.Add("@ADDRESS", item.Address);
cmd.Parameters.Add("@REMARK", item.Remark);
cmd.Parameters.Add("@ID", item.ID);
conn.Open();
cmd.ExecuteNonQuery();
}
}
mWatch.Stop();
Console.WriteLine("EditOfSql:" + mWatch.ElapsedMilliseconds + "(毫秒)");
}
HFSoft.Data组件操作代码:
static void EditOfHFSoft()
{
IList<DBEntities.Test> items = HFSoft.Data.AdoContext.List<DBEntities.Test>(null, new HFSoft.Data.Region(0, 100));
SqlConnection conn;
SqlCommand cmd;
mWatch.Reset();
mWatch.Start();
foreach (DBEntities.Test item in items)
{
item.FirstName += "1";
item.LastName += "1";
item.City += "1";
item.Address += "1";
item.Remark += "1";
HFSoft.Data.AdoContext.Edit(item);
}
mWatch.Stop();
Console.WriteLine("EditOfHFSoft:" + mWatch.ElapsedMilliseconds + "(毫秒)");
}
测试结果：
|
EditOfSql:544(毫秒) |
EditOfHFSoft:554(毫秒) |
|
EditOfSql:578(毫秒) |
EditOfHFSoft:547(毫秒) |
|
EditOfSql:542(毫秒) |
EditOfHFSoft:549(毫秒) |
|
EditOfSql:547(毫秒) |
EditOfHFSoft:555(毫秒) |
|
EditOfSql:541(毫秒) |
EditOfHFSoft:648(毫秒) |
|
EditOfSql:544(毫秒) |
EditOfHFSoft:599(毫秒) |
|
EditOfSql:539(毫秒) |
EditOfHFSoft:548(毫秒) |
|
EditOfSql:541(毫秒) |
EditOfHFSoft:551(毫秒) |
3）数据查询性能测试对比
数据结构：
Sql2000的NorthWind.Orders数据表。
查询条件
1．EmployeeID=1 and OrderDate>1997-7-1
2．EmployeeID=3 and orderdate>1997-1-1 and orderdate <1997-12-31
3．EmployeeID=5 and orderdate < 1998-1-31
4．EmployeeID=7 and orderdate > 1996-5-1 and orderdate < 1996-12-31
方法执行以上4种情况的条件查询并获取数据集，分别执行十次获取中间八次时间。
传统ADO.NET操作方式代码:
static void QueryOfSql()
{
SqlConnection conn;
SqlCommand cmd;
SqlDataAdapter da;
DataSet ds;
List<Filter> filters = GetFilters();
StringBuilder sql;
mWatch.Reset();
mWatch.Start();
foreach (Filter item in filters)
{
using (conn = new SqlConnection("data source=.;initial catalog=northwind;user id=sa;pwd=790128;"))
{
sql = new StringBuilder();
cmd = new SqlCommand();
ds = new DataSet();
cmd.Connection = conn;
sql.Append("select * from orders where 1=1");
if (item.EmployeeID != null)
{
sql.Append(" and employeeid=@EMPLOYEEID");
cmd.Parameters.Add("@EMPLOYEEID", item.EmployeeID);
}
if (item.OrderDateS != null)
{
sql.Append(" and orderdate > @ORDERDATE");
cmd.Parameters.Add("@ORDERDATE", item.OrderDateS);
}
if (item.OrderDateE != null)
{
sql.Append(" and orderdate < @ORDERDATE1");
cmd.Parameters.Add("@ORDERDATE1", item.OrderDateE);
}
cmd.CommandText = sql.ToString();
da = new SqlDataAdapter(cmd);
conn.Open();
da.Fill(ds);
}
}
mWatch.Stop();
Console.WriteLine("QueryOfSql:" + mWatch.ElapsedMilliseconds + "(毫秒)");
}
HFSoft.Data组件操作代码:
static void QueryOfHFSoft()
{
List<Filter> filters = GetFilters();
HFSoft.Data.Expressions.Expression exp;
mWatch.Reset();
mWatch.Start();
foreach (Filter item in filters)
{
exp = new HFSoft.Data.Expressions.Expression();
if (item.EmployeeID != null)
exp &= DBMapping.Orders.EmployeeID == (int)item.EmployeeID;
if (item.OrderDateS != null)
exp &= DBMapping.Orders.OrderDate > (DateTime)item.OrderDateS;
if (item.OrderDateE != null)
exp &= DBMapping.Orders.OrderDate < (DateTime)item.OrderDateE;
IList<Orders> items = HFSoft.Data.AdoContext.List<Orders>(exp);
}
mWatch.Stop();
Console.WriteLine("QueryOfHFSoft:" + mWatch.ElapsedMilliseconds + "(毫秒)");
}
测试结果：
|
QueryOfSql:10(毫秒) |
QueryOfHFSoft:9(毫秒) |
|
QueryOfSql:10(毫秒) |
QueryOfHFSoft:9(毫秒) |
|
QueryOfSql:10(毫秒) |
QueryOfHFSoft:10(毫秒) |
|
QueryOfSql:11(毫秒) |
QueryOfHFSoft:9(毫秒) |
|
QueryOfSql:10(毫秒) |
QueryOfHFSoft:9(毫秒) |
|
QueryOfSql:10(毫秒) |
QueryOfHFSoft:9(毫秒) |
|
QueryOfSql:10(毫秒) |
QueryOfHFSoft:9(毫秒) |
|
QueryOfSql:9(毫秒) |
QueryOfHFSoft:9(毫秒) |
组件查询结果效率比传统查询返回DataSet快的主要原因是，组件只构造一个List对象远没有DataSet来得复杂因此效率上快。
