c# – 使用.NET更新(填充)1,000,000条记录到数据库中的最快方法

所以：

DataTable returnedDtViaLocalDbV11 = DtSqlLocalDb.GetDtViaConName(strConnName,queryStr,strReturnedDtName);

现在我们已经返回了DtViaLocalDbV11,让我们创建一个新的DataTable作为源数据库表的克隆：

DataTable NewDtForBlkInsert = returnedDtViaLocalDbV11.Clone();

Stopwatch SwSqlMdfLocalDb11 = Stopwatch.StartNew();
NewDtForBlkInsert.BeginLoadData();

for (int i = 0; i < 1000000; i++)
{
   NewDtForBlkInsert.LoadDataRow(new object[] { null,"NewShipperCompanyName"+i.ToString(),"NewShipperPhone" },false);
}
NewDtForBlkInsert.EndLoadData();

DBRCL_SET.UpdateDBWithNewDtUsingSQLBulkCopy(NewDtForBlkInsert,tblClients._TblName,strConnName);

SwSqlMdfLocalDb11.Stop();

var ResSqlMdfLocalDbv11_0 = SwSqlMdfLocalDb11.ElapsedMilliseconds;

此代码在5200ms内将100万条记录填充到嵌入式SQL数据库(localDb).其余的代码只是实现bulkCopy,但我会发布.

public string UpdateDBWithNewDtUsingSQLBulkCopy(DataTable TheLocalDtToPush,string TheOnlineSQLTableName,string WebConfigConName)
 {
    //Open a connection to the database. 
    using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings[WebConfigConName].ConnectionString))
    {
       connection.Open();

       // Perform an initial count on the destination table.
       SqlCommand commandRowCount = new SqlCommand("SELECT COUNT(*) FROM "+TheOnlineSQLTableName +";",connection);
       long countStart = System.Convert.ToInt32(commandRowCount.ExecuteScalar());

       var nl = "rn";
       string retStrReport = "";
       retStrReport = string.Concat(string.Format("Starting row count = {0}",countStart),nl);
       retStrReport += string.Concat("==================================================",nl);
       // Create a table with some rows. 
       //DataTable newCustomers = TheLocalDtToPush;

       // Create the SqlBulkCopy object.  
       // Note that the column positions in the source DataTable  
       // match the column positions in the destination table so  
       // there is no need to map columns.  
       using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
       {
          bulkCopy.DestinationTableName = TheOnlineSQLTableName;

          try
          {
             // Write from the source to the destination.
             for (int colIndex = 0; colIndex < TheLocalDtToPush.Columns.Count; colIndex++)
             {
                bulkCopy.ColumnMappings.Add(colIndex,colIndex);
             }
             bulkCopy.WriteToServer(TheLocalDtToPush);
          }

          catch (Exception ex)
          {
             Console.WriteLine(ex.Message);
          }
       }

       // Perform a final count on the destination  
       // table to see how many rows were added. 
       long countEnd = System.Convert.ToInt32(
       commandRowCount.ExecuteScalar());

       retStrReport += string.Concat("Ending row count = ",countEnd,nl);
       retStrReport += string.Concat((countEnd - countStart)," rows were added.",nl);
       retStrReport += string.Concat("New Customers Was updated successfully",nl,"END OF PROCESS !");
       //Console.ReadLine();
       return retStrReport;
   }
}

通过连接到SQL服务器尝试它是大约7000ms(最多)& ?7700ms平均.另外通过一个随机的kv nosql数据库花费大约40秒(真的,我甚至没有保留它的记录,因为它通过x2的sql变体).那么…有比我在代码中测试的更快的方法吗？

编辑

我正在使用win7 x64 8gb ram,最重要的是我应该认为(像i5 3ghz)现在不是很好
Raid-0上的x3 500Gb Wd的工作更好
但我只是说如果你会检查你的电脑
虽然只是将其与您的配置中的任何其他方法进行比较