测试SqlHelp,linq to SQL,Nhibernate批量处理数据的效率 2009-06-07
来源:互联网 发布:mac里的照片导出到u盘 编辑:程序博客网 时间:2024/06/06 17:14
(SqlHelp是一个操作数据库的辅助类,linq to SQL,Nhibernate,是数据持久化框架, linq to SQ只适合MS SQL,Nhibernate适合多种数据源)
数据的准备:
示例数据库: AdventureWorks
产生测试数据的脚本:
Code
--从[Person].[Address]把数据复制到新表[Address1]中
select into [Address1] from [AdventureWorks].[Person].[Address]
--从[Person].[Address]数据复制到[Address1]表,多次执行可产生更多数据
INSERT INTO [AdventureWorks].[dbo].[Address1]
([AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate])
SELECT
[AddressLine1]
,[AddressLine2]
,[City]
,[StateProvinceID]
,[PostalCode]
,[rowguid]
,[ModifiedDate]
FROM [AdventureWorks].[Person].[Address]
--删除[Address1]数据
delete from [AdventureWorks].[dbo].[Address1]
--查询数据
select * from [dbo].[Address1]
测试代码:
Code
using System;
using System.Collections.Generic;
using System.Collections;
using System.Linq;
using System.Text;
using Microsoft.ApplicationBlocks.Data;
using System.Diagnostics;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq.Dynamic;
using NHibernate;
using NHibernate.Cfg;
using System.Data.Common;
namespace ConTestData
{
class Program
{
static void Main(string[] args)
{
TestSqlHelp();
TestLinq();
TestNhibernate();
Console.Read();
}
/// <summary>
/// 测试SqlHelp的方法
/// </summary>
static void TestSqlHelp()
{
Console.WriteLine("SqlHelp 操作数据!");
Stopwatch wacth = new Stopwatch();
string conString = ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ConnectionString;
string selectCmm = " select * from Address1 ";
DataSet ds = new DataSet();
DataTable address = new DataTable();
wacth.Start();
Console.WriteLine("开始取数据");
ds= SqlHelper.ExecuteDataset(conString,CommandType.Text,selectCmm);
address = ds.Tables[0];
Console.WriteLine("取数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Rows.Count);
Console.WriteLine("开始取改变实体的值");
wacth.Reset();
wacth.Start();
foreach (DataRow a in address.Rows)
{
a["AddressLine1"] = a["AddressLine1"] + "1";
a["AddressLine2"] = a["AddressLine2"] + "1";
a["City"] = a["City"] + "1";
a["ModifiedDate"] =((DateTime)a["ModifiedDate"]).AddDays(1);
}
Console.WriteLine("改变实体值完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Rows.Count);
wacth.Reset();
wacth.Start();
SqlConnection con = new SqlConnection(conString);
SqlDataAdapter sqlDA = new SqlDataAdapter(selectCmm,con);
SqlCommandBuilder bd= new SqlCommandBuilder(sqlDA);
SqlCommand up = bd.GetUpdateCommand();
Console.WriteLine("开始取更新数据");
SqlHelper.UpdateDataset(bd.GetInsertCommand(), bd.GetDeleteCommand(), up, ds, ds.Tables[0].TableName);
Console.WriteLine("更新数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Rows.Count);
DataSet addressCopy = SqlHelper.ExecuteDataset(conString, CommandType.Text, selectCmm);
wacth.Reset();
wacth.Start();
foreach (DataRow a in address.Rows)
{
a.Delete();
}
Console.WriteLine("开始取删除数据");
SqlHelper.UpdateDataset(bd.GetInsertCommand(), bd.GetDeleteCommand(), up, ds, ds.Tables[0].TableName);
Console.WriteLine("删除数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, addressCopy.Tables[0].Rows.Count);
wacth.Reset();
wacth.Start();
foreach (DataRow a in addressCopy.Tables[0].Rows)
{
a.SetAdded();
}
Console.WriteLine("开始添加新数据");
SqlHelper.UpdateDataset(bd.GetInsertCommand(), bd.GetDeleteCommand(), up, addressCopy, addressCopy.Tables[0].TableName);
Console.WriteLine("添加数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, addressCopy.Tables[0].Rows.Count);
}
/// <summary>
/// 测试Linq的方法
/// </summary>
static void TestLinq()
{
Console.WriteLine("Linq to SQL 操作数据!");
Stopwatch wacth = new Stopwatch();
TestLinqDataContext dc = new TestLinqDataContext();
wacth.Start();
Console.WriteLine("开始取数据");
var address = dc.Address1.ToArray();
Console.WriteLine("取数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Length);
Console.WriteLine("开始取改变实体的值");
wacth.Reset();
wacth.Start();
foreach (var a in address)
{
a.AddressLine1 = a.AddressLine1 + "1";
a.AddressLine2 = a.AddressLine2 + "1";
a.City = a.City + "1";
a.ModifiedDate = a.ModifiedDate.AddDays(1);
}
Console.WriteLine("改变实体值完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Length);
wacth.Reset();
wacth.Start();
Console.WriteLine("开始取更新数据");
dc.SubmitChanges();
Console.WriteLine("更新数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Length);
wacth.Reset();
wacth.Start();
foreach (var a in address)
{
dc.Address1.DeleteOnSubmit(a);
}
int count = address.Length;
Console.WriteLine("开始删除数据");
dc.SubmitChanges();
Console.WriteLine("删除数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, count);
TestLinqDataContext dcAdd = new TestLinqDataContext();
wacth.Reset();
wacth.Start();
foreach (var a in address)
{
dcAdd.Address1.InsertOnSubmit(a);
}
Console.WriteLine("开始添加数据");
dcAdd.SubmitChanges();
Console.WriteLine("添加数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Length);
}
/// <summary>
/// 测试Nhibernate的方法
/// </summary>
static void TestNhibernate()
{
Console.WriteLine("Nhibernate 操作数据!");
Stopwatch wacth = new Stopwatch();
string strSelect = " select * from Address1 ";
NHibernate.Cfg.Configuration config = new NHibernate.Cfg.Configuration();
config.Configure(@"D:\MyProject\ConTestData\ConTestData\ConTestData\App.config");
config.AddAssembly(typeof(Address1N).Assembly);
config.AddXmlFile(@"D:\MyProject\ConTestData\ConTestData\ConTestData\Adress1N.hbm.xml");
//config.AddClass(typeof(Address1N));
ISessionFactory isessionFactory = config.BuildSessionFactory();
ISession isession = isessionFactory.OpenSession();
wacth.Start();
Console.WriteLine("开始取数据");
// IList<Address1N> address = isession.CreateCriteria(typeof(Address1N)).List<Address1N>();
IList<Address1N> address = isession.CreateSQLQuery(strSelect).AddEntity(typeof(Address1N)).List<Address1N>();
Console.WriteLine("取数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Count);
Console.WriteLine("开始取改变实体的值");
wacth.Reset();
wacth.Start();
foreach (var a in address)
{
a.AddressLine1 = a.AddressLine1 + "1";
a.AddressLine2 = a.AddressLine2 + "1";
a.City = a.City + "1";
a.ModifiedDate = a.ModifiedDate.AddDays(1);
}
Console.WriteLine("改变实体值完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Count);
wacth.Reset();
wacth.Start();
Console.WriteLine("开始取更新数据");
using(ITransaction tran=isession.Transaction)
{
tran.Begin();
foreach(Address1N a in address)
{
isession.Update(a);
}
tran.Commit();
}
Console.WriteLine("更新数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Count);
wacth.Reset();
wacth.Start();
Console.WriteLine("开始取删除数据");
using (ITransaction tran = isession.Transaction)
{
tran.Begin();
foreach (Address1N a in address)
{
isession.Delete(a);
}
tran.Commit();
}
Console.WriteLine("删除数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Count);
wacth.Reset();
wacth.Start();
Console.WriteLine("开始取添加数据");
using (ITransaction tran = isession.Transaction)
{
tran.Begin();
foreach (Address1N a in address)
{
isession.Save(a);
}
tran.Commit();
}
Console.WriteLine("添加数据完成用时为{0}毫秒!一共{1}条数据", wacth.ElapsedMilliseconds, address.Count);
}
}
}
测试解决方案的下载
测试显示结果:
操作数据为117684条;显示结果表进:Linq to SQL 读取数据最快,但更新数据\删除数据\添加数据性速度是最慢的。
Nhibernate读取数据最慢。
- 测试SqlHelp,linq to SQL,Nhibernate批量处理数据的效率 2009-06-07
- LINQ to SQL 效率问题
- 扩展LINQ to SQL:使用Lambda Expression批量删除数据
- LINQ TO SQL批量查找
- Linq To SQL 批量更新方法汇总
- Linq to Sql 合并数据
- Linq to SQL 读取数据
- linq to sql更改数据
- SQL SERVER 批量效率的读取游标数据?
- Linq to SQL 插入数据时的一个问题
- linq,linq to sql
- 今天关于Linq,NHibernate等持久数据的理解
- LINQ to SQL的不足
- LINQ to SQL的不足
- LINQ to SQL 的EntitySet)>)
- linq to sql 的学习
- Linq to sql 的学习体会
- Linq To Sql的总结
- makefile中常见的错误—missing separator. Stop.—原因命令行缺少tab键
- 线程属性pthread_attr_t简介
- XML简介
- 写页面加载状态改变的时候执行的方法
- 使用IDirectDrawClipper进行裁剪
- 测试SqlHelp,linq to SQL,Nhibernate批量处理数据的效率 2009-06-07
- Linux 信号signal处理机制
- 中国国内无线通信网络介绍
- pthread_cond_signal和pthread_cond_wait简介
- SQL Server与Oracle的数据同步方案及解决过程
- 各种Mutex(互斥量)的区别
- 当代GSM手机的硬件系统分析
- MySQL的AUTO_INCREMENT
- Posix线程私有数据