C#:30行数据插入到数据库中的效率测试-一行行执行、构造SQL一次执行、SqlBulkCopy
于是我做了个简单测试,代码如下:
数据库表很简单:
代码
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.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
多线程测试二、
{
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