如何将慢参数化插入更改为快速批量复制（即使从内存） [英] How to change slow parametrized inserts into fast bulk copy (even from memory)

问题描述

我在我的代码（.Net 2.0，MS SQL）有这样的

I had someting like this in my code (.Net 2.0, MS SQL)

SqlConnection connection = new SqlConnection(@"Data Source=localhost;Initial
Catalog=DataBase;Integrated Security=True");
  connection.Open();

  SqlCommand cmdInsert = connection.CreateCommand();
  SqlTransaction sqlTran = connection.BeginTransaction();
  cmdInsert.Transaction = sqlTran;

  cmdInsert.CommandText =
     @"INSERT INTO MyDestinationTable" +
      "(Year, Month, Day, Hour,  ...) " +
      "VALUES " +
      "(@Year, @Month, @Day, @Hour, ...) ";

  cmdInsert.Parameters.Add("@Year", SqlDbType.SmallInt);
  cmdInsert.Parameters.Add("@Month", SqlDbType.TinyInt);
  cmdInsert.Parameters.Add("@Day", SqlDbType.TinyInt);
  // more fields here
  cmdInsert.Prepare();

  Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);

  StreamReader reader = new StreamReader(stream);
  char[] delimeter = new char[] {' '};
  String[] records;
  while (!reader.EndOfStream)
  {
    records = reader.ReadLine().Split(delimeter, StringSplitOptions.None);

    cmdInsert.Parameters["@Year"].Value = Int32.Parse(records[0].Substring(0, 4));
    cmdInsert.Parameters["@Month"].Value = Int32.Parse(records[0].Substring(5, 2));
    cmdInsert.Parameters["@Day"].Value = Int32.Parse(records[0].Substring(8, 2));
    // more here complicated stuff here
    cmdInsert.ExecuteNonQuery()
  }
  sqlTran.Commit();
  connection.Close();

使用 cmdInsert.ExecuteNonQuery（）注释掉此代码的执行时间少于2秒。使用SQL执行它需要1m 20秒。有大约0.5百万的记录。表已清空。类似功能的SSIS数据流任务大约需要20秒。

With cmdInsert.ExecuteNonQuery() commented out this code executes in less than 2 sec. With SQL execution it takes 1m 20 sec. There are around 0.5 milion records. Table is emptied before. SSIS data flow task of similar functionality takes around 20 sec.

• 批量插入是 ）。


• 我的测试机是Core 2 Duo，配有2 GB RAM。


• 查看任务管理器时，CPU未完全启用。 IO似乎也没有被充分利用。


• 模式很简单，就像地狱：一个表以AutoInt为主索引，小于10个int，微小的int和chars（10）。

• Bulk Insert was not an option (see below). I did some fancy stuff during this import.
• My test machine is Core 2 Duo with 2 GB RAM.
• When looking in Task Manager CPU was not fully untilized. IO seemed also not to be fully utilized.
• Schema is simple like hell: one table with AutoInt as primary index and less than 10 ints, tiny ints and chars(10).

在这里的一些答案后，我发现可以执行从内存批量复制！我拒绝使用批量复制beacuse我认为它必须从文件...

After some answers here I found that it is possible to execute bulk copy from memory! I was refusing to use bulk copy beacuse I thought it has to be done from file...

现在我使用这个，它需要aroud 20秒（像SSIS任务）

Now I use this and it takes aroud 20 sec (like SSIS task)

  DataTable dataTable = new DataTable();

  dataTable.Columns.Add(new DataColumn("ixMyIndex", System.Type.GetType("System.Int32")));   
  dataTable.Columns.Add(new DataColumn("Year", System.Type.GetType("System.Int32")));   
  dataTable.Columns.Add(new DataColumn("Month", System.Type.GetType("System.Int32")));
  dataTable.Columns.Add(new DataColumn("Day", System.Type.GetType("System.Int32")));
 // ... and more to go

  DataRow dataRow;
  object[] objectRow = new object[dataTable.Columns.Count];

  Stream stream = new FileStream(fileName, FileMode.Open, FileAccess.Read);

  StreamReader reader = new StreamReader(stream);
  char[] delimeter = new char[] { ' ' };
  String[] records;
  int recordCount = 0;
  while (!reader.EndOfStream)
  {
    records = reader.ReadLine().Split(delimeter, StringSplitOptions.None);

    dataRow = dataTable.NewRow();
    objectRow[0] = null; 
    objectRow[1] = Int32.Parse(records[0].Substring(0, 4));
    objectRow[2] = Int32.Parse(records[0].Substring(5, 2));
    objectRow[3] = Int32.Parse(records[0].Substring(8, 2));
    // my fancy stuf goes here

    dataRow.ItemArray = objectRow;         
    dataTable.Rows.Add(dataRow);

    recordCount++;
  }

  SqlBulkCopy bulkTask = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, null);
  bulkTask.DestinationTableName = "MyDestinationTable"; 
  bulkTask.BatchSize = dataTable.Rows.Count;
  bulkTask.WriteToServer(dataTable);
  bulkTask.Close();

推荐答案

a href =http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx =nofollow noreferrer> SqlBulkCopy 类批量插入所有记录一次。

Instead of inserting each record individually, Try using the SqlBulkCopy class to bulk insert all the records at once.

创建一个DataTable并将所有记录添加到DataTable，然后使用 SqlBulkCopy 。 WriteToServer 批量插入所有数据。

Create a DataTable and add all your records to the DataTable, and then use SqlBulkCopy.WriteToServer to bulk insert all the data at once.

这篇关于如何将慢参数化插入更改为快速批量复制（即使从内存）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！