测试SqlHelp,linq to SQL,Nhibernate批量处理数据的效率 2009-06-07

 

（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读取数据最慢。