.NET批量大数据插入性能分析及比较
数据插入使用了以下几种方式 1. 逐条数据插入 2. 拼接sql语句批量插入 3. 拼接sql语句并使用Transaction 4. 拼接sql语句并使用SqlTransaction 5. 使用DataAdapter 6. 使用TransactionScope及SqlBulkCopy 7. 使用表值参数 数据库使用SQL Server，脚本如下 create table TestTable ( Id int ，Name nvarchar(20) ) 程序中生成测试DataTable结构和测试数据的类如下 1.public class Tools 2.{ 3. public static DataTable MakeDataTable() 4. { 5. DataTable table = new DataTable(); 6. 7. //生成DataTable的模式(schema) 8. table.Columns.Add("Id"， Type.GetType("System.Int32")); 9. table.Columns.Add("Name"， Type.GetType("System.String")); 10. 11. //设置主键 12. table.PrimaryKey = new DataColumn[] { table.Columns["ID"] }; 13. table.Columns["Id"].AutoIncrement = true; 14. table.Columns["Id"].AutoIncrementSeed = 1; 15. table.Columns["Id"].ReadOnly = true; 16. return table; 17. } 18. 19. public static void MakeData(DataTable table， int count) 20. { 21. if (table == null) 22. return; 23. 24. if (count <= 0) 25. return; 26. 27. DataRow row = null; 28. 29. for (int i = 1; i <= count; i++) 30. { 31. //创建一个新的DataRow对象(生成一个新行) 32. row = table.NewRow(); 33. row["Name"] = "Test" + i.ToString(); 34. //添加新的DataRow 35. table.Rows.Add(row); 36. } 37. } 38.} public class Tools { public static DataTable MakeDataTable() { DataTable table = new DataTable(); //生成DataTable的模式(schema) table.Columns.Add("Id"， Type.GetType("System.Int32")); table.Columns.Add("Name"， Type.GetType("System.String")); //设置主键 table.PrimaryKey = new DataColumn[] { table.Columns["ID"] }; table.Columns["Id"].AutoIncrement = true; table.Columns["Id"].AutoIncrementSeed = 1; table.Columns["Id"].ReadOnly = true; return table; } public static void MakeData(DataTable table， int count) { if (table == null) return; if (count <= 0) return; DataRow row = null; for (int i = 1; i <= count; i++) { //创建一个新的DataRow对象(生成一个新行) row = table.NewRow(); row["Name"] = "Test" + i.ToString(); //添加新的DataRow table.Rows.Add(row); } } }使用Log4net记录日志，默认插入记录数为40000条，每次插入1条，可在界面修改，使用System.Diagnostics.StopWatch记录插入时间，每次测试后删除原表重建 窗体代码如下： public delegate bool InsertHandler(DataTable table， int batchSize); public partial class FrmBatch : Form { private Stopwatch _watch = new Stopwatch(); public FrmBatch() { InitializeComponent(); } private void FrmBatch_Load(object sender， EventArgs e) { txtRecordCount.Text = "40000"; txtBatchSize.Text = "1"; } //逐条数据插入 private void btnInsert_Click(object sender， EventArgs e) { Insert(DbOperation.ExecuteInsert， "Use SqlServer Insert"); } //拼接sql语句插入 private void btnBatchInsert_Click(object sender， EventArgs e) { Insert(DbOperation.ExecuteBatchInsert， "Use SqlServer Batch Insert"); } //拼接sql语句并使用Transaction private void btnTransactionInsert_Click(object sender， EventArgs e) { Insert(DbOperation.ExecuteTransactionInsert， "Use SqlServer Batch Transaction Insert"); } //拼接sql语句并使用SqlTransaction private void btnSqlTransactionInsert_Click(object sender， EventArgs e) { Insert(DbOperation.ExecuteSqlTransactionInsert， "Use SqlServer Batch SqlTransaction Insert"); } //使用DataAdapter private void btnDataAdapterInsert_Click(object sender， EventArgs e) { Insert(DbOperation.ExecuteDataAdapterInsert， "Use SqlServer DataAdapter Insert"); } //使用TransactionScope private void btnTransactionScopeInsert_Click(object sender， EventArgs e) { Insert(DbOperation.ExecuteTransactionScopeInsert， "Use SqlServer TransactionScope Insert"); } //使用表值参数 private void btnTableTypeInsert_Click(object sender， EventArgs e) { Insert(DbOperation.ExecuteTableTypeInsert， "Use SqlServer TableType Insert"); } private DataTable InitDataTable() { DataTable table = Tools.MakeDataTable(); int count = 0; if (int.TryParse(txtRecordCount.Text.Trim()， out count)) { Tools.MakeData(table， count); //MessageBox.Show("Data Init OK"); } return table; } public void Insert(InsertHandler handler， string msg) { DataTable table = InitDataTable(); if (table == null) { MessageBox.Show("DataTable is null"); return; } int recordCount = table.Rows.Count; if (recordCount <= 0) { MessageBox.Show("No Data"); return; } int batchSize = 0; int.TryParse(txtBatchSize.Text.Trim()， out batchSize); if (batchSize <= 0) { MessageBox.Show("batchSize <= 0"); return; } bool result = false; _watch.Reset(); _watch.Start(); result = handler(table， batchSize); _watch.Stop(www.nuoya66.com); string log = string.Format("{0};RecordCount:{1};BatchSize:{2};Time:{3};"， msg， recordCount， batchSize， _watch.ElapsedMilliseconds); LogHelper.Info(log); MessageBox.Show(result.ToString()); } }
