. NET batch Big data insert performance analysis and comparison

Data insertion is used in the following ways

1. Insert data by article
2. Splicing SQL statements BULK INSERT
3. Splicing SQL statements and using transaction
4. Splicing SQL statements and using SqlTransaction
5. Using DataAdapter
6. Using TransactionScope and SqlBulkCopy
7. Using table-Valued parameters

The database uses SQL Server and the script is as follows

CREATE TABLE TestTable
(
Id int
, Name nvarchar (20)
)

The class that generates the test DataTable structure and test data in the program is as follows

[C-sharp] View plaincopyprint?
1.public class Tools
0.9
3. public static DataTable makedatatable ()
4. {
5. DataTable table = new DataTable ();
6.
7.//Schema for generating DataTable (schema)
8. Table. Columns.Add ("Id", Type.GetType ("System.Int32"));
9. Table. Columns.Add ("Name", Type.GetType ("System.String"));
10.
11.//Set Primary key
Table. PrimaryKey = new datacolumn[] {table. columns["ID"};
Table. columns["Id"]. AutoIncrement = true;
. table. columns["Id"]. AutoIncrementSeed = 1;
Table. columns["Id"]. ReadOnly = true;
A. return table;
17.}
18.
public static void Makedata (DataTable table, int count)
20. {
. if (table = = null)
A. return;
23.
if (Count <= 0)
return;
26.
. DataRow row = null;
28.
. for (int i = 1; I <= count; i++)
30. {
31.//Create a new DataRow object (to generate a new row)
row = table. NewRow ();
row["Name"] = "Test" + i.tostring ();
34.//Add a new DataRow
Table. Rows.Add (row);
36.}
37.}
38.}
public class Tools
{
public static DataTable makedatatable ()
{
DataTable table = new DataTable ();

           //Schema for generating DataTable (schema)
             table. Columns.Add ("Id", Type.GetType ("System.Int32"));
            table. Columns.Add ("Name", Type.GetType ("System.String"));

           //Set 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);
}
}
}

Using log4net logging, the default number of inserted records is 40,000, each time inserting 1, can be modified in the interface, using System.Diagnostics.StopWatch record insertion time, after each test delete the original table reconstruction

The form code is as follows:

[C-sharp]Www.nuoya118.com

2. Public Delegate bool Inserthandler (DataTable table, int batchsize);
6. Public partial class Frmbatch:form
8. {
10. private Stopwatch _watch = new Stopwatch ();
14. Public Frmbatch ()
16. {
18. InitializeComponent ();
20. }
24. private void Frmbatch_load (object sender, EventArgs e)
26. {
28. Txtrecordcount.text = "40000";
30. Txtbatchsize.text = "1";
32. }
36. //Data insertion
38. private void Btninsert_click (object sender, EventArgs e)
40. {
42. Insert (Dboperation.executeinsert, "Use SQL Server Insert");
44. }
48. //Splicing SQL statement Insert
50. private void Btnbatchinsert_click (object sender, EventArgs e)
52. {
54. Insert (Dboperation.executebatchinsert, "Use SQL Server Batch Insert");
56. }
60. //Splicing SQL statements and using transaction
62. private void Btntransactioninsert_click (object sender, EventArgs e)
64. {
66. Insert (Dboperation.executetransactioninsert, "Use SQL Server Batch Transaction Insert");
68. }
72. //Splicing SQL statements and using SqlTransaction
74. private void Btnsqltransactioninsert_click (object sender, EventArgs e)
76. {
78. Insert (Dboperation.executesqltransactioninsert, "Use SQL Server Batch sqltransaction Insert");
80. }
84. //Use DataAdapter
86. private void Btndataadapterinsert_click (object sender, EventArgs e)
88. {
90. Insert (Dboperation.executedataadapterinsert, "Use SQL Server DataAdapter Insert");
92. }
96. //Use TransactionScope
98. private void Btntransactionscopeinsert_click (object sender, EventArgs e)
100. {
102. Insert (Dboperation.executetransactionscopeinsert, "Use SQL Server TransactionScope Insert");
104. }
108. //Using table-valued parameters
110. private void Btntabletypeinsert_click (object sender, EventArgs e)
112. {
114. Insert (Dboperation.executetabletypeinsert, "Use SQL Server Tabletype Insert");
116. }
120. private DataTable initdatatable ()
122. {
124. DataTable table = tools.makedatatable ();
126. int count = 0;
128. if (Int. TryParse (TxtRecordCount.Text.Trim (), out count))
130. {
132. Tools.makedata (table, count);
134. //messagebox.show ("Data Init OK");
136. }
138. return table;
140. }
144. public void Insert (Inserthandler handler, string msg)
146. {
148. DataTable table = initdatatable ();
150. if (table = = null)
152. {
154. MessageBox.Show ("DataTable is null");
156. return;
158. }
162. int recordCount = table.  Rows.Count;
164. if (recordCount <= 0)
166. {
168. MessageBox.Show ("No Data");
170. return;
172. }
176. int batchsize = 0;
178. Int.  TryParse (TxtBatchSize.Text.Trim (), out batchsize);
180. if (batchsize <= 0)
182. {
184. MessageBox.Show ("batchsize <= 0");
186. return;
188. }
192. bool result = false;
194. _watch. Reset (); _watch. Start ();
196. result = handler (table, batchsize);
198. _watch. Stop (www.nuoya66.com);
200. string log = string. Format ("{0}; Recordcount:{1}; BATCHSIZE:{2}; TIME:{3}; ", MSG, RecordCount, BatchSize, _watch.  Elapsedmilliseconds);
202. Loghelper.info (log);
204. MessageBox.Show (result. ToString ());
206. }
208. }

. NET batch Big data insert performance analysis and comparison