C#：30行数据插入到数据库中的效率测试-一行行执行、构造SQL一次执行、SqlBulkCopy

博客园 - Ferry

ASP.NET网站权限设计实现（三）——套用JQuery EasyUI列表显示数据、分页、查询 ASP.NET网站权限设计实现（二）——角色权限绑定 ASP.NET读取网络图片并在页面上显示 ASP.NET网站权限设计实现（一）——使用PowerDesigner进行数据库设计 Visual Studio 2010 架构图之用例图（UML Use Case Diagram） ASP.NET MVC3 新的视图引擎（View Engine）一个德国人图解中西文化差异（值得一看）微软AJAX CDN（内容分发网络）注册使用GAC—Global Assembly Cache（.NET） ASP.NET 递归将分类绑定到 TreeView - Ferry 新浪微博PC客户端（DotNet WinForm版）——功能实现分解介绍新浪微博PC客户端（DotNet WinForm版）—— 初探 C#使用GMAIL群发带附件邮件的例子 ASP.NET Web 应用开发实战快速上手系列 3—C#面向对象编程纲要-类 ASP.NET带进度条多文件上传 ASP.NET4.0中客户端ID的生成在高优先级下运行应用程序学习使用NHibernate2.1.0Beta1（续七）— 单例模式 ASP.NET Web 应用开发实战快速上手系列 2——C#基础

Ferry · 2010-11-11 · via 博客园 - Ferry

CSDN中看到有人问这一样一个问题：

GridView中有30條記錄：

產品編號產品名稱產品價格

001 男士活力潔面乳 39

002 男士剃鬚刀 109

......

030 男士沐浴香波 120

有兩种方法寫入數據庫：

（1）

打開數據庫連接

逐條插入數據

關閉連接

（2）

拼湊出更新數據的SQL語句

打開數據庫連接

執行這條拼湊的SQL語句

關閉數據庫連接

請問哪一種效率更高？大概能高出多少？

另外，在ASP.NET 2.0中，有SqlBulkCopy類，它能完全取代普通的ADO.NET操作嗎？

于是我做了个简单测试，代码如下：

数据库表很简单：

CREATE TABLE [dbo].[TestTable](

[ID] [int] NULL,

[CreateDateTime] [datetime] NULL,

[TestMethod] [nvarchar](50) NULL

) ON [PRIMARY]

代码

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;namespace InsertEfficiency
{
    class Program
    {
        static void Main(string[] args)
        {
            //构造数据源
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("ID",typeof(int)));
            dt.Columns.Add(new DataColumn("CreateDateTime",typeof(DateTime)));
            dt.Columns.Add(new DataColumn("TestMethod", typeof(string)));for (int i = 1; i <= 30; i++)
            {
                dt.Rows.Add(new object[] { i, DateTime.Now, "ExecuteSqlBulkCopy" });
            }

Test t

= new Test();

DateTime begin1

= DateTime.Now;
            t.ExecuteRowByRow(dt);
            DateTime end1 = DateTime.Now;
            Console.WriteLine("ExecuteRowByRow:{0}ms", (end1 - begin1).Milliseconds);

DateTime begin2

= DateTime.Now;
            t.ExecuteOnce(dt);
            DateTime end2 = DateTime.Now;
            Console.WriteLine("ExecuteOnce:{0}ms", (end2 - begin2).Milliseconds);

DateTime begin3

= DateTime.Now;
            t.ExecuteSqlBulkCopy(dt);
            DateTime end3 = DateTime.Now;
            Console.WriteLine("ExecuteSqlBulkCopy:{0}ms", (end3 - begin3).Milliseconds);

            Console.ReadLine();
        }
    }

class Test
    {
        public Test()
        {
        }public void ExecuteRowByRow(DataTable dt)
        {
            using(SqlConnection conn = new SqlConnection(GetConnectionString))
            {
                conn.Open();
                for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
                {
                    DataRow dr = dt.Rows[rowIndex];

SqlCommand cmd

= new SqlCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = string.Format("insert into TestTable values ({0},'{1}','{2}')",
                        dr[0].ToString(), dr[1].ToString(), "ExecuteRowByRow");
                    cmd.ExecuteNonQuery();
                }
            }
        }
        public void ExecuteOnce(DataTable dt)
        {
            StringBuilder strSql = new StringBuilder();
            for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
            {
                DataRow dr = dt.Rows[rowIndex];
                string sql = string.Format("insert into TestTable values ({0},'{1}','{2}')",
                        dr[0].ToString(), dr[1].ToString(), "ExecuteOnce");
                if (strSql.ToString().Length == 0)
                {
                    strSql.Append(sql);
                }
                else
                {
                    strSql.Append(";").Append(sql);
                }

}

using (SqlConnection conn = new SqlConnection(GetConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = strSql.ToString();
                cmd.ExecuteNonQuery();
            }
        }public void ExecuteSqlBulkCopy(DataTable dt)
        {
            using (SqlConnection conn = new SqlConnection(GetConnectionString))
            {
                SqlBulkCopy bulk = new SqlBulkCopy(conn);
                bulk.DestinationTableName = "TestTable";
                bulk.BatchSize = dt.Rows.Count;if (dt != null && dt.Rows.Count != 0)
                {
                    conn.Open();
                    bulk.WriteToServer(dt);
                }
                bulk.Close();
            }
        }string GetConnectionString
        {
            get
            {
                return @"server=.\mssqlserver2008;database=test;uid=sa;pwd=123456";
            }
        }
    }
}

测试结果：

虽然测试方法比较简单，但基本能说明问题了。

-----------------------------------------------------------------------------------------------------------------------------------------------

多线程测试一、

using System;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Threading;namespace InsertEfficiency
{
    class Program
    {
        static void Main(string[] args)
        {
            Test t = new Test();
            t.Testing();
            Console.ReadLine();
        }

}

class Test
    {
        DataTable dt = new DataTable();public Test()
        {
            CreateData();
        }private void CreateData()
        {
            //构造数据源
            dt.Columns.Add(new DataColumn("ID", typeof(int)));
            dt.Columns.Add(new DataColumn("CreateDateTime", typeof(DateTime)));
            dt.Columns.Add(new DataColumn("TestMethod", typeof(string)));for (int i = 1; i <= 30; i++)
            {
                dt.Rows.Add(new object[] { i, DateTime.Now, "ExecuteSqlBulkCopy" });
            }
        }public void Testing()
        {
            for (int i = 0; i < 20; i++)
            {
                Thread t = new Thread(new ParameterizedThreadStart(ExcuteTesting));
                t.Start(i);
            }
        }private void ExcuteTesting(object TreadNo)
        {
            DateTime begin1 = DateTime.Now;
            ExecuteRowByRow();
            DateTime end1 = DateTime.Now;
            Console.WriteLine("Tread-{0}-ExecuteRowByRow:{1}ms", TreadNo,(end1 - begin1).Milliseconds);

DateTime begin2

= DateTime.Now;
            ExecuteOnce();
            DateTime end2 = DateTime.Now;
            Console.WriteLine("Tread-{0}-ExecuteOnce:{1}ms", TreadNo, (end2 - begin2).Milliseconds);

DateTime begin3

= DateTime.Now;
            ExecuteSqlBulkCopy();
            DateTime end3 = DateTime.Now;
            Console.WriteLine("Tread-{0}-ExecuteSqlBulkCopy:{1}ms", TreadNo,(end3 - begin3).Milliseconds);

Console.WriteLine(

"Tread-{0} execute successfully\r\n", TreadNo);

        }private void ExecuteRowByRow()
        {
            using(SqlConnection conn = new SqlConnection(GetConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                DataRow dr;
                conn.Open();
                SqlTransaction tran = conn.BeginTransaction();
                try
                {
                    for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
                    {
                        dr = dt.Rows[rowIndex];
                        cmd.CommandText = string.Format("insert into TestTable values ({0},'{1}','{2}')",
                            dr[0].ToString(), dr[1].ToString(), "ExecuteRowByRow");
                        cmd.ExecuteNonQuery();
                    }
                    tran.Commit();
                }
                catch
                {
                    tran.Rollback();
                }
            }
        }private void ExecuteOnce()
        {
            StringBuilder strSql = new StringBuilder();
            for (int rowIndex = 0; rowIndex < dt.Rows.Count; rowIndex++)
            {
                DataRow dr = dt.Rows[rowIndex];
                string sql = string.Format("insert into TestTable values ({0},'{1}','{2}')",
                        dr[0].ToString(), dr[1].ToString(), "ExecuteOnce");
                if (strSql.ToString().Length == 0)
                {
                    strSql.Append(sql);
                }
                else
                {
                    strSql.Append(";").Append(sql);
                }

}

using (SqlConnection conn = new SqlConnection(GetConnectionString))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = strSql.ToString();
                conn.Open();
                cmd.ExecuteNonQuery();
            }
        }public void ExecuteSqlBulkCopy()
        {
            using (SqlConnection conn = new SqlConnection(GetConnectionString))
            {
                SqlBulkCopy bulk = new SqlBulkCopy(conn);
                bulk.DestinationTableName = "TestTable";
                bulk.BatchSize = dt.Rows.Count;if (dt != null && dt.Rows.Count != 0)
                {
                    conn.Open();
                    bulk.WriteToServer(dt);
                }
                bulk.Close();
            }
        }private string GetConnectionString
        {
            get
            {
                return @"server=.\mssqlserver2008;database=test;uid=sa;pwd=123456";
            }
        }
    }
}

结果：

Tread-2-ExecuteRowByRow:546ms

Tread-5-ExecuteRowByRow:521ms

Tread-0-ExecuteRowByRow:592ms

Tread-1-ExecuteRowByRow:623ms

Tread-10-ExecuteRowByRow:445ms

Tread-8-ExecuteRowByRow:471ms

Tread-4-ExecuteRowByRow:545ms

Tread-7-ExecuteRowByRow:494ms

Tread-9-ExecuteRowByRow:448ms

Tread-11-ExecuteRowByRow:396ms

Tread-6-ExecuteRowByRow:493ms

Tread-12-ExecuteRowByRow:441ms

Tread-13-ExecuteRowByRow:405ms

Tread-10-ExecuteOnce:74ms

Tread-18-ExecuteRowByRow:421ms

Tread-14-ExecuteRowByRow:457ms

Tread-15-ExecuteRowByRow:459ms

Tread-16-ExecuteRowByRow:457ms

Tread-19-ExecuteRowByRow:428ms

Tread-0-ExecuteOnce:99ms

Tread-3-ExecuteRowByRow:681ms

Tread-2-ExecuteOnce:144ms

Tread-9-ExecuteOnce:108ms

Tread-1-ExecuteOnce:117ms

Tread-17-ExecuteRowByRow:463ms

Tread-0-ExecuteSqlBulkCopy:28ms

Tread-0 execute successfully

Tread-5-ExecuteOnce:167ms

Tread-8-ExecuteOnce:145ms

Tread-15-ExecuteOnce:63ms

Tread-6-ExecuteOnce:118ms

Tread-10-ExecuteSqlBulkCopy:80ms

Tread-10 execute successfully

Tread-11-ExecuteOnce:154ms

Tread-2-ExecuteSqlBulkCopy:56ms

Tread-2 execute successfully

Tread-1-ExecuteSqlBulkCopy:59ms

Tread-1 execute successfully

Tread-8-ExecuteSqlBulkCopy:41ms

Tread-8 execute successfully

Tread-9-ExecuteSqlBulkCopy:83ms

Tread-9 execute successfully

Tread-5-ExecuteSqlBulkCopy:63ms

Tread-5 execute successfully

Tread-15-ExecuteSqlBulkCopy:50ms

Tread-15 execute successfully

Tread-3-ExecuteOnce:99ms

Tread-18-ExecuteOnce:136ms

Tread-6-ExecuteSqlBulkCopy:45ms

Tread-6 execute successfully

Tread-18-ExecuteSqlBulkCopy:8ms

Tread-18 execute successfully

Tread-7-ExecuteOnce:227ms

Tread-14-ExecuteOnce:156ms

Tread-19-ExecuteOnce:155ms

Tread-3-ExecuteSqlBulkCopy:26ms

Tread-3 execute successfully

Tread-11-ExecuteSqlBulkCopy:89ms

Tread-11 execute successfully

Tread-12-ExecuteOnce:218ms

Tread-19-ExecuteSqlBulkCopy:10ms

Tread-19 execute successfully

Tread-13-ExecuteOnce:221ms

Tread-4-ExecuteOnce:265ms

Tread-14-ExecuteSqlBulkCopy:36ms

Tread-14 execute successfully

Tread-4-ExecuteSqlBulkCopy:5ms

Tread-4 execute successfully

Tread-7-ExecuteSqlBulkCopy:50ms

Tread-7 execute successfully

Tread-12-ExecuteSqlBulkCopy:33ms

Tread-12 execute successfully

Tread-16-ExecuteOnce:201ms

Tread-13-ExecuteSqlBulkCopy:28ms

Tread-13 execute successfully

Tread-16-ExecuteSqlBulkCopy:10ms

Tread-16 execute successfully

Tread-17-ExecuteOnce:184ms

Tread-17-ExecuteSqlBulkCopy:3ms

Tread-17 execute successfully

多线程测试二、

   public void Testing()
        {
            for (int i = 0; i < 20; i++)
            {
                Thread t = new Thread(new ParameterizedThreadStart(ExcuteTesting));
                t.Start(i);
                Thread.Sleep(1000);//多加了这行代码
            }
        }

结果：

Tread-0-ExecuteRowByRow:247ms

Tread-0-ExecuteOnce:18ms

Tread-0-ExecuteSqlBulkCopy:6ms

Tread-0 execute successfully

Tread-1-ExecuteRowByRow:11ms

Tread-1-ExecuteOnce:19ms

Tread-1-ExecuteSqlBulkCopy:4ms

Tread-1 execute successfully

Tread-2-ExecuteRowByRow:11ms

Tread-2-ExecuteOnce:22ms

Tread-2-ExecuteSqlBulkCopy:4ms

Tread-2 execute successfully

Tread-3-ExecuteRowByRow:11ms

Tread-3-ExecuteOnce:22ms

Tread-3-ExecuteSqlBulkCopy:4ms

Tread-3 execute successfully

Tread-4-ExecuteRowByRow:11ms

Tread-4-ExecuteOnce:20ms

Tread-4-ExecuteSqlBulkCopy:3ms

Tread-4 execute successfully

Tread-5-ExecuteRowByRow:6ms

Tread-5-ExecuteOnce:13ms

Tread-5-ExecuteSqlBulkCopy:4ms

Tread-5 execute successfully

Tread-6-ExecuteRowByRow:10ms

Tread-6-ExecuteOnce:21ms

Tread-6-ExecuteSqlBulkCopy:4ms

Tread-6 execute successfully

Tread-7-ExecuteRowByRow:10ms

Tread-7-ExecuteOnce:20ms

Tread-7-ExecuteSqlBulkCopy:5ms

Tread-7 execute successfully

Tread-8-ExecuteRowByRow:10ms

Tread-8-ExecuteOnce:326ms

Tread-8-ExecuteSqlBulkCopy:4ms

Tread-8 execute successfully

Tread-9-ExecuteRowByRow:10ms

Tread-9-ExecuteOnce:18ms

Tread-9-ExecuteSqlBulkCopy:4ms

Tread-9 execute successfully

Tread-10-ExecuteRowByRow:12ms

Tread-10-ExecuteOnce:17ms

Tread-10-ExecuteSqlBulkCopy:4ms

Tread-10 execute successfully

Tread-11-ExecuteRowByRow:10ms

Tread-11-ExecuteOnce:20ms

Tread-11-ExecuteSqlBulkCopy:5ms

Tread-11 execute successfully

Tread-12-ExecuteRowByRow:10ms

Tread-12-ExecuteOnce:20ms

Tread-12-ExecuteSqlBulkCopy:3ms

Tread-12 execute successfully

Tread-13-ExecuteRowByRow:10ms

Tread-13-ExecuteOnce:17ms

Tread-13-ExecuteSqlBulkCopy:3ms

Tread-13 execute successfully

Tread-14-ExecuteRowByRow:7ms

Tread-14-ExecuteOnce:14ms

Tread-14-ExecuteSqlBulkCopy:3ms

Tread-14 execute successfully

Tread-15-ExecuteRowByRow:9ms

Tread-15-ExecuteOnce:18ms

Tread-15-ExecuteSqlBulkCopy:3ms

Tread-15 execute successfully

Tread-16-ExecuteRowByRow:11ms

Tread-16-ExecuteOnce:21ms

Tread-16-ExecuteSqlBulkCopy:4ms

Tread-16 execute successfully

Tread-17-ExecuteRowByRow:9ms

Tread-17-ExecuteOnce:19ms

Tread-17-ExecuteSqlBulkCopy:3ms

Tread-17 execute successfully

Tread-18-ExecuteRowByRow:10ms

Tread-18-ExecuteOnce:20ms

Tread-18-ExecuteSqlBulkCopy:5ms

Tread-18 execute successfully

Tread-19-ExecuteRowByRow:10ms

Tread-19-ExecuteOnce:20ms

Tread-19-ExecuteSqlBulkCopy:5ms

Tread-19 execute successfully

此内容由惯性聚合(RSS阅读器)自动聚合整理，仅供阅读参考。原文来自 — 版权归原作者所有。

推荐订阅源

博客园 - Ferry