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

现在的位置: 首页 > 综合 > 正文

RSS

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

2012年08月24日 ⁄ 综合 ⁄ 共 13756字 ⁄ 字号小中大 ⁄ 评论关闭

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";
            }
        }
    }
}

测试结果：

第一次执行

ExecuteRowByRow:151ms

ExecuteOnce:19ms

ExecuteSqlBulkCopy:5ms

第二次执行

ExecuteRowByRow:140ms

ExecuteOnce:15ms

ExecuteSqlBulkCopy:6ms

第三次执行

ExecuteRowByRow:179ms

ExecuteOnce:18ms

ExecuteSqlBulkCopy:5ms

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

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

多线程测试一、

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;

        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

【上篇】用CSS实现层的垂直居中
【下篇】一切敌人都是纸老虎!

作者: midwife

该日志由 midwife 于12年前发表在综合分类下，最后更新于 2012年08月24日.
转载请注明: C#：30行数据插入到数据库中的效率测试-一行行执行、构造SQL一次执行、SqlBulkCopy | 学步园 +复制链接

抱歉!评论已关闭.

学步园

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

作者: midwife

书签

最新文章New

本站推荐

返回首页