最近學習了Dapper,聽說它裡面有批量插記錄的功能,就寫了個小程式來測試下:分别用SqlBulkCopay,原始的單條插入,Dapper批量插入5萬條記錄。
表結構 tb_User (id, UserName,Password)
比較結果如下:
1.Dapper: 花費時間10秒
public void TesDappertBatch()
{
IDbConnection connection = new SqlConnection(_connect);
connection.Open();
var tran = connection.BeginTransaction(IsolationLevel.Serializable);
var userList=Enumerable.Range(1, 50000).Select(i => new User
{
UserName = "User" + i,
Password="pwd"+i
});
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
var result = connection.Execute("insert tb_User(UserName,Password) values(@UserName,@Password)", userList,tran);
tran.Commit();
stopwatch.Stop();
Console.WriteLine(stopwatch.ElapsedMilliseconds);
Console.ReadLine();//10023
}
2.SqlBulkCopy,我用的是自己封閉的SqlBulkHelper,花費1.5秒
public void TestBulk()
{
BulkCopyHelper bh = new BulkCopyHelper(_connect);
DataTable dt = new DataTable();
dt.Columns.Add("UserName", typeof(string));
dt.Columns.Add("PassWord", typeof(string));
for(int i=0;i<50000;i++)
{
dt.Rows.Add(new object[] { "User" + i, "pwd" + i });
}
bh.SetMappingList(dt);
bh.DestTable = "tb_User";
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
bh.BulkInsert(dt);
stopwatch.Stop();
Console.WriteLine(stopwatch.ElapsedMilliseconds);
Console.ReadLine(); //1551
}
3.原生SQL單條插入,花費60秒
public void TestSingInsert()
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
for (int i=0;i< 50000;i++)
{
string sql = @"
insert tb_User(UserName,PassWord) values(@p1,@p2)
";
string user = "User" + i;
string pwd = "Pwd" + i;
SqlHelper.ExecuteNonQuery(_connect, CommandType.Text, sql, new SqlParameter[] { new SqlParameter("@p1", user),new SqlParameter("@p2",pwd )});
}
stopwatch.Stop();
Console.WriteLine(stopwatch.ElapsedMilliseconds);
Console.ReadLine(); //60469
}
如此看來,雖然性能還不能和SqlBulkCopy比,但也相當不錯了,而且還可以用在SqlServer以外的資料庫上。在非批量導入(Excel,其它資料表),且不是單條操作的場景下适宜使用。單條操作我還是用自己的ORMapping資料模型更友善,因為Dapper需要自己手工寫類。