. NET batch big data insertion performance analysis and comparison,. net data insertion Performance

. NET batch big data insertion performance analysis and comparison,. net data insertion Performance

 

Data insertion uses the following methods:

1. Insert data one by one
2. concatenate SQL statements for batch insert
3. concatenate SQL statements and use Transaction
4. concatenate SQL statements and use SqlTransaction
5. Use DataAdapter
6. Use TransactionScope and SqlBulkCopy
7. Use table value Parameters

 

The database uses SQL Server. The script is as follows:

 

Create table TestTable
(
Id int
, Name nvarchar (20)
)

 

The program generates the test DataTable structure and test data class as follows:

[C-sharp] view plaincopyprint?
1. public class Tools
2 .{
3. public static DataTable MakeDataTable ()
4 .{
5. DataTable table = new DataTable ();
6.
7. // schema for generating DataTable)
8. table. Columns. Add ("Id", Type. GetType ("System. Int32 "));
9. table. Columns. Add ("Name", Type. GetType ("System. String "));
10.
11. // set the primary key
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. // create a new DataRow object (generate a new row)
32. row = table. NewRow ();
33. row ["Name"] = "Test" + I. ToString ();
34. // Add a new DataRow
35. table. Rows. Add (row );
36 .}
37 .}
38 .}
Public class Tools
{
Public static DataTable MakeDataTable ()
{
DataTable table = new DataTable ();

// Schema)
Table. Columns. Add ("Id", Type. GetType ("System. Int32 "));
Table. Columns. Add ("Name", Type. GetType ("System. String "));

// Set the primary key
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 ++)
{
// Create a new DataRow object (generate a new row)
Row = table. NewRow ();
Row ["Name"] = "Test" + I. ToString ();
// Add a new DataRow
Table. Rows. Add (row );
}
}
}

 

 

 

Use Log4net to record logs. The default number of inserted records is 40000. you can insert one record at a time. You can modify it on the interface and use System. Diagnostics. StopWatch to record the insertion time. After each test, delete the original table and recreate it.

 

The form code is as follows:

 

[C-sharp]Www.nuoya118.com

1. Public delegate bool InsertHandler (DataTable table, int batchSize );
3. Public partial class FrmBatch: Form
4. {
5. Private Stopwatch _ watch = new Stopwatch ();
7. Public FrmBatch ()
8. {
9. InitializeComponent ();
10. }
12. Private void FrmBatch_Load (object sender, EventArgs e)
13. {
14. TxtRecordCount. Text = "40000 ";
15. TxtBatchSize. Text = "1 ";
16. }
18. // Insert data one by one
19. Private void btnInsert_Click (object sender, EventArgs e)
20. {
21. Insert (DbOperation. ExecuteInsert, "Use SqlServer Insert ");
22. }
24. // Concatenate SQL statement insert
25. Private void btnBatchInsert_Click (object sender, EventArgs e)
26. {
27. Insert (DbOperation. ExecuteBatchInsert, "Use SqlServer Batch Insert ");
28. }
30. // Concatenate an SQL statement and use Transaction
31. Private void btnTransactionInsert_Click (object sender, EventArgs e)
32. {
33. Insert (DbOperation. ExecuteTransactionInsert, "Use SqlServer Batch Transaction Insert ");
34. }
36. // Concatenate an SQL statement and use SqlTransaction
37. Private void btnSqlTransactionInsert_Click (object sender, EventArgs e)
38. {
39. Insert (DbOperation. ExecuteSqlTransactionInsert, "Use SqlServer Batch SqlTransaction Insert ");
40. }
42. // Use DataAdapter
43. Private void btnDataAdapterInsert_Click (object sender, EventArgs e)
44. {
45. Insert (DbOperation. ExecuteDataAdapterInsert, "Use SqlServer DataAdapter Insert ");
46. }
48. // Use TransactionScope
49. Private void btnTransactionScopeInsert_Click (object sender, EventArgs e)
50. {
51. Insert (DbOperation. ExecuteTransactionScopeInsert, "Use SqlServer TransactionScope Insert ");
52. }
54. // Use table value Parameters
55. Private void btnTableTypeInsert_Click (object sender, EventArgs e)
56. {
57. Insert (DbOperation. ExecuteTableTypeInsert, "Use SqlServer TableType Insert ");
58. }
60. Private DataTable InitDataTable ()
61. {
62. DataTable table = Tools. MakeDataTable ();
63. Int count = 0;
64. If (int. TryParse (txtRecordCount. Text. Trim (), out count ))
65. {
66. Tools. MakeData (table, count );
67. // MessageBox. Show ("Data Init OK ");
68. }
69. Return table;
70. }
72. Public void Insert (InsertHandler handler, string msg)
73. {
74. DataTable table = InitDataTable ();
75. If (table = null)
76. {
77. MessageBox. Show ("DataTable is null ");
78. Return;
79. }
81. Int recordCount = table. Rows. Count;
82. If (recordCount <= 0)
83. {
84. MessageBox. Show ("No Data ");
85. Return;
86. }
88. Int batchSize = 0;
89. Int. TryParse (txtBatchSize. Text. Trim (), out batchSize );
90. If (batchSize <= 0)
91. {
92. MessageBox. Show ("batchSize <= 0 ");
93. Return;
94. }
96. Bool result = false;
97. _ Watch. Reset (); _ watch. Start ();
98. Result = handler (table, batchSize );
99. _ Watch. Stop (www.nuoya66.com );
100. String log = string. format ("{0}; RecordCount: {1}; BatchSize: {2}; Time: {3};", msg, recordCount, batchSize, _ watch. elapsedMilliseconds );
101. LogHelper. Info (log );
102. MessageBox. Show (result. ToString ());
103. }
104. }