SqlBulkCoy and the general database operation execution speed comparison

Recommended for you: Get network issues from WhatsUp Gold. Not end users.

SQLBulkCopy, For bulk data transfer between the database. Usually for new, old data between the database update. Even if the table structure is completely different, also can through the corresponding relation between the field, well data.


1 initializing the SqlBulkCopy object, with the new connection as a parameter.

SqlBulkCopy bulkCopy = new SqlBulkCopy (string);

The mapping relationship between the 2 data source and destination data table (listing to the corresponding)

bulkCopy.ColumnMappings.Add("source",'"dest")

3 set the destination table name

 BulkCopy.DestinationTableName = the destination table name;

4 set up a one-time processing lines. The number of rows processed, will stimulate the SqlRowsCopied () method. The default is 1

bulkCopy.NotifyAfter = 10;

5 data transmission

bulkCopy.WriteToServer(sdr);


There is currently a TXT document, which records the large amounts of data

Then we use the common database operations into the database

 1 OpenFileDialog ofd = new OpenFileDialog();
 2             ofd.Filter = "The txt file|*.txt";
 3             if (ofd.ShowDialog() == false)
 4             {
 5 
 6                 return;
 7             }
 8             //Read the contents of TXT, then the string
 9             //Readlines can read the contents of a line, and keep the content of the newline character, while ReadAllLines, must wait until the entire array of strings to be able to access the array returned. Therefore, in the treatment of very large files, ReadLines may be more efficient.  
10             string[] lines = File.ReadLines(ofd.FileName, Encoding.Default).ToArray();
11             using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ToString()))
12             {
13                 con.Open();
14                 //The current time
15                 DateTime startTime = DateTime.Now;
16                 for(int i=1;i<=lines.Length;i++)
17                 {
18                     string line=lines[i];
19                     string[] strs = line.Split('\t');
20 
21                     string startNum = strs[0];
22 
23                     string city = strs[1];
24                     city = city.Trim('"');
25 
26                     string type = strs[2];
27                     type = type.Trim('"');
28 
29                     string areaNum = strs[3];
30                     areaNum = areaNum.Trim('"');
31                     using (SqlCommand cmd = con.CreateCommand())
32                     {
33                         cmd.CommandText = @"insert into T_TelNum(StartNum,TelArea,TelType,AreaNum) 
34                                  values(@StartNum,@TelArea,@TelArea,@AreaNum)";
35                         cmd.Parameters.Add(new SqlParameter("@StartNum",startNum));
36                         cmd.Parameters.Add(new SqlParameter("@TelArea", city));
37                         cmd.Parameters.Add(new SqlParameter("@TelType", type));
38                         cmd.Parameters.Add(new SqlParameter("@AreaNum", areaNum));
39                         cmd.ExecuteNonQuery();
40                     }
41                     //Article I execution time to
42                     DateTime nowTime = DateTime.Now;
43                     TimeSpan ts = nowTime - startTime;
44                     //double totalTime=(ts.TotalSeconds/(i+1))*(lines.Length)/60;
45 
46                     //To the total number of seconds
47                     double totalTime = ts.TotalSeconds * lines.Length / (i + 1);

Run the project, the total number of seconds to complete.

It will run time of 1671 seconds, nearly 28 minutes.


Then we use SqlBulkCopy to read the data and storage.

 1  //Create a DatatTable for the transmission of parameters. 
 2             DataTable table = new DataTable();
 3             //Create database columns and the corresponding
 4             table.Columns.Add("StartNum");
 5             table.Columns.Add("TelArea");
 6             table.Columns.Add("TelType");
 7             table.Columns.Add("AreaNum");
 8             DateTime start = DateTime.Now;
 9             for (int i = 1; i <= lines.Length; i++)
10             {
11                 string line = lines[i];
12                 string[] strs = line.Split('\t');
13 
14                 string startNum = strs[0];
15 
16                 string city = strs[1];
17                 city = city.Trim('"');
18 
19                 string type = strs[2];
20                 type = type.Trim('"');
21 
22                 string areaNum = strs[3];
23                 areaNum = areaNum.Trim('"');
24                 DataRow row = table.NewRow();
25                 row[0] = startNum;
26                 row[1] = city;
27                 row[2] = type;
28                 row[3] = areaNum;
29                 
30             }
31 
32 
33             using (System.Data.SqlClient.SqlBulkCopy copy = new System.Data.SqlClient.SqlBulkCopy(ConfigurationManager.ConnectionStrings[0].ToString()))
34             {
35                 //The destination table name
36                 copy.DestinationTableName = "T_TelNum";
37                 //Data mapping between the source and destination data table
38                 copy.ColumnMappings.Add("StartNum", "StartNum");
39                 copy.ColumnMappings.Add("TelArea", "TelArea");
40                 copy.ColumnMappings.Add("TelType", "TelType");
41                 copy.ColumnMappings.Add("AreaNum", "AreaNum");
42 
43                 copy.WriteToServer(table);
44             }
45             double totoleTime = DateTime.Now.Second- start.Second;
46             


It is not difficult to find only 3 seconds, so it can be seen that in the best use of SqlBulkCopy in data processing for operation´╝ü

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Oswald at November 13, 2013 - 9:29 AM